package main import ( "encoding/json" "encoding/xml" "fmt" "os" "strconv" "strings" "time" "./models" "./pkg/setting" "github.com/astaxie/beego/logs" "github.com/pkg/errors" //"unsafe" ) var ( strMethod string // 输入的方法名称 strOutFilePath string //保存路径 strDate string // 操作日期 FarmCode string // 牧场编号 ) type DataXML struct { XMLName xml.Name `xml:"DataXML"` Head XMLHead `xml:"Head"` Record []XMLRecord `xml:"Record"` } type DataXMLMix struct { XMLName xml.Name `xml:"DataXML"` Head XMLHead `xml:"Head"` Record []XMLRecordMix `xml:"Record"` } type DataXMLResi struct { XMLName xml.Name `xml:"DataXML"` Head XMLHead `xml:"Head"` Record []XMLRecordResi `xml:"Record"` } type XMLHead struct { SystemCode string `xml:"SystemCode"` MappingID string `xml:"MappingID"` Type string `xml:"Type"` FarmCode string `xml:"FarmCode"` DataNum string `xml:"DataNum"` Version string `xml:"Version"` } type XMLRecord struct { FeedCode string FeedName string FarmCode string FeedDate string Banci string BarName string GroupTypeCode string GroupTypeName string CowAmount string PCows string RecipeCode string RecipeName string MixBatch string BarStartTime string BarEndTime string BarRecipePlanWeight string BarRecipeRealWeight string BarFeedPlanWeight string BarFeedRealWeight string FDate string DStatus string UserName string UserTrueName string } type XMLRecordMix struct { FarmCode string MixDate string RecipeCode string RecipeName string FeedCode string FeedName string GroupTypeCode string GroupTypeName string Banci string MixBatch string MixStartTime string MixEndTime string MixRound string OutFeedTime string FeedWeightTotal string FeedOrder string FeedDryMatter string FeedAddTime string RecipeWeight string SystemWeight string FDate string DStatus string UserName string UserTrueName string } type XMLRecordResi struct { FarmCode string FeedDate string ResiDate string BarName string GroupTypeCode string GroupTypeName string ResiWeight string ResiGroupTypeCode string ResiGroupTypeName string FDate string DStatus string UserName string UserTrueName string } type Senddatarecipe struct { XMLName xml.Name `xml:"DataXML"` Head XMLHead `xml:"Head"` Record []Xnr_senddatarecipe `xml:"Record"` } type Senddatafeed struct { XMLName xml.Name `xml:"DataXML"` Head XMLHead `xml:"Head"` Record []Xnr_senddatafeed `xml:"Record"` } type Feedgdailycost struct { XMLName xml.Name `xml:"DataXML"` Head XMLHead `xml:"Head"` Record []Xnr_feedgdailycost `xml:"Record"` } type Xnr_senddatarecipe struct { getDate string getTime string FarmCode string RecipeCode string RecipeName string RecipeVersion string GroupTypeCode string GroupTypeName string RecipeType string FeedCode string FeedName string DryMatter string JiaobanOrder string JiaobanTime string BDWeight string BDTime string FeedWeight string FeedWeightRate string StartDate string LastAdjDate string FDate string DStatus string UserName string UserTrueName string } type Xnr_senddatafeed struct { getDate string getTime string FarmCode string FeedCode string FeedName string Goods_Spec string Goods_Unit string Class2_Name string FDate string DStatus string UserName string UserTrueName string } type Xnr_feedgdailycost struct { GDailyCostListID string getDate string getTime string FarmCode string FeedName string FeedTMR_Date string DeptGroupType_Code string DeptGroupType_Name string Group_Name string RealCowAmount string PCows string FeedCowAmount string FeedRecipe_Code string FeedRecipe_Verson string FeedRecipe_Name string FeedWeight string AverageDMIasRecipe string FeedBanciPlan string Banci1Rate string Banci2Rate string Banci3Rate string Banci4Rate string StatusID string StatusText string OrderNum string Remark string Creator string CreateDate string } func main() { if len(os.Args) < 2 { logs.Error("参数输入错误：例 ： " + `PrconsoleV2.4.exe -a getData -p d:\202101011getData.txt -d 2021-01-01`) return } paraMap := make(map[string]string, 0) for i := 1; i < len(os.Args); i++ { if len(os.Args) > i+1 { paraMap[strings.ToLower(os.Args[i])] = strings.Trim(strings.ToLower(os.Args[i+1]), " ") } else { paraMap[strings.ToLower(os.Args[i])] = "" } } strMethod = paraMap["-a"] strDate = paraMap["-d"] err := checkDate() if err != nil { logs.Error("日期格式错误 例 ： 2021-01-01") return } strOutFilePath = strings.ToLower(paraMap["-p"]) FarmCode = setting.DatabaseSetting.FarmCode setting.Setup("") models.Setup() FarmCode = setting.DatabaseSetting.FarmCode //logs.Error(strMethod,strOutFilePath,strDate,FarmCode) SelectApi() } func SelectApi() { var err error switch strMethod { case "getstate": err = getstate() case "getdatamix": err = checkpathCreat() if err != nil { return } err = getDataMix() case "getdata": err = checkpathCreat() if err != nil { return } err = getData() case "getdataresi": err = checkpathCreat() if err != nil { return } err = getDataResi() case "senddatarecipe": err = checkpathExist() if err != nil { return } err = sendDataRecipe() case "senddatafeed": err = checkpathExist() if err != nil { return } err = sendDataFeed() case "feedgdailycost": err = checkpathExist() if err != nil { return } err = feedGDailyCost() default: fmt.Println("1") //logs.Info("1") } if err == nil { fmt.Println("1") //logs.Info("1") } else { logs.Error("fail", err) } } func getstate() error { tx := models.Engine.NewSession() err := tx.Begin() defer func() { switch { case err != nil: //logs.Error("__error:", err) if tx != nil { tx.Rollback() } default: if tx != nil { err = tx.Commit() } } if tx != nil { tx.Close() } }() // 修改栏舍配方 sqlstr := ` ALTER TABLE feed ADD COLUMN xnrFeedName VARCHAR(50) DEFAULT NULL COMMENT '伊启牛饲料名称' ` _, err = tx.Exec(sqlstr) if err != nil { // logs.Error("err", err, ) } sqlstr = ` ALTER TABLE feedp ADD COLUMN xnrFeedpName VARCHAR(50) DEFAULT NULL COMMENT '伊启牛栏舍名称' ` _, err = tx.Exec(sqlstr) if err != nil { // logs.Error("err", err, ) } sqlstr = ` ALTER TABLE feedtemplet ADD COLUMN feedtempletCode VARCHAR(50) DEFAULT NULL COMMENT '伊启牛配方模板编码' ` _, err = tx.Exec(sqlstr) if err != nil { // logs.Error("err", err, ) } sqlstr = ` ALTER TABLE feedtemplet ADD COLUMN xnrFeedtempletName VARCHAR(50) DEFAULT NULL COMMENT '伊启牛配方模板名称' ` _, err = tx.Exec(sqlstr) if err != nil { //logs.Error("err", err, ) } return nil } func getDataMix() error { sqlstr := `SELECT ? AS FarmCode, DATE_FORMAT(MixDate,'%Y-%m-%d') MixDate,RecipeCode,RecipeName,GroupTypeCode,GroupTypeName,Banci,MixBatch, DATE_FORMAT(MixStartTime,'%Y-%m-%d %H:%i:%s')MixStartTime, DATE_FORMAT(MixEndTime,'%Y-%m-%d %H:%i:%s') MixEndTime,MixRound, DATE_FORMAT(OutFeedTime,'%Y-%m-%d %H:%i:%s') OutFeedTime, SUM(FeedWeightTotal) AS FeedWeightTotal,FeedOrder,FeedCode,FeedName, FeedDryMatter, DATE_FORMAT(FeedAddTime,'%Y-%m-%d %H:%i:%s') FeedAddTime ,SUM(RecipeWeight) AS RecipeWeight,SUM(SystemWeight) AS SystemWeight, DATE_FORMAT(FDate,'%Y-%m-%d %H:%i:%s') FDate,DStatus,UserName,UserTrueName FROM( SELECT d.Mydate AS MixDate, IFNULL((SELECT feedtempletCode FROM feedtemplet WHERE id = d2.feedtempletId),'') AS RecipeCode, d2.feedtempletName AS RecipeName, (SELECT GroupTypeCode FROM xnr_senddatarecipe WHERE RecipeName=d2.feedtempletName ORDER BY id DESC LIMIT 1) AS GroupTypeCode, (SELECT GroupTypeName FROM xnr_senddatarecipe WHERE RecipeName=d2.feedtempletName ORDER BY id DESC LIMIT 1) AS GroupTypeName, CASE WHEN (d.Times = 1) OR (d.Times = 2) THEN 1 WHEN (d.Times = 3) OR (d.Times = 4) THEN 2 WHEN (d.Times = 5) OR (d.Times = 6) THEN 3 END AS Banci, CEIL(SUBSTRING_INDEX(d.ProjName,'.', -1)) AS MixBatch, (SELECT MIN(downloadplandtl1_exec.InTime) FROM downloadplandtl1_exec WHERE downloadplandtl1_exec.PID = d.id GROUP BY d.id) AS MixStartTime, (SELECT MAX(downloadplandtl1_exec.InTime) FROM downloadplandtl1_exec WHERE downloadplandtl1_exec.PID = d.id GROUP BY d.id) AS MixEndTime,'' AS MixRound, d1exe.InTime AS OutFeedTime, (SELECT SUM(downloadplandtl1.ActualWeightMinus) FROM downloadplandtl1 WHERE downloadplandtl1.PID = d.id GROUP BY d.id) AS FeedWeightTotal,d1exe.sort AS FeedOrder, feed.feedcode AS FeedCode,d1.Fname AS FeedName,feed.dry AS FeedDryMatter, IFNULL(DATE_SUB(d1exe.Intime, INTERVAL d1exe.processTime HOUR_SECOND),d1exe.Intime) AS FeedAddTime,d1.LWEIGHT AS RecipeWeight, d1.ActualWeightMinus AS SystemWeight,NOW() AS FDate,1 AS DStatus,driver.ID AS UserName,driver.drivername AS UserTrueName FROM downloadplandtl1 d1 LEFT JOIN downloadedplan d ON d.id = d1.PID LEFT JOIN downloadplandtl1_exec d1exe ON d1.PID = d1exe.PID AND d1.SORT=d1exe.SORT LEFT JOIN driver ON d.DriverID = driver.ID LEFT JOIN feed ON d1.FID = feed.ID LEFT JOIN (SELECT downloadplandtl2.* FROM downloadplandtl2 JOIN downloadedplan ON downloadplandtl2.pid=downloadedplan.id WHERE downloadedplan.lpplanType!=1 AND downloadedplan.Mydate= ? GROUP BY pid) d2 ON d2.pid=d.id WHERE d.Mydate = ? AND d.IsCompleted = 1 ) a WHERE FeedName != '需加料' GROUP BY RecipeName,Banci,MixBatch,feedname UNION ALL SELECT ? AS FarmCode, DATE_FORMAT(MixDate,'%Y-%m-%d') MixDate,RecipeCode,RecipeName,GroupTypeCode,GroupTypeName,Banci,MixBatch, DATE_FORMAT(MixStartTime,'%Y-%m-%d %H:%i:%s')MixStartTime, DATE_FORMAT(MixEndTime,'%Y-%m-%d %H:%i:%s') MixEndTime,MixRound, DATE_FORMAT(OutFeedTime,'%Y-%m-%d %H:%i:%s') OutFeedTime, SUM(FeedWeightTotal) AS FeedWeightTotal,FeedOrder,FeedCode,FeedName, FeedDryMatter, DATE_FORMAT(FeedAddTime,'%Y-%m-%d %H:%i:%s') FeedAddTime ,SUM(RecipeWeight) AS RecipeWeight,SUM(SystemWeight) AS SystemWeight, DATE_FORMAT(FDate,'%Y-%m-%d %H:%i:%s') FDate,DStatus,UserName,UserTrueName FROM( SELECT d.Mydate AS MixDate, IFNULL((SELECT feedtempletCode FROM feedtemplet WHERE id = d2.feedtempletId),'') AS RecipeCode, d2.feedtempletName AS RecipeName, (SELECT GroupTypeCode FROM xnr_senddatarecipe WHERE RecipeName=d2.feedtempletName ORDER BY id DESC LIMIT 1) AS GroupTypeCode, (SELECT GroupTypeName FROM xnr_senddatarecipe WHERE RecipeName=d2.feedtempletName ORDER BY id DESC LIMIT 1) AS GroupTypeName, CASE WHEN (d.Times = 1) OR (d.Times = 2) THEN 1 WHEN (d.Times = 3) OR (d.Times = 4) THEN 2 WHEN (d.Times = 5) OR (d.Times = 6) THEN 3 END AS Banci, CEIL(SUBSTRING_INDEX(d.ProjName, '.', -1)) AS MixBatch, (SELECT MIN(downloadplandtl1_exec.InTime) FROM downloadplandtl1_exec WHERE downloadplandtl1_exec.PID = d.id GROUP BY d.id) AS MixStartTime, (SELECT MAX(downloadplandtl1_exec.InTime) FROM downloadplandtl1_exec WHERE downloadplandtl1_exec.PID = d.id GROUP BY d.id) AS MixEndTime, ''AS MixRound, d1exe.InTime AS OutFeedTime, (SELECT SUM(downloadplandtl1.ActualWeightMinus) FROM downloadplandtl1 WHERE downloadplandtl1.PID = d.id GROUP BY d.id) AS FeedWeightTotal,d1exe.sort AS FeedOrder, feed.feedcode AS FeedCode,d1.Fname AS FeedName,feed.dry AS FeedDryMatter, IFNULL(DATE_SUB(d1exe.Intime, INTERVAL d1exe.processTime HOUR_SECOND),d1exe.Intime) AS FeedAddTime,d1.LWEIGHT AS RecipeWeight, d1.ActualWeightMinus AS SystemWeight,NOW() AS FDate,1 AS DStatus,driver.ID AS UserName,driver.drivername AS UserTrueName FROM downloadplandtl1 d1 LEFT JOIN downloadedplan d ON d.id = d1.PID LEFT JOIN downloadplandtl1_exec d1exe ON d1.PID = d1exe.PID AND d1.SORT=d1exe.SORT LEFT JOIN driver ON d.DriverID = driver.ID LEFT JOIN feed ON d1.FID = feed.ID LEFT JOIN (SELECT downloadplandtl2.* FROM downloadplandtl2 JOIN downloadedplan ON downloadplandtl2.pid=downloadedplan.id WHERE downloadedplan.lpplanType!=1 AND downloadedplan.Mydate= ? GROUP BY pid) d2 ON d2.flpid=d.id WHERE d.Mydate = ? AND d.IsCompleted = 1 AND d2.FLPID IS NOT NULL ) a GROUP BY RecipeName,Banci,MixBatch,feedname` valuesMap, err := models.Engine.SQL(sqlstr, FarmCode, strDate, strDate, FarmCode, strDate, strDate).QueryString() if err != nil { logs.Error("err", err, len(valuesMap)) return err } W := DataXMLMix{} W.Head = XMLHead{ SystemCode: "TMR", MappingID: FarmCode, Type: "TMRDataMix", FarmCode: FarmCode, DataNum: strconv.Itoa(len(valuesMap)), Version: "3", } var records []XMLRecordMix strtem, err := json.Marshal(valuesMap) if err != nil { logs.Error("json.Marshal err", err) } err = json.Unmarshal(strtem, &records) if err != nil { logs.Error("json.Unmarshal err", err) } W.Record = append(W.Record, records...) output1, _ := xml.MarshalIndent(W, " ", " ") f, err := os.Create(strOutFilePath) if err != nil { logs.Error("os.Open err", err) return err } output1 = append([]byte(xml.Header), output1...) _, err = f.Write(output1) if err != nil { logs.Error("f.Write err", err) return err } f.Close() return nil } func getData() error { sqlstr := ` SELECT ? AS FarmCode,id,DATE_FORMAT(FeedDate,'%Y-%m-%d') AS FeedDate,Banci,BarName,GroupTypeCode,GroupTypeName,CowAmount, PCows,RecipeCode,RecipeName,MixBatch, DATE_FORMAT(BarStartTime,'%Y-%m-%d %H:%i:%s') BarStartTime,DATE_FORMAT(BarEndTime,'%Y-%m-%d %H:%i:%s') BarEndTime, SUM(BarRecipePlanWeight) AS BarRecipePlanWeight, SUM(BarRecipeRealWeight) AS BarRecipeRealWeight, FeedCode,FeedName, SUM(ROUND(BarFeedPlanWeight / CCountRatio * BarRecipePlanWeight,2)) AS BarFeedPlanWeight, SUM(ROUND(BarFeedRealWeight / aCCountRatio * BarRecipeRealWeight,2)) AS BarFeedRealWeight, DATE_FORMAT(FDate,'%Y-%m-%d %H:%i:%s') FDate,IF(d1fname <> 0,d1fname,IF(ismodify=0,1,3)) DStatus,UserName,UserTrueName FROM ( SELECT d.id,d.Mydate AS FeedDate, IF( (SELECT COUNT(*) FROM recweight r WHERE d2.Intime = r.MTime AND r.Weight = d2.ActualWeight )>0, IF((SELECT COUNT(*) FROM recweight r WHERE r.MTime = (SELECT dee.intime FROM downloadplandtl1_exec dee WHERE dee.pid = d1.pid AND dee.sort= d1.sort) AND r.Weight = (SELECT dee.ActualWeight FROM downloadplandtl1_exec dee WHERE dee.pid = d1.pid AND dee.sort= d1.sort) ) >0,0,3),3) ismodify, IF(ABS((SELECT SUM(ActualWeightMinus) FROM downloadplandtl1_exec de WHERE de.PID = d1.PID AND de.sort = d1.sort ) - (SELECT SUM(ActualWeightMinus) FROM downloadplandtl1 de WHERE de.PID = d1.PID AND de.sort = d1.sort ))<1,0,2 ) d1fname, CASE WHEN (d.Times = 1) OR (d.Times = 2) THEN 1 WHEN (d.Times = 3) OR (d.Times = 4) THEN 2 WHEN (d.Times = 5) OR (d.Times = 6) THEN 3 END AS Banci, d2.Fname AS BarName, IFNULL((SELECT DeptGroupType_Code FROM xnr_feedgdailycost WHERE Group_Name=d2.Fname ORDER BY id DESC LIMIT 1), (SELECT GroupTypeCode FROM xnr_senddatarecipe WHERE RecipeName =d2.feedtempletName ORDER BY id DESC LIMIT 1) ) AS GroupTypeCode, IFNULL( (SELECT DeptGroupType_Name FROM xnr_feedgdailycost WHERE Group_Name=d2.Fname ORDER BY id DESC LIMIT 1), (SELECT GroupTypeName FROM xnr_senddatarecipe WHERE RecipeName =d2.feedtempletName ORDER BY id DESC LIMIT 1) ) AS GroupTypeName, d2.cowcount AS CowAmount, d2.cowcount*CCountRatio/100 AS PCows, (SELECT feedtempletCode FROM feedtemplet WHERE id = d2.feedtempletId) AS RecipeCode, d2.feedtempletName AS RecipeName, CEIL(SUBSTRING_INDEX(d.ProjName, '.', -1)) AS MixBatch, IFNULL(DATE_SUB(d2.Intime, INTERVAL d2.processTime HOUR_SECOND),d2.intime) AS BarStartTime,d2.Intime AS BarEndTime, d2.LWEIGHT AS BarRecipePlanWeight,d2.ActualWeightMinus AS BarRecipeRealWeight,feed.feedcode AS FeedCode,d1.Fname AS FeedName, d1.LWEIGHT AS BarFeedPlanWeight,d1.ActualWeightMinus AS BarFeedRealWeight,NOW() AS FDate,1 AS DStatus,driver.ID AS UserName,driver.drivername AS UserTrueName, (SELECT SUM(dd1.lweight) FROM downloadplandtl1 dd1 INNER JOIN downloadedplan dd ON dd1.PID = dd.id WHERE dd1.PID = d.ID AND dd.IsCompleted = 1) AS CCountRatio, (SELECT SUM(ActualWeightMinus) FROM downloadplandtl1 dd1 INNER JOIN downloadedplan dd ON dd1.PID = dd.id WHERE dd1.PID = d.ID AND dd.IsCompleted = 1) AS aCCountRatio FROM downloadedplan d INNER JOIN downloadplandtl1 d1 ON d.id = d1.PID INNER JOIN downloadplandtl2 d2 ON d.id = d2.PID INNER JOIN feed ON d1.FID=feed.id left JOIN driver ON d.DriverID = driver.ID WHERE d.Mydate = ? AND d.IsCompleted = 1 ) a GROUP BY BarName,FeedName,banci UNION ALL SELECT ? AS FarmCode,id,DATE_FORMAT(FeedDate,'%Y-%m-%d') FeedDate,Banci,BarName,GroupTypeCode,GroupTypeName,CowAmount, PCows,RecipeCode,RecipeName,MixBatch, DATE_FORMAT(BarStartTime,'%Y-%m-%d %H:%i:%s') BarStartTime,DATE_FORMAT(BarEndTime,'%Y-%m-%d %H:%i:%s')BarEndTime, SUM(BarRecipePlanWeight) AS BarRecipePlanWeight, SUM(BarRecipeRealWeight) AS BarRecipeRealWeight, FeedCode,FeedName, SUM(ROUND(BarFeedPlanWeight / CCountRatio * BarRecipePlanWeight,2)) AS BarFeedPlanWeight, SUM(ROUND(BarFeedRealWeight / aCCountRatio * BarRecipeRealWeight,2)) AS BarFeedRealWeight, DATE_FORMAT(FDate,'%Y-%m-%d %H:%i:%s') FDate, IF(d1fname <> 0,d1fname,IF(ismodify=0,1,3)) DStatus,UserName,UserTrueName FROM ( SELECT d.id,d.Mydate AS FeedDate, IF( (SELECT COUNT(*) FROM recweight r WHERE d2.Intime = r.MTime AND r.Weight = d2.ActualWeight )>0, IF((SELECT COUNT(*) FROM recweight r WHERE r.MTime = (SELECT dee.intime FROM downloadplandtl1_exec dee WHERE dee.pid = d1.pid AND dee.sort= d1.sort) AND r.Weight = (SELECT dee.ActualWeight FROM downloadplandtl1_exec dee WHERE dee.pid = d1.pid AND dee.sort= d1.sort) ) >0,0,3),3) ismodify, IF((SELECT SUM(ActualWeightMinus) FROM downloadplandtl1_exec de WHERE de.PID = d1.PID AND de.sort = d1.sort ) = (SELECT SUM(ActualWeightMinus) FROM downloadplandtl1 de WHERE de.PID = d1.PID AND de.sort = d1.sort ),0,2 ) d1fname, CASE WHEN (d.Times = 1) OR (d.Times = 2) THEN 1 WHEN (d.Times = 3) OR (d.Times = 4) THEN 2 WHEN (d.Times = 5) OR (d.Times = 6) THEN 3 END AS Banci, d2.Fname AS BarName, IFNULL((SELECT DeptGroupType_Code FROM xnr_feedgdailycost WHERE Group_Name=d2.Fname ORDER BY id DESC LIMIT 1), (SELECT GroupTypeCode FROM xnr_senddatarecipe WHERE RecipeName =d2.Fname ORDER BY id DESC LIMIT 1) ) AS GroupTypeCode, IFNULL( (SELECT DeptGroupType_Name FROM xnr_feedgdailycost WHERE Group_Name=d2.Fname ORDER BY id DESC LIMIT 1), (SELECT GroupTypeCode FROM xnr_senddatarecipe WHERE RecipeName =d2.Fname ORDER BY id DESC LIMIT 1) ) AS GroupTypeName, d2.cowcount AS CowAmount, d2.cowcount*CCountRatio/100 AS PCows, (SELECT feedtempletCode FROM feedtemplet WHERE id = d2.feedtempletId) AS RecipeCode, d2.feedtempletName AS RecipeName, CEIL(SUBSTRING_INDEX(d.ProjName, '.', -1)) AS MixBatch, IFNULL(DATE_SUB(d2.Intime, INTERVAL d2.processTime HOUR_SECOND),d2.intime) AS BarStartTime,d2.Intime AS BarEndTime, d2.LWEIGHT AS BarRecipePlanWeight,d2.ActualWeightMinus AS BarRecipeRealWeight,feed.feedcode AS FeedCode,d1.Fname AS FeedName, d1.LWEIGHT AS BarFeedPlanWeight,d1.ActualWeightMinus AS BarFeedRealWeight,NOW() AS FDate,1 AS DStatus,driver.ID AS UserName,driver.drivername AS UserTrueName, (SELECT SUM(dd1.lweight) FROM downloadplandtl1 dd1 INNER JOIN downloadedplan dd ON dd1.PID = dd.id WHERE dd1.PID = d.ID AND dd.IsCompleted = 1) AS CCountRatio, (SELECT SUM(ActualWeightMinus) FROM downloadplandtl1 dd1 INNER JOIN downloadedplan dd ON dd1.PID = dd.id WHERE dd1.PID = d.ID AND dd.IsCompleted = 1) AS aCCountRatio FROM downloadedplan d INNER JOIN downloadplandtl1 d1 ON d.id = d1.PID INNER JOIN downloadplandtl2 d2 ON d.id = d2.FLPID INNER JOIN feed ON d1.FID=feed.id left JOIN driver ON d.DriverID = driver.ID WHERE d.Mydate = ? AND d.IsCompleted = 1 AND d2.FLPID IS NOT NULL ) a GROUP BY BarName,FeedName,banci ` valuesMap, err := models.Engine.SQL(sqlstr, FarmCode, strDate, FarmCode, strDate).QueryString() //valuesMap, err := models.Engine.SQL(sqlstr).QueryString() if err != nil { logs.Error("err", err, len(valuesMap)) logs.Error("err", err, len(valuesMap)) return err } W := DataXML{} W.Head = XMLHead{ SystemCode: "TMR", MappingID: FarmCode, Type: "TMRData", FarmCode: FarmCode, DataNum: strconv.Itoa(len(valuesMap)), Version: "3", } var records []XMLRecord strtem, err := json.Marshal(valuesMap) if err != nil { logs.Error("json.Marshal err", err) logs.Error("json.Marshal err", err) } err = json.Unmarshal(strtem, &records) if err != nil { logs.Error("json.Unmarshal err", err) logs.Error("json.Unmarshal err", err) } W.Record = append(W.Record, records...) output1, _ := xml.MarshalIndent(W, " ", " ") f, err := os.Create(strOutFilePath) if err != nil { logs.Error("os.Open err", err) logs.Error("os.Open err", err) return err } output1 = append([]byte(xml.Header), output1...) _, err = f.Write(output1) if err != nil { logs.Error("f.Write err", err) return err } f.Close() return nil } func getDataResi() error { sqlstr := ` SELECT ? AS FarmCode, DATE_FORMAT(DATE_SUB(d.Mydate, INTERVAL 1 DAY),'%Y-%m-%d') AS FeedDate,DATE_FORMAT(d.Mydate,'%Y-%m-%d') AS ResiDate,d2.Fname AS BarName, '' AS GroupTypeCode,d2.cowclassname AS GroupTypeName,barfeedremain.Remain AS ResiWeight,'' AS ResiGroupTypeCode,bar.class AS ResiGroupTypeName, DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') AS FDate,1 AS DStatus,driver.ID AS UserName,driver.drivername AS UserTrueName FROM downloadedplan d INNER JOIN downloadplandtl2 d2 ON d.id = d2.PID JOIN barfeedremain ON d2.FBarID = barfeedremain.barid AND DATE_ADD(d.Mydate,INTERVAL -1 DAY) = barfeedremain.RemainDate LEFT JOIN feedp ON feedp.barid = barfeedremain.barid LEFT JOIN bar ON feedp.barid = bar.id left JOIN driver ON d.DriverID = driver.ID WHERE d.Mydate = ? AND d.IsCompleted = 1 ` valuesMap, err := models.Engine.SQL(sqlstr, FarmCode, strDate).QueryString() if err != nil { logs.Error("select downloadedplan err", err, len(valuesMap)) return err } W := DataXMLResi{} W.Head = XMLHead{ SystemCode: "TMR", MappingID: FarmCode, Type: "TMRDataResi", FarmCode: FarmCode, DataNum: strconv.Itoa(len(valuesMap)), Version: "3", } var records []XMLRecordResi strtem, err := json.Marshal(valuesMap) if err != nil { logs.Error("json.Marshal err", err) } err = json.Unmarshal(strtem, &records) if err != nil { logs.Error("json.Unmarshal err", err) } W.Record = append(W.Record, records...) output1, _ := xml.MarshalIndent(W, " ", " ") f, err := os.Create(strOutFilePath) if err != nil { logs.Error("os.Open err", err) return err } output1 = append([]byte(xml.Header), output1...) _, err = f.Write(output1) if err != nil { logs.Error("f.Write err", err) return err } f.Close() return nil } // 判断集合是否包含某个字符串 func contains(slice []string, str string) bool { for _, s := range slice { if s == str { return true } } return false } func sendDataRecipe() error { tx := models.Engine.NewSession() err := tx.Begin() defer func() { switch { case err != nil: logs.Error("__error:", err) if tx != nil { tx.Rollback() } default: if tx != nil { err = tx.Commit() } } if tx != nil { tx.Close() } }() _, err = tx.Exec("delete from xnr_senddatarecipe where getDate = ?", strDate) if err != nil { logs.Error("delete xnr_senddatarecipe err", err) } f, err := os.Open(strOutFilePath) if err != nil { logs.Error("os.Open err", err) } input := make([]byte, 100000) _, err = f.Read(input) if err != nil { logs.Error("f.Read err", err) } W := Senddatarecipe{} err = xml.Unmarshal(input, &W) if err != nil { //logs.Error("xml.Unmarshal err", err,W) } sqlstr := `INSERT INTO xnr_senddatarecipe ( getDate,getTime,FarmCode,RecipeCode,RecipeName,RecipeVersion,GroupTypeCode,GroupTypeName,RecipeType,FeedCode,FeedName,DryMatter,JiaobanTime,JiaobanOrder,BDWeight,BDTime,FeedWeight,FeedWeightRate,StartDate,LastAdjDate,FDate,DStatus,UserName,UserTrueName ) VALUES ( ?,NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ` // 新增新的明细 for _, v := range W.Record { _, err = tx.Exec(sqlstr, strDate, v.FarmCode, v.RecipeCode, v.RecipeName, transtr(v.RecipeVersion), v.GroupTypeCode, v.GroupTypeName, transtr(v.RecipeVersion), v.FeedCode, v.FeedName, transtr(v.DryMatter), transtr(v.JiaobanTime), transtr(v.JiaobanOrder), transtr(v.BDWeight), transtr(v.BDTime), transtr(v.FeedWeight), transtr(v.FeedWeightRate), trandate(v.StartDate), trandate(v.LastAdjDate), trandate(v.FDate), v.DStatus, v.UserName, v.UserTrueName) if err != nil { logs.Error("insert xnr_senddatarecipe err"+sqlstr, err) } } // 修改饲料 sqlstr = ` UPDATE feedtemplet SET TNAME=CONCAT(LEFT(TNAME,4),'_del'), feedtempletCode=NULL,xnrFeedtempletName=NULL WHERE isDelete=1 ` _, _ = tx.Exec(sqlstr) // 修改饲料 sqlstr = ` UPDATE feed JOIN xnr_senddatarecipe xf ON feed.feedCode = xf.FeedCode SET feed.xnrFeedName=xf.FeedName,feed.ENABLE=1 WHERE xf.getDate=? ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error("UPDATE feed1 err", err) } // 修改饲料 // sqlstr = ` // UPDATE feed // JOIN xnr_senddatarecipe xf ON feed.xnrFeedName = xf.FeedName OR feed.FNAME = xf.FeedName // SET // feed.xnrFeedName=xf.FeedName,feed.feedCode=xf.FeedCode,feed.ENABLE=1 // WHERE xf.getDate=? //` // _, err =tx.Exec(sqlstr,strDate) // if err != nil { // logs.Error(" update feed2 err", err) // } //新增饲料 sqlstr = ` INSERT INTO feed(feedcode,FNAME,xnrFeedName,FCLASS,ENABLE) SELECT xf.FeedCode,xf.FeedName,xf.FeedName, IFNULL((SELECT ID FROM feedclass WHERE FCNAME='其它' OR FCNAME='其他' LIMIT 1),0)ww,1 FROM xnr_senddatarecipe xf WHERE xf.getDate=? AND (SELECT COUNT(*) FROM feed WHERE feed.feedCode = xf.FeedCode) = 0 GROUP BY xf.FeedCode ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error(" INSERT feed err", err) } // 修改饲料干物质 sqlstr = ` UPDATE feed JOIN xnr_senddatarecipe xf ON feed.feedCode = xf.FeedCode SET feed.dry=ROUND(xf.DryMatter/100,4) WHERE xf.getDate=? AND xf.DryMatter IS NOT NULL ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error("update feed3 err", err) } // 修改配方模板 sqlstr = ` UPDATE feedtemplet ft JOIN xnr_senddatarecipe xf ON ft.feedtempletCode = xf.RecipeCode OR ft.xnrFeedtempletName = xf.RecipeName OR ft.TNAME = xf.RecipeName SET ft.TNAME=xf.RecipeName,ft.feedtempletCode=xf.RecipeCode, ft.xnrFeedtempletName=xf.RecipeName, ft.OWNER = xf.UserTrueName, ft.REMARK = '伊启牛配方', ft.CCID = IFNULL((SELECT id FROM cowclass WHERE CLASSNAME= xf.GroupTypeName LIMIT 1),3) WHERE xf.getDate=? ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error(" update feedtemplet1 err", err) } //新增配方模板 sqlstr = ` INSERT INTO feedtemplet(TNAME,feedtempletCode,xnrFeedtempletName,OWNER,CCID,REMARK) SELECT xf.RecipeName,xf.RecipeCode,xf.RecipeName,xf.UserTrueName, IFNULL((SELECT id FROM cowclass WHERE CLASSNAME= xf.GroupTypeName LIMIT 1),3),'伊启牛配方' FROM xnr_senddatarecipe xf LEFT JOIN feedtemplet ft ON ft.feedtempletCode = xf.RecipeCode OR ft.xnrFeedtempletName = xf.RecipeName OR ft.TNAME = xf.RecipeName WHERE xf.getDate=? AND ft.id IS NULL GROUP BY xf.RecipeCode ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error(" INSERT feedtemplet2 err", err) } //修改配方模板子表 sqlstr = ` DELETE ftdetail FROM feedtemplet JOIN ftdetail ON ftdetail.FTID = feedtemplet.id WHERE feedtemplet.feedtempletCode IN( SELECT RecipeCode FROM xnr_senddatarecipe WHERE getDate=? GROUP BY RecipeCode ) ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error("UPDATE ftdetail err", err) } //新增配方模板子表 // sqlstr = ` // DELETE ftd // FROM ftdetail ftd // LEFT JOIN feed ON feed.id = ftd.FID // LEFT JOIN feedtemplet ft ON ftd.FTID = ft.id // LEFT JOIN xnr_senddatarecipe xf // ON ft.feedtempletCode = xf.RecipeCode AND feed.feedCode = xf.FeedCode // AND xf.getDate = ? // WHERE xf.id IS NULL AND ft.feedtempletCode IN(SELECT RecipeCode FROM xnr_senddatarecipe xf1 WHERE xf1.getDate = ? GROUP BY RecipeCode) // ` // _, err = tx.Exec(sqlstr, strDate, strDate) // if err != nil { // logs.Error("delete ftdetail err", err) // } //新增配方模板子表 sqlstr = ` INSERT INTO ftdetail(FTID,FID,FWEIGHT,packageSumWeight,sort,autosecond,autosecondname,feedgroup,fname) SELECT ft.id,(SELECT id FROM feed WHERE feed.feedCode = xf.FeedCode LIMIT 1) feedcode,xf.FeedWeight,0,xf.JiaobanOrder,0,'禁用', IF((SELECT COUNT(1) FROM ftdetail WHERE ftid = ft.id AND sort = xf.JiaobanOrder ) > 0 , (SELECT feedgroup FROM ftdetail WHERE ftid = ft.id AND sort = xf.JiaobanOrder ) , (SELECT fname FROM feed WHERE feed.feedCode =xf.FeedCode LIMIT 1)), (SELECT fname FROM feed WHERE feed.feedCode =xf.FeedCode LIMIT 1) FROM xnr_senddatarecipe xf LEFT JOIN feedtemplet ft ON ft.feedtempletCode =xf.RecipeCode OR ft.xnrFeedtempletName = xf.RecipeName OR ft.TNAME = xf.RecipeName WHERE xf.getDate=? GROUP BY ft.id,feedcode` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error(" INSERT ftdetail err", err) } return nil } func sendDataFeed() error { tx := models.Engine.NewSession() err := tx.Begin() defer func() { switch { case err != nil: logs.Error("__error:", err) if tx != nil { tx.Rollback() } default: if tx != nil { err = tx.Commit() } } if tx != nil { tx.Close() } }() _, err = tx.Exec("delete from xnr_senddatafeed where getDate = ?", strDate) if err != nil { logs.Error("delete err", err) } f, err := os.Open(strOutFilePath) if err != nil { logs.Error("os.Open err", err) } input := make([]byte, 100000) _, err = f.Read(input) if err != nil { logs.Error("f.Read err", err) } W := Senddatafeed{} err = xml.Unmarshal(input, &W) if err != nil { //logs.Error("xml.Unmarshal err", err,W) } sqlstr := `INSERT INTO xnr_senddatafeed ( getDate,getTime,FarmCode,FeedCode,FeedName,Goods_Spec,Goods_Unit,Class2_Name,FDate,DStatus,UserName,UserTrueName ) VALUES ( ?,NOW(),?,?,?,?,?,?,?,?,?,? ) ` for _, v := range W.Record { _, err = tx.Exec(sqlstr, strDate, v.FarmCode, v.FeedCode, v.FeedName, v.Goods_Spec, v.Goods_Unit, v.Class2_Name, trandate(v.FDate), transtr(v.DStatus), v.UserName, v.UserTrueName) if err != nil { logs.Error("insert err", err) } } _, err = tx.Exec("call xnr_synfeeddata(?)", strDate) if err != nil { logs.Error("call xnr_synfeeddata err", err) } return nil } func feedGDailyCost() error { tx := models.Engine.NewSession() err := tx.Begin() defer func() { //switch { //case err != nil: // logs.Error("__error:", err) // if tx != nil { // tx.Rollback() // } //default: // if tx != nil { // err = tx.Commit() // } //} err = tx.Commit() if tx != nil { tx.Close() } }() _, err = tx.Exec("delete from xnr_feedgdailycost where getDate = ?", strDate) if err != nil { logs.Error("delete err", err) } f, err := os.Open(strOutFilePath) if err != nil { logs.Error("os.Open err", err) } input := make([]byte, 100000) _, err = f.Read(input) if err != nil { logs.Error("f.Read err", err) } W := Feedgdailycost{} err = xml.Unmarshal(input, &W) if err != nil { logs.Error("xml.Unmarshal err", err, W) } sqlstr := `INSERT INTO xnr_feedgdailycost ( getDate,getTime,FarmCode,GDailyCostListID,FeedName,FeedTMR_Date,DeptGroupType_Code,DeptGroupType_Name,Group_Name,RealCowAmount, PCows,FeedCowAmount,FeedRecipe_Code,FeedRecipe_Verson,FeedRecipe_Name,FeedWeight,AverageDMIasRecipe, FeedBanciPlan,Banci1Rate,Banci2Rate,Banci3Rate,Banci4Rate,StatusID,StatusText,OrderNum,Remark,Creator,CreateDate ) VALUES ( ?,NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ` for _, v := range W.Record { _, err = tx.Exec(sqlstr, strDate, v.FarmCode, transtr(v.GDailyCostListID), v.FeedName, trandate(v.FeedTMR_Date), v.DeptGroupType_Code, v.DeptGroupType_Name, v.Group_Name, transtr(v.RealCowAmount), transtr(v.PCows), transtr(v.FeedCowAmount), v.FeedRecipe_Code, v.FeedRecipe_Verson, v.FeedRecipe_Name, transtr(v.FeedWeight), transtr(v.AverageDMIasRecipe), transtr(v.FeedBanciPlan), transtr(v.Banci1Rate), transtr(v.Banci2Rate), transtr(v.Banci3Rate), transtr(v.Banci4Rate), transtr(v.StatusID), v.StatusText, transtr(v.OrderNum), v.Remark, v.Creator, trandate(v.CreateDate)) if err != nil { logs.Error("insert err", err) } } // 修改栏舍配方 sqlstr = ` UPDATE feedp JOIN xnr_feedgdailycost xf ON feedp.xnrFeedpName = xf.Group_Name OR feedp.BNAME = xf.Group_Name SET feedp.softCowCount=xf.RealCowAmount,feedp.xnrFeedpName= xf.Group_Name,feedp.ENABLE=1 WHERE xf.getDate=? ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error(" update feedp err", err) } //新增栏舍配方 sqlstr = ` INSERT INTO feedp (BNAME,xnrFeedpName,softCowCount,ENABLE) SELECT xf.Group_Name,xf.Group_Name,xf.RealCowAmount,1 FROM xnr_feedgdailycost xf LEFT JOIN feedp ON feedp.xnrFeedpName = xf.Group_Name OR feedp.BNAME = xf.Group_Name WHERE xf.getDate=? AND feedp.id IS NULL ` _, err = tx.Exec(sqlstr, strDate) if err != nil { logs.Error(" insert feedp err", err) } return nil } func transtr(str string) string { if str == "" { str = "0" } return str } func trandate(str string) string { if str == "" { str = time.Now().Format("2006-01-02 15:04:05") } return str } func checkpathCreat() error { if strOutFilePath == "" { strOutFilePath = setting.CurrentPath + strMethod + strDate } f, err := os.Create(strOutFilePath) if err != nil { logs.Error("输入的保存路径不存在，请核对", err.Error()) return err } f.Close() return nil } func checkpathExist() error { if strOutFilePath == "" { logs.Error("输入的路径不存在") return errors.New("输入的路径不存在") } f, err := os.Open(strOutFilePath) if err != nil { logs.Error("输入的保存路径不存在，请核对", err.Error()) return err } f.Close() return nil } func checkDate() error { if strDate == "" { strDate = time.Now().Format("2006-01-02") return nil } _, err := time.Parse("2006-01-02", strDate) if err != nil { _, err = time.Parse("2006/01/02", strDate) if err != nil { return err } } return nil }