LAPP_Acura_MES_Backend/etl_task/BackFlushPackOrder.go

package main

import (
	dalBase "LAPP_ACURA_MOM_BACKEND/dao/base"
	dalJit "LAPP_ACURA_MOM_BACKEND/dao/jit"
	dalOm "LAPP_ACURA_MOM_BACKEND/dao/om"
	"LAPP_ACURA_MOM_BACKEND/etl_task/model"
	"LAPP_ACURA_MOM_BACKEND/grmi"
	meta "LAPP_ACURA_MOM_BACKEND/meta/base"
	"LAPP_ACURA_MOM_BACKEND/utils"
	"fmt"
	_ "github.com/denisenkom/go-mssqldb"
	_ "github.com/go-sql-driver/mysql"
	"github.com/go-xorm/xorm"
	"golang.org/x/text/encoding/simplifiedchinese"
	"golang.org/x/text/transform"
	"io/ioutil"
	"os"
	"path"
	"strconv"
	"strings"
	"time"
)

//BackFlushPackOrder.exe "server=101.201.121.115;database=LAPP_ACURA_MES;user id=sa;password=Leit2020;port=1433;encrypt=disable" "LAPP_ACURA_MES" "server=101.201.121.115;database=LAPP_ETL2;user id=sa;password=Leit2020;port=1433;encrypt=disable" "LAPP_ETL2" "98"

