package test_data

import (
	"bufio"
	"encoding/json"
	"errors"
	"gitee.com/golang-module/carbon/v2"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
	"gorm.io/gorm"
	"log"
	"os"
	"strconv"
	"strings"
	"testData/global"
	"testData/model"
	"time"
)

//func Emails() {
//	fmt.Println("Connecting to server...")
//
//	// Connect to server
//	c, err := client.DialTLS("imap.exmail.qq.com:993", nil)
//	if err != nil {
//		fmt.Println("连接IMAP服务器出错:", err)
//		return
//	}
//	fmt.Println("Connected QQ Imap Receive Server(imap.exmail.qq.com)...")
//
//	// Login
//	if err = c.Login("wip@tkplusemi.com", "Tkpluse123ip"); err != nil {
//		fmt.Println("登陆IMAP服务器出错:", err)
//		return
//	}
//	//if err := c.Login("jh_peng@tkplusemi.com", "Jimmy123"); err != nil {
//	//	log.Fatal(err)
//	//}
//	fmt.Println("Logged in...")
//
//	global.IMAP = c
//
//	// List mailboxes
//	mailboxes := make(chan *imap.MailboxInfo, 10)
//	done := make(chan error, 10)
//	go func() {
//		done <- global.IMAP.List("", "*", mailboxes)
//	}()
//	log.Println("Mailboxes:")
//	for m := range mailboxes {
//		fmt.Println("* " + m.Name)
//	}
//
//	if err := <-done; err != nil {
//		fmt.Println(err)
//	}
//
//	// Select INBOX
//	mbox, err := global.IMAP.Select("INBOX", false)
//	if err != nil {
//		fmt.Println(err)
//	}
//	fmt.Println("mbox:", mbox)
//	//fmt.Println("Flags for INBOX:", mbox.Flags)
//
//	// 创建搜索条件
//	criteria := imap.NewSearchCriteria()
//	// 搜索未读标志的邮件
//	criteria.WithoutFlags = []string{"\\Seen"}
//	// 获取未读标志邮件的UID
//	ids, _ := global.IMAP.UidSearch(criteria)
//	// 按UID获取邮件
//	seqset := new(imap.SeqSet)
//	seqset.AddNum(ids...)
//
//	// 获取邮件
//	messages := make(chan *imap.Message)
//	section := imap.BodySectionName{}
//	// 获取邮件标志, 内容
//	items := []imap.FetchItem{imap.FetchFlags, section.FetchItem()}
//	done = make(chan error, len(ids))
//	go func() {
//		// 按UID获取邮件
//		done <- global.IMAP.UidFetch(seqset, items, messages)
//	}()
//
//	var index int
//	var factorys []*model.Factory
//	global.PostGreSQL.Find(&factorys)
//
//	imap.CharsetReader = charset.Reader
//	for msg := range messages {
//		start := time.Now()
//		r := msg.GetBody(&section)
//		if r == nil {
//			log.Println("服务器未返回邮件正文")
//		}
//		fmt.Printf("获取邮件内容耗时:%s\n", time.Now().Sub(start).String())
//		mr, err := mail.CreateReader(r)
//		if err != nil {
//			log.Fatal(err)
//		}
//		header := mr.Header
//		var subject string
//		// 获取邮件发件人
//		if from, err := header.AddressList("From"); err == nil {
//			fmt.Println(from[0].Address)
//			startFrom := time.Now()
//			address := from[0].Address
//			if orgsender, err := header.AddressList("X-Qq-Csender"); err == nil { // X-Qq-Orgsender
//				if len(orgsender) > 0 && orgsender[0].Address != "" {
//					address = orgsender[0].Address
//				}
//			}
//			if len(from) != 1 {
//				fmt.Printf("当前邮件耗时:%s\n", time.Now().Sub(start).String())
//				fmt.Printf("当前邮件处理From耗时:%s\n", time.Now().Sub(startFrom).String())
//				continue
//			}
//			var needHandle bool
//			for j := 0; j < len(factorys); j++ {
//				if address != factorys[j].Email {
//					continue
//				}
//				// 获取邮件主题
//				if subject, err = header.Subject(); err == nil {
//					if !strings.Contains(subject, factorys[j].Keyword) || factorys[j].Keyword == "" {
//						continue
//					}
//					needHandle = true
//					index = j
//					log.Println("Subject:", subject)
//				}
//
//			}
//			if !needHandle {
//				fmt.Printf("当前邮件耗时:%s\n", time.Now().Sub(start).String())
//				fmt.Printf("当前邮件处理From耗时:%s\n", time.Now().Sub(startFrom).String())
//				continue
//			}
//			log.Println("From:", from)
//		}
//		var companyName string
//		companyName = factorys[index].Name
//		// 处理邮件正文
//		for {
//			p, err := mr.NextPart()
//			if err == io.EOF {
//				break
//			} else if err != nil {
//				log.Fatal("NextPart:err ", err)
//			}
//			switch h := p.Header.(type) {
//			case *mail.AttachmentHeader:
//				// 正文内附件
//				b, _ := ioutil.ReadAll(p.Body)
//				filename, _ := h.Filename()
//				var a []byte
//				p.Body.Read(a)
//				suffixIndex := strings.LastIndex(filename, ".")
//				suffix := strings.ToLower(filename[suffixIndex:])
//				filename = filename[:suffixIndex] + suffix
//				if suffix != ".xlsx" && suffix != ".xls" && suffix != ".csv" {
//					continue
//				}
//
//				// 根据公司名生成文件路径
//				folderPath := utils.MakeSavePath(companyName)
//				var newFileName string
//				if strings.Contains(filename, ".xlsx") {
//					newFileName = filepath.Join(folderPath, time.Now().Format("20060102150405.xlsx"))
//					f, _ := os.OpenFile(newFileName, os.O_RDWR|os.O_CREATE, 0766)
//					_, err = f.Write(b)
//					if err != nil {
//						log.Println("download file", filename, "err")
//						_ = os.Remove(filename)
//					}
//					f.Close()
//				} else if strings.Contains(filename, ".xls") {
//					newFileName = filepath.Join(folderPath, time.Now().Format("20060102150405.xls"))
//					f, _ := os.OpenFile(newFileName, os.O_RDWR|os.O_CREATE, 0766)
//					_, err = f.Write(b)
//					if err != nil {
//						log.Println("download file", filename, "err")
//						_ = os.Remove(filename)
//					}
//					_ = f.Close()
//					utils.ToXlsx("xls", newFileName)
//					newFileName += ".xlsx"
//				} else if strings.Contains(filename, ".csv") {
//					newFileName = filepath.Join(folderPath, time.Now().Format("20060102150405.xlsx"))
//					rows := strings.Split(string(b), "\r\n")
//					f := excelize.NewFile()
//					// 获取Sheet工作表名称
//					sheetName := f.GetSheetName(0)
//					for i := 0; i < len(rows); i++ {
//						columns := strings.Split(rows[i], ",")
//						_ = f.SetSheetRow(sheetName, string(byte(65))+strconv.Itoa(i+1), &columns)
//					}
//					_ = f.SaveAs(newFileName)
//					_ = f.Close()
//				}
//				// 处理Excel
//				switch companyName {
//
//				}
//				fmt.Printf("当前邮件耗时:%s\n", time.Now().Sub(start).String())
//			default:
//				fmt.Printf("当前邮件耗时:%s\n", time.Now().Sub(start).String())
//				continue
//			}
//		}
//	}
//	// 添加标志
//	item := imap.FormatFlagsOp(imap.AddFlags, true)
//	// 已读标志\Seen
//	flags := []interface{}{imap.SeenFlag}
//	// 将处理的未读邮件设置为已读
//	err = global.IMAP.UidStore(seqset, item, flags, nil)
//	if err != nil {
//		log.Println(err)
//	}
//	global.IMAP.Logout()
//}

