package limsreportbeampumpingunits

import (
	"dashoo.cn/backend/api/business/limscreatereport"
	"fmt"
	"os"
	"regexp"
	"strconv"
	"strings"

	"dashoo.cn/backend/api/business/limsentrust"

	//"strconv"

	//"strings"

	"time"

	. "dashoo.cn/backend/api/mydb"
	"dashoo.cn/utils"

	//. "dashoo.cn/utils/db"
	. "strings"

	"dashoo.cn/backend/api/business/limsdataentry"
	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/go-xorm/xorm"
	. "github.com/linxGnu/goseaweedfs"
)

type LimsReportBeamPumpingUnitsService struct {
	MyServiceBase
}

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

// 游梁式抽油机检测数据记录表存基本数据到数据库
func (s *LimsReportBeamPumpingUnitsService) AddDataBeamPumpingUnits(xlsx *excelize.File, balancelist limsdataentry.ApplyListModel, tablname string, username string, userid string, parentid int, photo string) {
	var rowlist LimsReportBeamPumpingUnits
	sheetmap := xlsx.GetSheetMap()
	for i := 2; i <= len(sheetmap); i++ {
		rowlist.EId = balancelist.EId
		rowlist.TaskBalanceId = balancelist.Id
		rowlist.CreateBy = username
		rowlist.CreateUserId, _ = utils.StrTo(userid).Int()
		rowlist.DataEntryId = parentid
		fmt.Println("------list---", rowlist)
		rowlist.CreateOn = time.Now()

		rowlist.CustNo = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C7")        //出厂编号
		rowlist.PositionCheck = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C6") //出厂编号

		//rowlist.ProductDate = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "M7") //出厂日期

		rowlist.Spec = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C8") //规格型号

		rowlist.Manufacturer = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "M8") //制造厂家

		//rowlist.CheckDate = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C9") //检测时间

		rowlist.Temperature = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "M9") //环境温度C

		rowlist.DetectBasis = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C10") //检测依据

		rowlist.MainProducts = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C11") //主要检测仪器名称

		rowlist.FaceCheck1 = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "R13") //外观检查1

		rowlist.FaceCheck2 = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "R14") //外观检查2

		rowlist.Retarder = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "R15")                   //减速器密封性
		rowlist.LongSwing = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C17"))     //支架纵向振幅mm
		rowlist.TransSwing = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "C18"))    //支架横向振幅mm
		rowlist.RetarderTemp = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "N17"))  //减速器轴承温度C
		rowlist.MachineNoise1 = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "N18")) //整机噪声1(A计权声级)dB
		rowlist.MachineNoise2 = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "Q18")) //整机噪声2(A计权声级)dB
		rowlist.MachineNoise3 = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "T18")) //整机噪声3(A计权声级)dB
		rowlist.DownX = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "E20"))         //x轴下止点mm
		rowlist.MidDownX = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "F20"))      //x轴中下止点mm
		rowlist.MiddleX = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "G20"))       //x轴中止点mm
		rowlist.MidUpX = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "H20"))        //x轴中上止点mm
		rowlist.UpX = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "K20"))           //x轴上止点mm
		rowlist.DownY = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "O20"))         //y轴下止点mm
		rowlist.MidDownY = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "P20"))      //y轴中下止点mm
		rowlist.MiddleY = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "R20"))       //y轴中止点mm
		rowlist.MidUpY = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "S20"))        //y轴中上止点mm
		rowlist.UpY = decimalDeal(xlsx.GetCellValue("sheet"+strconv.Itoa(i), "U20"))           //y轴上止点mm
		rowlist.Remark = xlsx.GetCellValue("sheet"+strconv.Itoa(i), "B23")

		_, err := s.InsertEntityBytbl(tablname, &rowlist)
		if err == nil {
			fmt.Println("======电气接地装置检测原始记录表存基本数据到数据库成功！")
		} else {
			fmt.Println("======电气接地装置检测原始记录表存基本数据到数据库失败！")
		}
		//插入审核人签名
		pictureerr := xlsx.AddPicture("sheet"+strconv.Itoa(i), "N26", "./"+photo, "")
		if pictureerr != nil {
			fmt.Println(pictureerr)
		}
	}
}

// 针对数据库 decimal 类型，空串处理，若空则返回 "0.00"，否则返回原字符
func decimalDeal(str string) string {
	_, err := strconv.Atoi(TrimSpace(str))
	if err == nil {
		return TrimSpace(str)
	} else {
		return "0.00"
	}
}