func main() {
	var err error
	var plantNr = 100
	var userId = "BackFlush"
	now := time.Now()
	year, month, day := now.Date()
	hour := now.Hour()
	minute := now.Minute()
	second := now.Second()
	// init global location time zone
	utils.TimezoneLocation, err = time.LoadLocation("Asia/Shanghai")
	if err != nil {
		fmt.Println("初始化时区错误:" + err.Error())
		return
	}
	if len(os.Args) < 5 {
		fmt.Println("参数长度错误!")
		return
	}
	var acuraDsn = os.Args[1]
	var etlDsn = os.Args[3]
	taskId, err := strconv.Atoi(os.Args[5])
	if err != nil {
		fmt.Println("接收到的taskId错误, taskId:" + os.Args[5])
		return
	}
	//etlDsn := "server=101.201.121.115;database=LAPP_ETL;user id=sa;password=Leit2020;port=1433;encrypt=disable"
	//acuraDsn := "server=101.201.121.115;database=LAPP_ACURA_MES3;user id=sa;password=Leit2020;port=1433;encrypt=disable"
	//taskId := 101
	// 初始化数据库连接
	etlDB, err := xorm.NewEngine("mssql", etlDsn)
	if err != nil {
		fmt.Println("初始化etl数据库引擎错误:" + err.Error())
		return
	}
	err = etlDB.Ping()
	if err != nil {
		fmt.Println("连接ETL数据库失败, error:" + err.Error())
		return
	}
	acuraDB, err := xorm.NewEngine("mssql", acuraDsn)
	if err != nil {
		fmt.Println("初始化讴歌数据库引擎错误:" + err.Error())
		return
	}
	err = acuraDB.Ping()
	if err != nil {
		fmt.Println("连接讴歌数据库失败, error:" + err.Error())
		return
	}
	acuraDB.ShowSQL(true)
	// 从etl数据库中获取参数
	var index1 string
	var index2 string
	var index3 string
	var index5 string
	var index8 string
	var tempIndex9 string
	var index12 string
	var dir string
	etlSession := etlDB.NewSession()
	defer etlSession.Close()
	shellParamLi := make([]model.ShellParam, 0)
	err = etlSession.Table("ShellParam").Where("TaskId = ?", taskId).Find(&shellParamLi)
	if err != nil {
		fmt.Println("查询任务需要参数失败, error:" + err.Error())
		return
	}
	for _, param := range shellParamLi {
		switch param.ParamName {
		case "index1":
			index1 = param.ParamValue
		case "index2":
			index2 = param.ParamValue
		case "index3":
			index3 = param.ParamValue
		case "index5":
			index5 = param.ParamValue
		case "index8":
			index8 = param.ParamValue
		case "index9":
			tempIndex9 = param.ParamValue
		case "index12":
			index12 = param.ParamValue
		case "path":
			dir = param.ParamValue
		}
	}
	// index1编码转换
	tempReader := transform.NewReader(strings.NewReader(index1), simplifiedchinese.GBK.NewEncoder())
	tempBytes, err := ioutil.ReadAll(tempReader)
	if err != nil {
		fmt.Println("转码失败, 错误:" + err.Error())
		return
	}
	index1 = string(tempBytes)
	// index2编码转换
	tempReader = transform.NewReader(strings.NewReader(index2), simplifiedchinese.GBK.NewEncoder())
	tempBytes, err = ioutil.ReadAll(tempReader)
	if err != nil {
		fmt.Println("转码失败, 错误:" + err.Error())
		return
	}
	index2 = string(tempBytes)
	// index3 编码转换
	tempReader = transform.NewReader(strings.NewReader(index3), simplifiedchinese.GBK.NewEncoder())
	tempBytes, err = ioutil.ReadAll(tempReader)
	if err != nil {
		fmt.Println("转码失败, 错误:" + err.Error())
		return
	}
	index3 = string(tempBytes)
	// index5 编码转换
	tempReader = transform.NewReader(strings.NewReader(index5), simplifiedchinese.GBK.NewEncoder())
	tempBytes, err = ioutil.ReadAll(tempReader)
	if err != nil {
		fmt.Println("转码失败, 错误:" + err.Error())
		return
	}
	index5 = string(tempBytes)
	// index8 编码转换
	tempReader = transform.NewReader(strings.NewReader(index8), simplifiedchinese.GBK.NewEncoder())
	tempBytes, err = ioutil.ReadAll(tempReader)
	if err != nil {
		fmt.Println("转码失败, 错误:" + err.Error())
		return
	}
	index8 = string(tempBytes)
	// index12 编码转换
	tempReader = transform.NewReader(strings.NewReader(index12), simplifiedchinese.GBK.NewEncoder())
	tempBytes, err = ioutil.ReadAll(tempReader)
	if err != nil {
		fmt.Println("转码失败, 错误:" + err.Error())
		return
	}
	index12 = string(tempBytes)
	filename := "bkfl" + fmt.Sprintf("%d%02d%02d%d", year, int(month), day, 3600*hour+60*minute+second) + "ACURA.csv"
	filepath := path.Join(dir, filename)
	acuraSession := acuraDB.NewSession()
	defer acuraSession.Close()
	nowStr := now.Format(grmi.DateTimeOutFormat)
	daoPackOrderItem := dalJit.NewPackOrderItemLstDAO(acuraSession, plantNr, userId)
	daoPackOrderStatus := dalJit.NewPackOrderStatusDAO(acuraSession, plantNr, userId)
	daoArticle := dalBase.NewArticleDAO(acuraSession, plantNr, userId)
	daoSerialOrder := dalOm.NewSerialOrderDAO(acuraSession, plantNr, userId)
	backFlushLi, err := daoPackOrderItem.GetUnFlushBackData(nowStr)
	if err != nil {
		fmt.Println("查询和更新包装单状态失败, error:" + err.Error())
		return
	}
	if len(backFlushLi) == 0 {
		fmt.Println("OK")
		return
	}
	ArtMap := make(map[string]int)
	serialOrderIdLi := make([]string, 0)
	packOrderIdLi := make([]string, 0)
	//serialOrderStartTime := time.Now()
	//serialOrderEndTime := time.Now()
	//packOrderStartTime := time.Now()
	//packOrderEndTime := time.Now()
	var currentPackOrderId string
	for index, item := range backFlushLi {
		if index == 0 {
			currentPackOrderId = item.PackOrderStatus.PackOrderId
			packOrderIdLi = append(packOrderIdLi, item.PackOrderStatus.PackOrderId)
		}
		_, exist := ArtMap[item.SerialOrder.ArtId]
		if !exist {
			ArtMap[item.SerialOrder.ArtId] = 1
		} else {
			ArtMap[item.SerialOrder.ArtId] += 1
		}
		//if index == 0 {
		//	serialOrderStartTime = item.SerialOrder.CreateTime.Restore()
		//	serialOrderEndTime = item.SerialOrder.CreateTime.Restore()
		//	packOrderStartTime = item.PackOrderStatus.CreateTime.Restore()
		//	packOrderEndTime = item.PackOrderStatus.CreateTime.Restore()
		//}
		//fmt.Println("item.SerialOrder:", item.SerialOrder.SerialOrderId)
		//fmt.Println("item.SerialOrder.CreateTime:", item.SerialOrder.CreateTime.Restore().String())
		//fmt.Println("serialOrderStartTime:", serialOrderStartTime.String())
		//fmt.Println("serialOrderEndTime:", serialOrderEndTime.String())
		//if item.SerialOrder.CreateTime.Restore().Before(serialOrderStartTime) {
		//	fmt.Println("here1")
		//	serialOrderStartTime = item.SerialOrder.CreateTime.Restore()
		//}
		//if serialOrderEndTime.Before(item.SerialOrder.CreateTime.Restore()) {
		//	fmt.Println("here2")
		//	serialOrderEndTime = item.SerialOrder.CreateTime.Restore()
		//}
		//if item.PackOrderStatus.CreateTime.Restore().Before(packOrderStartTime) {
		//	packOrderStartTime = item.PackOrderStatus.CreateTime.Restore()
		//}
		//if packOrderEndTime.Before(item.PackOrderStatus.CreateTime.Restore()) {
		//	packOrderEndTime = item.PackOrderStatus.CreateTime.Restore()
		//}
		serialOrderIdLi = append(serialOrderIdLi, item.SerialOrder.SerialOrderId)
		if currentPackOrderId != item.PackOrderStatus.PackOrderId {
			currentPackOrderId = item.PackOrderStatus.PackOrderId
			packOrderIdLi = append(packOrderIdLi, item.PackOrderStatus.PackOrderId)
		}
	}

	writeLi := make([]string, 0, len(ArtMap))
	for artId, qty := range ArtMap {
		//article, err := daoArticle.SelectOne(artId)
		articleLi, err := daoArticle.Select([]grmi.Predicate{meta.Article_ArtId.NewPredicate(grmi.Equal, artId)}, nil)
		if err != nil {
			fmt.Println("查询物料数据失败, error:" + err.Error())
			return
		}
		if len(articleLi) == 0 {
			_ = acuraSession.Rollback()
			fmt.Println("物料不存在, 物料ID:" + artId)
			return
		}
		article := articleLi[0]
		if !article.BackFlushItemToggle {
			continue
		}
		qadArtId := article.ArtSpec3
		if qadArtId == "" {
			fmt.Println("未获取到ERP零件号, 物料ID:" + artId)
			return
		}
		index6 := fmt.Sprintf("%02d%02d%d", int(month), day, 3600*hour+60*minute+second)
		index7 := fmt.Sprintf("%02d%02d%d", int(month), day, 3600*hour+60*minute+second)
		index9 := tempIndex9 + fmt.Sprintf("%d%02d%02d%d", year, int(month), day, 3600*hour+60*minute+second)
		index11 := now.Format("01/02/06")
		writeStr := fmt.Sprintf(`"%s","%s",%s,"%s","%s",%s,%s,"%s","%s",%s,%s,"%s",""`,
			index1,
			index2,
			index3,
			qadArtId,
			index5,
			index6,
			index7,
			index8,
			index9,
			strconv.Itoa(qty),
			index11,
			index12,
			)
		writeLi = append(writeLi, writeStr)
	}
	if err = acuraSession.Begin(); err != nil {
		fmt.Println("开启事务失败, error:" + err.Error())
		return
	}
	count, err := daoSerialOrder.UpdateBackFlushFlag(serialOrderIdLi)
	if err != nil {
		_ = acuraSession.Rollback()
		fmt.Println("更新工单回冲状态失败, error:" + err.Error())
		return
	}
	if int(count) != len(serialOrderIdLi) {
		_ = acuraSession.Rollback()
		fmt.Println("更新工单回冲状态数量和查询的数量不一致, 更新数量:" + strconv.Itoa(int(count)) + ", 查询数量:" + strconv.Itoa(len(serialOrderIdLi)))
		return
	}
	count, err = daoPackOrderStatus.UpdateBackFlushFlag(packOrderIdLi)
	if err != nil {
		_ = acuraSession.Rollback()
		fmt.Println("更新包装单回冲状态失败, error:" + err.Error())
		return
	}
	if int(count) != len(packOrderIdLi) {
		_ = acuraSession.Rollback()
		fmt.Println("更新包装单回冲状态数量和查询的数量不一致, 更新数量:" + strconv.Itoa(int(count)) + ", 查询数量:" + strconv.Itoa(len(packOrderIdLi)))
		return
	}
	//OpenFile读取文件，不存在时则创建，使用追加模式
	File, err := os.OpenFile(filepath, os.O_RDWR|os.O_APPEND|os.O_CREATE, 0666)
	if err != nil {
		fmt.Println("创建csv文件失败, error:" + err.Error())
		return
	}
	_, err = File.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM
	if err != nil {
		_ = acuraSession.Rollback()
		fmt.Println("写入csv文件失败, error:" + err.Error())
		return
	}
	for _, item := range writeLi {
		_, err = File.WriteString(item)
		if err != nil {
			_ = acuraSession.Rollback()
			fmt.Println("写入csv文件失败, error:" + err.Error())
			return
		}
		_, err = File.Write([]byte{'\r', '\n'})
		if err != nil {
			_ = acuraSession.Rollback()
			fmt.Println("写入csv文件失败, error:" + err.Error())
			return
		}
	}
	File.Close()
	//创建写入接口
	//WriterCsv := csv.NewWriter(File)

	//for _, writeItem := range writeLi {
	//	//写入一条数据，传入数据为切片(追加模式)
	//	err = WriterCsv.Write(writeItem)
	//	if err != nil {
	//		_ = acuraSession.Rollback()
	//		fmt.Println("写入csv文件失败, error:" + err.Error())
	//		return
	//	}
	//}
	//WriterCsv.Flush() //刷新，不刷新是无法写入的
	err = acuraSession.Commit()
	if err != nil {
		fmt.Println("事务提交失败, error:" + err.Error())
		return
	}
	fmt.Println("OK")
	return

}