func SMCFT(filePath string) {
	file, _ := excelize.OpenFile(filePath)
	sheetName := "FT0801 test Yield(" + carbon.Now().SubMonth().Format("Y.m") + ")"
	rows, err := file.GetRows(sheetName)
	if err != nil {
		log.Println(err)
	}
	if len(rows) < 3 {
		log.Println("Sheet ", sheetName, "格式不规范")
		return
	}
	title := make(map[string]int)
	for index, cell := range rows[1] {
		title[cell] = index
	}
	var fTReports []*model.Report
	_ = global.Oracle.Transaction(func(tx *gorm.DB) error {
		for i := 2; i < len(rows); i++ {
			var tcSFDFile *model.TcSfdFile
			tx.Where("tc_sfd01 = ? AND tc_sfd06 = ?", rows[i][title["客户订单号"]], rows[i][title["客户批号"]]).Find(&tcSFDFile)
			orderDate := carbon.Parse("20" + strings.Split(rows[i][title["客户订单号"]], "-")[1][:6]).Format("Y-m-d")
			fTReports = append(fTReports, &model.Report{
				Product:         rows[i][title["完成型号"]],
				Lot:             rows[i][title["客户批号"]],
				Factory:         "saimeike",
				TestProgram:     rows[i][title["测试程序"]],
				PBI:             rows[i][title["客户订单号"]],
				SubBatch:        rows[i][title["厂内批号"]],
				OrderDate:       orderDate,
				Seal:            strings.ReplaceAll(tcSFDFile.TcSfdUd02, "'", ""),
				TestQuantity:    rows[i][title["主数量"]],
				PassQuantity:    rows[i][title["PASS数"]],
				PassProbability: rows[i][title["良率"]],
				Step:            "FT",
			})
		}
		return nil
	})
	global.PostGreSQL.Create(&fTReports)
}