/**
 * 生成[游梁式抽油机]检测报告
 */
func (s *LimsReportBeamPumpingUnitsService) DataToExcelReport(
	entrustMainEntity limsentrust.LimsEntrustMain,
	reportBeamPumpingUnitsName string,
	dataentryName string,
	creatreportName string,
	BaseUserName string,
	seqStr string,
	retUrl string,
	entrustId string,
	photo string,
	dataentryIds string) (reportUrl string, err error) {

	//游梁式抽油机检测数据记录表
	var reportList []LimsReportBeamPumpingUnits
	s.GetEntitysByOrderbyWhere(reportBeamPumpingUnitsName, "DataEntryId in ("+dataentryIds+")","DataEntryId", &reportList)
	//模板下载到服务器
	_dir := utils.Cfg.MustValue("file", "tmplateDir") + entrustId
	fileurl := strings.Split(retUrl, "|")
	utils.DownloadFile("http://"+fileurl[0], fileurl[1], _dir)

	xlsx, err := excelize.OpenFile(utils.Cfg.MustValue("file", "tmplateDir") + entrustId + "/" + fileurl[1])
	if err != nil {
		utils.LogError(err)
		return "", err
	}
	tmpDocName := entrustId + "_" + strconv.Itoa(time.Now().Nanosecond()) + ".xlsx"
	//提前生成文件在服务器的存储路径
	_pdir := utils.Cfg.MustValue("file", "docDir") + entrustId + "/"
	exist, _ := utils.PathExists(_pdir)
	var dirErr error
	if !exist {
		dirErr = os.Mkdir(_pdir, os.ModePerm)
	}
	if dirErr != nil {
		utils.LogError(err)
		return "", dirErr
	}
	//1. 插入Excel自动生成的报告单号
	xlsx.SetCellValue("Sheet1", "F1", seqStr)
	//2. 插入Excel工作委托的相关字段 entrustMainEntity
	xlsx.SetCellValue("Sheet1", "C22", entrustMainEntity.ProjectType)
	xlsx.SetCellValue("Sheet1", "C24", entrustMainEntity.CustomerName)
	xlsx.SetCellValue("Sheet1", "C26", entrustMainEntity.DetectSample)
	xlsx.SetCellValue("Sheet1", "C28", time.Now().Format("2006 年 01 月 02 日"))

	//3. 复制报告Sheet
	sheetIdx := 3
	if len(reportList) > 1 {
		for i := 1; i < (len(reportList)); i++ {
			index := i + sheetIdx
			xlsx.NewSheet(index, "Sheet"+strconv.Itoa(index))
			// index := xlsx.NewSheet("Sheet" + strconv.Itoa(i+sheetIdx))
			xlsx.CopySheet(3, index)
		}
	}
	var seqpage string
	for i := 0; i < (len(reportList)); i++ {
		var reportmainlist limsdataentry.LimsDateEntry
		var createreportlist limscreatereport.LimsCreateReport
		s.GetEntityByWhere(dataentryName," Id= "+utils.ToStr(reportList[i].DataEntryId),&reportmainlist)
		s.GetEntityByWhere(creatreportName," EId = "+ entrustId,&createreportlist)
		//获取校核人签名
		var jhuserlist limsdataentry.UserModel
		s.GetEntityByWhere(BaseUserName,"Id = '"+utils.ToStr(reportmainlist.JHUserId)+"'",&jhuserlist)
		//获取批准人签名
		var pzuserlist limsdataentry.UserModel
		s.GetEntityByWhere(BaseUserName,"Id = '"+utils.ToStr(createreportlist.ConUserId)+"'",&pzuserlist)
		qulify := 1
		var DetectBasis string
		if strings.Contains(reportmainlist.DetectBasis,",") == true {
			DetectBasis = strings.Replace(reportmainlist.DetectBasis, ",", " ", -1)
		} else{
			DetectBasis = reportmainlist.DetectBasis
		}
		specification := reportList[i].Spec
		Specif := s.IsEnglishCap(specification)
		//规格型号拆分
		Specifsplit := strings.Split(Specif, "-")
		load, _ := strconv.Atoi(Specifsplit[0])
		conflict, _ := strconv.ParseFloat(Specifsplit[1], 64)
		//整机噪声
		noise1, _ := strconv.ParseFloat(reportList[i].MachineNoise1, 64)
		noise2, _ := strconv.ParseFloat(reportList[i].MachineNoise2, 64)
		noise3, _ := strconv.ParseFloat(reportList[i].MachineNoise3, 64)
		noise := noise1 + noise2 + noise3
		if load <= 10 {
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D17", "<=85")
			if noise > 85 {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I17", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I17", "合格")
			}
		} else if load > 10 {
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D17", "<=87")
			if noise > 87 {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I17", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I17", "合格")
			}
		}
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K11",reportList[i].FaceCheck1 )//外观一结果
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K12",reportList[i].FaceCheck2 )//外观二结果
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K13",reportList[i].RDCResult)//密封性结果
		//LongSwingSplict := strings.Split(reportList[i].LongSwing, "/")
		//TransSwingSplict := strings.Split(reportList[i].TransSwing, "/")
		LongSwing1, _ := strconv.ParseFloat(reportList[i].LongSwing, 64)
		LongSwing2, _ := strconv.ParseFloat(strconv.Itoa(reportList[i].LSBaseNum), 64)
		TransSwing1, _ := strconv.ParseFloat(reportList[i].TransSwing, 64)
		TransSwing2, _ := strconv.ParseFloat(strconv.Itoa(reportList[i].TSBaseNum), 64)
		LongSwing := LongSwing2 - LongSwing1
		TransSwing := TransSwing2 - TransSwing1
		down, _ := strconv.ParseFloat(reportList[i].DownX, 64)
		up, _ := strconv.ParseFloat(reportList[i].UpX, 64)
		radius := up - down
		switch {
		case conflict <= 2.00:
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D14", "<=3")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D15", "<=2")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D18", "<=10")
			if LongSwing > 3 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "合格")
			}
			if TransSwing > 2 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "合格")
			}
			if radius > 10 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "合格")
			}
		case conflict <= 3.00 && conflict > 2.00:
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D14", "<=5")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D15", "<=4")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D18", "<=14")
			if LongSwing > 5 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "合格")
			}
			if TransSwing > 4 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "合格")
			}
			if radius > 14 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "合格")
			}
		case conflict <= 4.00 && conflict > 3.00:
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D14", "<=6")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D15", "<=5")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D18", "<=18")
			if LongSwing > 6{
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "合格")
			}
			if TransSwing > 5 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "合格")
			}
			if radius > 18 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "合格")
			}
		case conflict <= 5.00 && conflict > 4.00:
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D14", "<=7")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D15", "<=6")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D18", "<=22")
			if LongSwing > 7  {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "合格")
			}
			if TransSwing > 6 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "合格")
			}
			if radius > 22 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "合格")
			}
		case conflict <= 7.00 && conflict > 5.00:
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D14", "<=8")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D15", "<=7")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D18", "<=26")
			if LongSwing > 8 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "合格")
			}
			if TransSwing > 7 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "合格")
			}
			if radius > 26 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "合格")
			}
		case conflict > 7.00:
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D14", "<=9")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D15", "<=8")
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D18", "<=28")
			if LongSwing > 9  {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I14", "合格")
			}
			if TransSwing > 8 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I15", "合格")
			}
			if radius > 28 {
				qulify = 0
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "不合格")
			} else {
				xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I18", "合格")
			}
		}
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "D16", "<=70°")
		Temperature, _ := strconv.ParseFloat(reportList[i].Temperature, 64)
		if Temperature >70.00{
			qulify = 0
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I16", "不合格")
		}else{
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "I16", "合格")
		}
		if qulify == 1 {
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C19", "    依据《"+DetectBasis+"》标准，检测数据符合标准要求，检测结论：合格。")
		} else if qulify == 0 {
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C19", "    依据《"+DetectBasis+"》标准，检测数据不符合标准要求，检测结论：不合格。")
		}
		//插入excel文件
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "A1", entrustMainEntity.ProjectType+"报告") //检测项目
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F31", time.Now().Format("2006年01月02日"))
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G2", "第"+strconv.Itoa(i+3)+"页  共"+strconv.Itoa(len(reportList)+2)+"页")
		if i<10{
			seqpage ="0"+strconv.Itoa(i+1)
		}else{
			seqpage =strconv.Itoa(i+1)
		}
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "A2", seqStr+"-"+seqpage)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C3", entrustMainEntity.CustomerName)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C4", entrustMainEntity.AddressName)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C5", reportList[i].CustNo)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G5", reportList[i].ProductDate.Format("2006年01月02日"))
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C6", reportList[i].Spec)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G6", reportList[i].Manufacturer)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C7", reportList[i].CheckDate.Format("2006年01月02日"))
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G7", reportmainlist.Temperature)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C8", DetectBasis)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C9", reportmainlist.Instrument1)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G11", reportList[i].FaceCheck1)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G12", reportList[i].FaceCheck2)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G13", reportList[i].Retarder)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G14", LongSwing)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G15", TransSwing)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G16", reportList[i].RetarderTemp)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G17", noise)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G18", radius)
		//xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "B33", reportmainlist.CreateBy) //主检人签名
		//xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "E33", reportmainlist.JHBy) //校核人签名
		// if createreportlist.ConUserBy != ""{
		// 	xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H33", createreportlist.ConUserBy) //批准人签名
		// }
		//插入主检人签名
		pictureerr := xlsx.AddPicture("Sheet"+strconv.Itoa(i+sheetIdx), "B33", "./"+photo, `{"x_offset": 15, "y_offset": 6,"lock_aspect_ratio": false,"locked": false, "positioning": "absolute "}`)
		if pictureerr != nil {
			fmt.Println(pictureerr)
		}
		//插入校核人签名
		if jhuserlist.Description != ""{
			pictureerr = xlsx.AddPicture("Sheet"+strconv.Itoa(i+sheetIdx), "E33", "./"+jhuserlist.Description, `{"x_offset": 15, "y_offset": 6,"lock_aspect_ratio": false,"locked": false, "positioning": "absolute "}`)
			if pictureerr != nil {
				fmt.Println(pictureerr)
			}
		}
		//插入批准人签名
		if pzuserlist.Description != ""{
			pictureerr = xlsx.AddPicture("Sheet"+strconv.Itoa(i+sheetIdx), "H33", "./"+pzuserlist.Description, `{"x_offset": 15, "y_offset": 6,"lock_aspect_ratio": false,"locked": false, "positioning": "absolute "}`)
			if pictureerr != nil {
				fmt.Println(pictureerr)
			}
		}
	}

	//5. 将文件保存到服务器相关目录上
	xlsx.SaveAs(utils.Cfg.MustValue("file", "docDir") + entrustId + "/" + tmpDocName)

	//6. 上传文件到文件服务器
	DocFilePath := utils.Cfg.MustValue("file", "docDir") + entrustId + "/" + tmpDocName
	var sw *Seaweed
	var filer []string
	if _filer := os.Getenv("GOSWFS_FILER_URL"); _filer != "" {
		filer = []string{_filer}
	}
	sw = NewSeaweed("http", utils.Cfg.MustValue("file", "upFileHost"), filer, 2*1024*1024, 5*time.Minute)
	_, _, fID, err := sw.UploadFile(DocFilePath, "", "")
	retDocUrl := utils.Cfg.MustValue("file", "downFileHost") + "/" + fID
	//6.1 删除本地文件，释放空间
	os.Remove(DocFilePath)

	//7. 保存住报告URL(由controller层调用生成或修改生成报告记录)
	return retDocUrl, err
}

