You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
347 lines
11 KiB
347 lines
11 KiB
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
|
|
|
|
}
|