func QiPaiFT(filePath string) {
	file, _ := excelize.OpenFile(filePath)
	sheetName := file.GetSheetName(1)
	rows, err := file.GetRows(sheetName)
	if err != nil {
		log.Println(err)
	}
	if len(rows) < 2 {
		log.Println("Sheet ", sheetName, "格式不规范")
		return
	}
	title := make(map[string]int)
	for index, cell := range rows[0] {
		title[cell] = index
	}
	var fTReports []*model.Report
	_ = global.Oracle.Transaction(func(tx *gorm.DB) error {
		for i := 1; i < len(rows); i++ {
			var tcSFDFile *model.TcSfdFile
			tx.Where("tc_sfd01 = ? AND tc_sfd06 = ?", rows[i][title["客户订单编号"]], rows[i][title["芯片批号"]]).Find(&tcSFDFile)
			orderDate := carbon.Parse("20" + strings.Split(rows[i][title["客户订单编号"]], "-")[1][:6]).Format("Y-m-d")
			fTReports = append(fTReports, &model.Report{
				Product:         rows[i][title["出货品名"]],
				Lot:             rows[i][title["芯片批号"]],
				Factory:         "qipai",
				TestProgram:     "",
				PBI:             rows[i][title["客户订单编号"]],
				SubBatch:        rows[i][title["组装批次"]],
				OrderDate:       orderDate,
				Seal:            strings.ReplaceAll(tcSFDFile.TcSfdUd02, "'", ""),
				TestQuantity:    rows[i][title["订单数量"]],
				PassQuantity:    rows[i][title["入库良品数量"]],
				PassProbability: rows[i][title["总良率"]],
				Step:            "FT",
			})
		}
		return nil
	})
	global.PostGreSQL.Create(&fTReports)
}