/**
 * 数据录入导出 [游梁式抽油机]
 */
func (s *LimsReportBeamPumpingUnitsService) DocDataToExcel(entrustMainEntity limsdataentry.DataEntryEntrustModel,dataentrymainlist limsdataentry.LimsDateEntry, reportEquipotent string, seqStr string, retUrl string, photo string,JHSign string) (reportUrl string, err error) {

	//获取报告明细列表
	var dataentrylist []LimsReportBeamPumpingUnits
	s.GetEntitysByWhere(reportEquipotent, "DataEntryId='"+utils.ToStr(dataentrymainlist.Id)+"'", &dataentrylist)
	//模板下载到服务器
	_dir := utils.Cfg.MustValue("file", "tmplateDir") + utils.ToStr(dataentrymainlist.Id)
	fileurl := strings.Split(retUrl, "|")
	utils.DownloadFile("http://"+fileurl[0], fileurl[1], _dir)

	xlsx, err := excelize.OpenFile(utils.Cfg.MustValue("file", "tmplateDir") + utils.ToStr(dataentrymainlist.Id) + "/" + fileurl[1])
	if err != nil {
		utils.LogError(err)
		return "", err
	}
	tmpDocName := utils.ToStr(dataentrymainlist.Id) + "_" + strconv.Itoa(time.Now().Nanosecond()) + ".xlsx"
	//提前生成文件在服务器的存储路径
	_pdir := utils.Cfg.MustValue("file", "docDir") + utils.ToStr(dataentrymainlist.Id) + "/"
	exist, _ := utils.PathExists(_pdir)
	var dirErr error
	if !exist {
		dirErr = os.Mkdir(_pdir, os.ModePerm)
	}
	if dirErr != nil {
		utils.LogError(err)
		return "", dirErr
	}
	//3. 复制报告Sheet
	sheetIdx := 2
	if len(dataentrylist) > 1 {
		for i := 1; i < (len(dataentrylist)); i++ {
			index := i + sheetIdx
			xlsx.NewSheet(index, "Sheet"+strconv.Itoa(index))
			// index := xlsx.NewSheet("Sheet" + strconv.Itoa(i+sheetIdx))
			xlsx.CopySheet(sheetIdx, index)
		}
	}

	for i := 0; i < (len(dataentrylist)); i++ {
		// xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "B26", dataentrymainlist.CreateBy) //主检人签名
		// xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N26", dataentrymainlist.JHBy) //校核人签名
		//插入检测人签名
		pictureerr := xlsx.AddPicture("Sheet"+strconv.Itoa(i+sheetIdx), "B26", "./"+photo, `{"x_offset": 2, "y_offset": 2,"lock_aspect_ratio": false,"locked": true, "positioning": "absolute "}`)
		if pictureerr != nil {
			fmt.Println(pictureerr)
		}
		if JHSign != "" {
			//插入校核人签名
			pictureerr = xlsx.AddPicture("Sheet"+strconv.Itoa(i+sheetIdx), "N26", "./"+JHSign, `{"x_offset": 2, "y_offset": 2,"lock_aspect_ratio": false,"locked": true, "positioning": "absolute "}`)
			if pictureerr != nil {
				fmt.Println(pictureerr)
			}
		}
		/*xlsx.ProtectSheet("Sheet"+strconv.Itoa(i+sheetIdx), &excelize.FormatSheetProtection{
			Password:      utils.Cfg.MustValue("file", "excelPassword"),
			EditScenarios: false,
		})*/
		noise1, _ := strconv.ParseFloat(dataentrylist[i].MachineNoise1, 64)
		noise2, _ := strconv.ParseFloat(dataentrylist[i].MachineNoise2, 64)
		noise3, _ := strconv.ParseFloat(dataentrylist[i].MachineNoise3, 64)
		noisesum := noise1 + noise2 + noise3
		noise := noisesum/3
		//插入excel文件
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "A2", entrustMainEntity.ProjectType+"数据记录表")  //检测项目
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C4", seqStr)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C5", entrustMainEntity.CustomerName)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C6", dataentrymainlist.PositionCheck)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C7", dataentrylist[i].CustNo)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "M7", dataentrylist[i].ProductDate.Format("2006年01月02日"))
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C8", dataentrylist[i].Spec)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "M8", dataentrylist[i].Manufacturer)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C9", dataentrylist[i].CheckDate.Format("2006年01月02日"))
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "M9", dataentrylist[i].Temperature+"℃")
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C10", dataentrymainlist.DetectBasis)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C11", dataentrymainlist.Instrument1)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "R13", dataentrylist[i].FaceCheck1)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "R14", dataentrylist[i].FaceCheck2)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "R15", dataentrylist[i].Retarder)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C17", dataentrylist[i].LongSwing+"mm")
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C18", dataentrylist[i].TransSwing+"mm")
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N17", dataentrylist[i].RetarderTemp+"℃")
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N18", dataentrylist[i].MachineNoise1+"dB")
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "Q18", dataentrylist[i].MachineNoise2+"dB")
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "T18", dataentrylist[i].MachineNoise3+"dB")
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "E20", dataentrylist[i].DownX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F20", dataentrylist[i].MidDownX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G20", dataentrylist[i].MiddleX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H20", dataentrylist[i].MidUpX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K20", dataentrylist[i].UpX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "O20", dataentrylist[i].DownY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "P20", dataentrylist[i].MidDownY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "R20", dataentrylist[i].MiddleY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "S20", dataentrylist[i].MidUpY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "U20", dataentrylist[i].UpY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "B23", dataentrylist[i].Remark)

		//备注页
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C30", dataentrylist[i].LongSwing)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C31", dataentrylist[i].TransSwing)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "C32", noise)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F38", dataentrylist[i].DownX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G38", dataentrylist[i].MidDownX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H38", dataentrylist[i].MiddleX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K38", dataentrylist[i].MidUpX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N38", dataentrylist[i].UpX)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F39", dataentrylist[i].DownY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G39", dataentrylist[i].MidDownY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H39", dataentrylist[i].MiddleY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K39", dataentrylist[i].MidUpY)
		xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N39", dataentrylist[i].UpY)
		DownX, _ := strconv.ParseFloat(dataentrylist[i].DownX, 64)
		MidDownX, _ := strconv.ParseFloat(dataentrylist[i].MidDownX, 64)
		MiddleX, _ := strconv.ParseFloat(dataentrylist[i].MiddleX, 64)
		MidUpX, _ := strconv.ParseFloat(dataentrylist[i].MidUpX, 64)
		UpX, _ := strconv.ParseFloat(dataentrylist[i].UpX, 64)
		DownY, _ := strconv.ParseFloat(dataentrylist[i].DownY, 64)
		MidDownY, _ := strconv.ParseFloat(dataentrylist[i].MidDownY, 64)
		MiddleY, _ := strconv.ParseFloat(dataentrylist[i].MiddleY, 64)
		MidUpY, _ := strconv.ParseFloat(dataentrylist[i].MidUpY, 64)
		UpY, _ := strconv.ParseFloat(dataentrylist[i].UpY, 64)
		if DownX == 0{
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F44", dataentrylist[i].DownX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G44", dataentrylist[i].MidDownX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H44", dataentrylist[i].MiddleX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K44", dataentrylist[i].MidUpX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N44", dataentrylist[i].UpX)
		}else{
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F44", DownX-DownX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G44", MidDownX-DownX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H44", MiddleX-DownX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K44", MidUpX-DownX)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N44", UpX-DownX)
		}
		if DownY == 0{
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F45", dataentrylist[i].DownY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G45", dataentrylist[i].MidDownY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H45", dataentrylist[i].MiddleY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K45", dataentrylist[i].MidUpY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N45", dataentrylist[i].UpY)
		}else{
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "F45", DownY-DownY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "G45", MidDownY-DownY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "H45", MiddleY-DownY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "K45", MidUpY-DownY)
			xlsx.SetCellValue("Sheet"+strconv.Itoa(i+sheetIdx), "N45", UpY-DownY)
		}

	}


	//4. 将文件保存到服务器相关目录上
	xlsx.SaveAs(utils.Cfg.MustValue("file", "docDir") + utils.ToStr(dataentrymainlist.Id) + "/" + tmpDocName)

	//5. 上传文件到文件服务器
	DocFilePath := utils.Cfg.MustValue("file", "docDir") + utils.ToStr(dataentrymainlist.Id) + "/" + tmpDocName
	var sw *Seaweed
	var filer []string
	if _filer := os.Getenv("GOSWFS_FILER_URL"); _filer != "" {
		filer = []string{_filer}
	}
	sw = NewSeaweed("http", utils.Cfg.MustValue("file", "upFileHost"), filer, 2*1024*1024, 5*time.Minute)
	_, _, fID, err := sw.UploadFile(DocFilePath, "", "")
	retDocUrl := utils.Cfg.MustValue("file", "downFileHost") + "/" + fID
	//6. 删除本地文件，释放空间
	os.Remove(DocFilePath)
	//7. 保存住报告URL(由controller层调用生成或修改生成报告记录)
	return retDocUrl, err
}

//型号字母去除
func (s *LimsReportBeamPumpingUnitsService) IsEnglishCap(str string) string {
	pat := `[[:upper:]]+`
	specification := str
	//r, _ := regexp.Compile(`[[:upper:]]+`)
	//fmt.Println(r.MatchString(specification))
	if ok, _ := regexp.MatchString(pat, specification); ok {
		fmt.Println("match found")
	}
	re, _ := regexp.Compile(pat)
	//将匹配到的部分替换为""
	Specif :=re.ReplaceAllString(specification, "")
	return  Specif
}