func QingYiFT(filePath string) {
	file, _ := excelize.OpenFile(filePath)
	sheetName := "Test " + time.Now().Format("01") + "月"
	rows, err := file.GetRows(sheetName)
	if err != nil {
		log.Println(err)
	}
	if len(rows) < 4 {
		log.Println("Sheet ", sheetName, "格式不规范")
		return
	}
	title := make(map[string]int)
	for index, cell := range rows[2] {
		title[cell] = index
	}
	var fTReports []*model.Report
	_ = global.Oracle.Transaction(func(tx *gorm.DB) error {
		for i := 3; i < len(rows); i++ {
			var tcSFDFile *model.TcSfdFile
			tx.Where("tc_sfd01 = ? AND tc_sfd06 = ?", rows[i][title["订单号"]], rows[i][title["下单批号"]]).Find(&tcSFDFile)
			orderDate := carbon.Parse("20" + strings.Split(rows[i][title["订单号"]], "-")[1][:6]).Format("Y-m-d")
			fTReports = append(fTReports, &model.Report{
				Product:         rows[i][title["产品型号"]],
				Lot:             rows[i][title["下单批号"]],
				Factory:         "qingyi",
				TestProgram:     rows[i][title["测试程序"]],
				PBI:             rows[i][title["订单号"]],
				SubBatch:        rows[i][title["厂内批号"]],
				OrderDate:       orderDate,
				Seal:            strings.ReplaceAll(tcSFDFile.TcSfdUd02, "'", ""),
				TestQuantity:    rows[i][title["测试投入数"]],
				PassQuantity:    rows[i][title["测试良品数"]],
				PassProbability: rows[i][title["良率"]],
				Step:            "FT",
			})
		}
		return nil
	})
	global.PostGreSQL.Create(&fTReports)
}

func RuiSiFT(filePath string) {
	file, _ := excelize.OpenFile(filePath)
	sheetName := file.GetSheetName(1)
	rows, err := file.GetRows(sheetName)
	if err != nil {
		log.Println(err)
	}
	if len(rows) < 2 {
		log.Println("Sheet ", sheetName, "格式不规范")
		return
	}
	title := make(map[string]int)
	for index, cell := range rows[0] {
		title[strings.ReplaceAll(cell, " ", "")] = index
	}
	var fTReports []*model.Report
	for i := 1; i < len(rows); i++ {
		if rows[i][0] == "" {
			break
		}
		orderDate := carbon.Parse("20" + strings.Split(rows[i][title["订单号码"]], "-")[1][:6]).Format("Y-m-d")
		quantityDecimal, _ := decimal.NewFromString(rows[i][title["数量"]])
		failDecimal, _ := decimal.NewFromString(rows[i][title["不良品数量"]])
		missingDecimal, _ := decimal.NewFromString(rows[i][title["缺失数量"]])
		fTReports = append(fTReports, &model.Report{
			Product:         rows[i][title["产品型号"]],
			Lot:             rows[i][title["LOTNO"]],
			Factory:         "ruisi",
			TestProgram:     rows[i][title["测试程序"]],
			PBI:             rows[i][title["订单号码"]],
			SubBatch:        rows[i][title["厂内批号"]],
			OrderDate:       orderDate,
			Seal:            rows[i][title["印字内容"]],
			TestQuantity:    rows[i][title["数量"]],
			PassQuantity:    quantityDecimal.Sub(failDecimal).Sub(missingDecimal).String(),
			PassProbability: rows[i][title["良率"]],
			Step:            "FT",
		})
	}
	global.PostGreSQL.Create(&fTReports)
}

func SuQianChangDianFT(filePath string) {
	file, _ := excelize.OpenFile(filePath)
	sheetName := file.GetSheetName(0)
	rows, err := file.GetRows(sheetName)
	if err != nil {
		log.Println(err)
	}
	if len(rows) < 2 {
		log.Println("Sheet ", sheetName, "格式不规范")
		return
	}
	title := make(map[string]int)
	for index, cell := range rows[0] {
		title[strings.ReplaceAll(cell, "\n", "")] = index
	}
	var fTReports []*model.Report
	_ = global.Oracle.Transaction(func(tx *gorm.DB) error {
		for i := 1; i < len(rows); i++ {
			var tcSFDFile *model.TcSfdFile
			tx.Where("tc_sfd01 = ? AND tc_sfd06 = ?", rows[i][title["订单号"]], rows[i][title["下单批号"]]).Find(&tcSFDFile)
			orderDate := carbon.Parse("20" + strings.Split(rows[i][title["客户订单号order number"]], "-")[1][:6]).Format("Y-m-d")
			fTReports = append(fTReports, &model.Report{
				Product:         rows[i][title["产品型号Device"]],
				Lot:             rows[i][title["客户批号Customer lot"]],
				Factory:         "changdian",
				TestProgram:     rows[i][title["测试程序test program"]],
				PBI:             rows[i][title["客户订单号order number"]],
				SubBatch:        rows[i][title["扩散批号wafer lot"]],
				OrderDate:       orderDate,
				Seal:            strings.ReplaceAll(tcSFDFile.TcSfdUd02, "'", ""),
				TestQuantity:    rows[i][title["投料量order quantity"]],
				PassQuantity:    rows[i][title["测试流出数Test out"]],
				PassProbability: rows[i][title["测试良率yield of test"]],
				Step:            "FT",
			})
		}
		return nil
	})
	global.PostGreSQL.Create(&fTReports)
}

func SMCCP(filePath string) {
	file, _ := excelize.OpenFile(filePath)
	sheetName := file.GetSheetName(0)
	rows, err := file.GetRows(sheetName)
	if err != nil {
		log.Println(err)
	}
	if len(rows) < 7 {
		log.Println("Sheet ", sheetName, "格式不规范")
		return
	}
	title := make(map[string]int)
	for index, cell := range rows[0] {
		title[cell] = index
	}
	rows[2][0] = strings.ReplaceAll(rows[2][0], "：", ":")
	rows[2][0] = strings.ReplaceAll(rows[2][0], ": ", ":")
	infosArray := strings.Split(rows[2][0], " ")
	m := make(map[string]string)
	for _, infos := range infosArray {
		if infos != "" {
			info := strings.Split(infos, ":")
			m[info[0]] = info[1]
		}
	}
	var cpReports []*model.Report
	_ = global.Oracle.Transaction(func(tx *gorm.DB) error {
		for i := 1; i < len(rows); i++ {
			if rows[i][0] == "Total" {
				break
			}
			var tcSFDFile *model.TcSfdFile
			tx.Where("tc_sfd01 = ? AND tc_sfd06 = ?", m["型号"], m["批号"]).Find(&tcSFDFile)
			orderDate := carbon.Parse("20" + strings.Split(rows[i][title["客户订单号order number"]], "-")[1][:6]).Format("Y-m-d")
			cpReports = append(cpReports, &model.Report{
				Product:         m["出货型号"],
				Lot:             rows[i][title["客户批号Customer lot"]],
				Factory:         "saimeike",
				TestProgram:     m["测试程序名"],
				PBI:             "",
				OrderDate:       orderDate,
				TestQuantity:    rows[i][title["Total"]],
				PassQuantity:    rows[i][title["Pass"]],
				PassProbability: rows[i][title["良率(%)"]],
				WaferID:         rows[i][title["片号"]],
				TestTime:        rows[i][title["测试时间"]],
				Step:            m["测试工步"],
			})
		}
		return nil
	})
	global.PostGreSQL.Create(&cpReports)
}

func SaveCP(report *model.Report) { //pbi, product, lot, waferID string,
	var fileHandle *model.FileHandled
	global.PostGreSQL.Where("pbi = ? AND product = ? AND lot = ? AND wafer_id = ?", report.PBI, report.Product, report.Lot, report.WaferID).
		Find(&fileHandle)

	f, err := os.Open(fileHandle.Path)
	if err != nil {
		log.Println(err)
		return
	}
	defer f.Close()
	scanner := bufio.NewScanner(f)
	datas := make([][]string, 0)
	fieldMap := make(map[string]int)
	for scanner.Scan() {
		line := scanner.Text()
		s := strings.Split(line, ",")
		datas = append(datas, s[:len(s)-1])
		if len(datas) == 1 {
			for index, cell := range s {
				if index == len(s)-1 {
					continue
				}
				fieldMap[cell] = index
			}
		}
	}
	if len(datas) >= 1 {
		datas = datas[1:]
	}

	//sbinHbinMap := make(map[string]map[string]string)
	sbinHbinMap := make(map[string]string)
	sbinHbinByte := []byte(fileHandle.SbinHbin)
	_ = json.Unmarshal(sbinHbinByte, &sbinHbinMap)
	binCounter := make(map[string]int)
	firstBinCounter := make(map[string]int)
	firstBinCounters := make(map[string]map[string]int)
	var latestDatas [][]string
	var firstDatas [][]string
	for _, data := range datas {
		isAppendData := true
		for _, mainData := range latestDatas {
			if mainData[0] == data[fieldMap["X_COORD"]] && mainData[1] == data[fieldMap["Y_COORD"]] {
				mainData = []string{data[fieldMap["X_COORD"]], data[fieldMap["Y_COORD"]],
					data[fieldMap["SITE_NUM"]], data[fieldMap["SOFT_BIN"]]}
				isAppendData = false
				break
			}
		}
		if isAppendData {
			latestDatas = append(latestDatas, []string{data[fieldMap["X_COORD"]], data[fieldMap["Y_COORD"]], data[fieldMap["SITE_NUM"]],
				data[fieldMap["SOFT_BIN"]]})
			firstDatas = append(firstDatas, []string{data[fieldMap["X_COORD"]], data[fieldMap["Y_COORD"]], data[fieldMap["SITE_NUM"]],
				data[fieldMap["SOFT_BIN"]]})
		}
	}
	for _, mainData := range latestDatas {
		binCounter[mainData[3]]++
		//if _, ok := siteCounters[mainData[2]]; !ok {
		//	siteCounters[mainData[2]] = make(map[string]int)
		//}
		//siteCounters[mainData[2]][mainData[3]]++
	}
	for _, siteData := range firstDatas {
		firstBinCounter[siteData[3]]++
		if _, ok := firstBinCounters[siteData[2]]; !ok {
			firstBinCounters[siteData[2]] = make(map[string]int)
		}
		firstBinCounters[siteData[2]][siteData[3]]++
	}

	firstPassProbabilityDecimal := decimal.NewFromFloat(float64(firstBinCounter["1"]) / float64(len(firstDatas)) * 100).Round(2).String()
	passProbabilityDecimal := decimal.NewFromFloat(float64(binCounter["1"]) / float64(len(latestDatas)) * 100).Round(2).String()
	global.PostGreSQL.Model(&report).Updates(map[string]interface{}{
		"test_quantity":          strconv.Itoa(len(latestDatas)),
		"first_pass_quantity":    strconv.Itoa(firstBinCounter["1"]),
		"first_pass_probability": firstPassProbabilityDecimal,
		"pass_quantity":          strconv.Itoa(binCounter["1"]),
		"pass_probability":       passProbabilityDecimal + "%",
	})
	var binFail []*model.BinFail
	for bin, num := range binCounter {
		binFail = append(binFail, &model.BinFail{
			ReportID: report.ID,
			HardBin:  sbinHbinMap[bin],
			//HardBin:  sbinHbinMap[bin]["HBin"],
			SoftBin:  bin,
			Quantity: strconv.Itoa(num),
		})
	}
	var siteReports []*model.ReportSites
	global.PostGreSQL.Transaction(func(tx *gorm.DB) error {
		for site := range firstBinCounters {
			siteReports = append(siteReports, &model.ReportSites{
				ReportID: report.ID,
				Site:     site,
			})
		}

		tx.Where("report_id = ?", report.ID).Delete(&model.ReportSites{})
		tx.Create(&siteReports)
		return nil
	})
	siteReports = []*model.ReportSites{}
	global.PostGreSQL.Where("report_id = ?", report.ID).Find(&siteReports)
	for _, siteReport := range siteReports {
		var sum, pass int
		for bin, num := range firstBinCounters[siteReport.Site] {
			sum += num
			if bin == "1" {
				pass += num
			}
			binFail = append(binFail, &model.BinFail{
				ReportID:     report.ID,
				ReportSiteID: siteReport.ID,
				HardBin:      sbinHbinMap[bin],
				//HardBin:      sbinHbinMap[bin]["HBin"],
				SoftBin:  bin,
				Quantity: strconv.Itoa(num),
			})
		}
		sumDecimal := decimal.NewFromInt(int64(sum))
		passDecimal := decimal.NewFromInt(int64(pass))
		global.PostGreSQL.Model(&siteReport).Updates(map[string]interface{}{
			"test_quantity":    sumDecimal.String(),
			"pass_quantity":    passDecimal.String(),
			"pass_probability": passDecimal.Div(sumDecimal).Mul(decimal.NewFromInt(100)).Round(2).String() + "%",
		})
	}
	global.PostGreSQL.Transaction(func(tx *gorm.DB) error {
		tx.Where("report_id = ?", report.ID).Delete(&model.BinFail{})
		tx.Create(&binFail)
		return nil
	})
}

func SaveFT(report *model.Report) { //pbi, product, lot, subBatch string,
	var ftFile *model.FileHandled
	global.PostGreSQL.Where("pbi = ? AND product = ? AND lot = ? AND sub_batch = ? AND step = ?",
		report.PBI, report.Product, report.Lot, report.SubBatch, "FT").Find(&ftFile)
	fieldMap := make(map[string]int)
	sbinHbinMap := make(map[string]string)
	binCounter := make(map[string]int)
	siteCounters := make(map[string]map[string]int)
	datas := make([][]string, 0)
	if _, err := os.Stat(ftFile.Path); err != nil {
		log.Println("该文件不存在")
	}

	ft, err := os.Open(ftFile.Path)
	if err != nil {
		log.Println(ftFile.Path, ":", err)
		return
	}
	scanner := bufio.NewScanner(ft)
	for scanner.Scan() {
		line := scanner.Text()
		s := strings.Split(line, ",")
		datas = append(datas, s[:len(s)-1])
		if len(datas) == 1 {
			for index, cell := range s {
				if index == len(s)-1 {
					continue
				}
				fieldMap[cell] = index
			}
		}
	}
	if len(datas) >= 1 {
		datas = datas[1:]
	}

	for _, data := range datas {
		binCounter[data[fieldMap["SOFT_BIN"]]]++
		if _, ok := siteCounters[data[fieldMap["SITE_NUM"]]]; !ok {
			siteCounters[data[fieldMap["SITE_NUM"]]] = make(map[string]int)
		}
		siteCounters[data[fieldMap["SITE_NUM"]]][data[fieldMap["SOFT_BIN"]]]++
	}
	ft.Close()
	//sbinHbinMap := make(map[string]map[string]string)
	sbinHbinByte := []byte(ftFile.SbinHbin)
	_ = json.Unmarshal(sbinHbinByte, &sbinHbinMap)

	var rtFile *model.FileHandled
	var passCounter int
	if !errors.Is(global.PostGreSQL.Where("pbi = ? AND product = ? AND lot = ? AND sub_batch = ? AND step = ?",
		report.PBI, report.Product, report.Lot, report.SubBatch, "RT").
		Find(&rtFile).Error, gorm.ErrRecordNotFound) {

		rtFieldMap := make(map[string]int)

		rtDatas := make([][]string, 0)
		rt, err := os.Open(rtFile.Path)
		if err != nil {
			log.Println(err)
		}
		rtScanner := bufio.NewScanner(rt)
		for rtScanner.Scan() {
			line := rtScanner.Text()
			s := strings.Split(line, ",")
			rtDatas = append(rtDatas, s[:len(s)-1])
			if len(rtDatas) == 1 {
				for index, cell := range s {
					if index == len(s)-1 {
						continue
					}
					rtFieldMap[cell] = index
				}
			}
		}
		rt.Close()
		if len(rtDatas) >= 1 {
			rtDatas = rtDatas[1:]
		}

		for _, rtData := range rtDatas {
			if rtData[rtFieldMap["SOFT_BIN"]] == "1" {
				passCounter++
			}
		}

	}

	firstPassProbabilityDecimal := decimal.NewFromFloat(float64(binCounter["1"]) / float64(len(datas)) * 100).Round(2).String()
	passProbabilityDecimal := decimal.NewFromFloat(float64(binCounter["1"]+passCounter) / float64(len(datas)) * 100).Round(2).String()
	global.PostGreSQL.Model(&report).Updates(map[string]interface{}{
		"test_quantity":          strconv.Itoa(len(datas)),
		"first_pass_quantity":    strconv.Itoa(binCounter["1"]),
		"first_pass_probability": firstPassProbabilityDecimal + "%",
		"pass_quantity":          strconv.Itoa(binCounter["1"] + passCounter),
		"pass_probability":       passProbabilityDecimal + "%",
	})
	var binFail []*model.BinFail
	for bin, num := range binCounter {
		binFail = append(binFail, &model.BinFail{
			ReportID: report.ID,
			HardBin:  sbinHbinMap[bin],
			//HardBin:  sbinHbinMap[bin]["HBin"],
			SoftBin:  bin,
			Quantity: strconv.Itoa(num),
		})
	}
	var siteReports []*model.ReportSites
	global.PostGreSQL.Transaction(func(tx *gorm.DB) error {
		for site := range siteCounters {
			siteReports = append(siteReports, &model.ReportSites{
				ReportID: report.ID,
				Site:     site,
			})
		}
		tx.Where("report_id = ?", report.ID).Delete(&model.ReportSites{})
		tx.Create(&siteReports)
		return nil
	})
	siteReports = []*model.ReportSites{}
	global.PostGreSQL.Where("report_id = ?", report.ID).Find(&siteReports)
	for _, siteReport := range siteReports {
		var sum, pass int
		for bin, num := range siteCounters[siteReport.Site] {
			sum += num
			if bin == "1" {
				pass += num
			}
			binFail = append(binFail, &model.BinFail{
				ReportID:     report.ID,
				ReportSiteID: siteReport.ID,
				HardBin:      sbinHbinMap[bin],
				//HardBin:      sbinHbinMap[bin]["HBin"],
				SoftBin:  bin,
				Quantity: strconv.Itoa(num),
			})
		}
		sumDecimal := decimal.NewFromInt(int64(sum))
		passDecimal := decimal.NewFromInt(int64(pass))
		var passProbability string
		if !sumDecimal.IsZero() {
			passProbability = passDecimal.Div(sumDecimal).Mul(decimal.NewFromInt(100)).Round(2).String() + "%"
		}
		global.PostGreSQL.Model(&siteReport).Updates(map[string]interface{}{
			"test_quantity":    sumDecimal.String(),
			"pass_quantity":    passDecimal.String(),
			"pass_probability": passProbability,
		})
	}
	global.PostGreSQL.Transaction(func(tx *gorm.DB) error {
		tx.Where("report_id = ?", report.ID).Delete(&model.BinFail{})
		tx.Create(&binFail)
		return nil
	})
}