tmrgo/routers/api/udsync.go

package api

import (
	"bytes"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"log"
	"net/http"
	"strconv"
	"time"

	"../../pkg/logging"
	"../../pkg/setting"
	"../../routers/restful"
	"github.com/astaxie/beego/logs"
	"github.com/robfig/cron"
)

func UDSync() {

	tx := restful.Engine.NewSession()
	defer tx.Close()

	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select token,pastureid  from pasture  p join ( select column_default   from    information_schema.COLUMNS 
		WHERE table_name = 'recweight' AND table_schema = 'tmrwatch2' AND column_name = 'pastureid') pas  
		where p.pastureid = pas.column_default`).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return
	}
	log.Println("Starting Cron...")
	// UDFeedtempletinfo(pastureinfo.Token, pastureinfo.Pastureid)
	// UDUploadadddata(pastureinfo.Token, pastureinfo.Pastureid)
	// UDUploaddiliverdata(pastureinfo.Token, pastureinfo.Pastureid)
	// UDUploadoverplusdata(pastureinfo.Token, pastureinfo.Pastureid)
	// // 同步栏舍
	// resFeedp := httpGet(pastureinfo.Token, "downloadpen")
	// syncFeedp(pastureinfo.Pastureid, resFeedp)

	// //  同步饲料
	// resFeed := httpGet(pastureinfo.Token, "downloadmaterial")
	// syncFeed(pastureinfo.Pastureid, resFeed)

	defer func() {
		if err := recover(); err != nil {
			fmt.Printf("CronTest pnic err%+v \n", err)
			logging.Error("panic recover err ", err)
		}
	}()
	log.Println("Starting Cron...")
	c := cron.New()
	_, err = c.AddFunc("30 21 * * *", func() {
		log.Println("UDSync", time.Now())
		UDFeedtempletinfo(pastureinfo.Token, pastureinfo.Pastureid)
		UDUploadadddata(pastureinfo.Token, pastureinfo.Pastureid)
		UDUploaddiliverdata(pastureinfo.Token, pastureinfo.Pastureid)
		UDUploadoverplusdata(pastureinfo.Token, pastureinfo.Pastureid)
		// // 同步栏舍
		// resFeedp := httpGet(pastureinfo.Token, "downloadpen")
		// syncFeedp(pastureinfo.Pastureid, resFeedp)

		//  同步饲料
		resFeed := httpGet(pastureinfo.Token, "downloadmaterial")
		syncFeed(pastureinfo.Pastureid, resFeed)

	})

	if err != nil {
		println("cron4 err", err.Error())
	}

	c.Start()

}

//同步饲料数据
func syncFeed(pastureid string, feedList []interface{}) error {

	tx := restful.Engine.NewSession()
	defer tx.Close()
	err := tx.Begin()
	if err != nil {
		tx.Rollback()
		logs.Error("syncFeed-error-1:", err)
		return err
	}
	for _, f := range feedList {
		feed := f.(map[string]interface{})

		var feedcode, fname, fclass, fclassid, dry interface{}

		if _, ok := feed["feedcode"]; ok {
			feedcode = feed["feedcode"]
		}
		if _, ok := feed["feedname"]; ok {
			fname = feed["feedname"]
		}
		if _, ok := feed["feedclass"]; ok {
			fclass = feed["feedclass"]
		}
		if _, ok := feed["drymatter"]; ok {
			dry = feed["drymatter"]
		}

		fclassExist, err := tx.SQL(" select id from feedclass where pastureid = ? and fcname = ?", pastureid, fclass).Exist()
		if err != nil {
			tx.Rollback()
			logs.Error("syncFeed-error-2:", err)
			return err
		}
		if fclassExist {
			fclassDataList, err := tx.SQL(" select id from feedclass where pastureid = ? and fcname = ?", pastureid, fclass).QueryString()
			if err != nil {
				tx.Rollback()
				logs.Error("syncFeed-error-3:", err)
				return err
			}
			for _, fclassData := range fclassDataList {
				fclassid = fclassData["id"]
			}
		} else {
			ids, err := setting.SnowIds.NextId()
			if err != nil {
				ids = time.Now().UnixNano()
				logging.Info("create SnowIds err", err)
			}
			_, err = tx.SQL("insert into feedclass(id,pastureid,fccode,fcname,bigfeedclassname,bigfeedclassid,sort)VALUES(?,?,?,?,?,?,(select ifnull(max(f.sort),0) +1 from feedclass f  where f.pastureid =? ))",
				ids, pastureid, fclass, fclass, fclass, ids, pastureid).Execute()
			if err != nil {
				tx.Rollback()
				logs.Error("syncFeed-error-4:", err)
				return err
			}

			fclassid = ids
		}

		ids, err := setting.SnowIds.NextId()
		if err != nil {
			ids = time.Now().UnixNano()
			logging.Info("create SnowIds err", err)
		}
		insertSql := `insert into feed(id,pastureid,feedcode,fname,fclassid,fclass,dry)VALUES(?,?,?,?,?,?,?) 
		ON DUPLICATE KEY UPDATE fname = ? ,dry = ? `
		_, err = tx.SQL(insertSql, ids, pastureid, feedcode, fname, fclassid, fclass, dry, fname, dry).Execute()
		if err != nil {
			tx.Rollback()
			logs.Error("syncFeed-error-5:", err)
			return err
		}
	}

	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		logs.Error("syncFeed-error-6:", err)
		return err
	}
	return nil
}

// 同步栏舍数据
func syncFeedp(pastureid string, feedpList []interface{}) error {

	tx := restful.Engine.NewSession()
	defer tx.Close()
	err := tx.Begin()
	if err != nil {
		logs.Error("syncFeedp-error-1:", err)
		return err
	}

	for _, f := range feedpList {
		feedp := f.(map[string]interface{})
		var barName, barCode, feedtempletCode, barId interface{}
		var ftId, tname interface{}
		var cowCount interface{}
		if _, ok := feedp["barname"]; ok {
			barName = feedp["barname"]
		}
		if _, ok := feedp["barcode"]; ok {
			barCode = feedp["barcode"]
		}
		if _, ok := feedp["cowcount"]; ok {
			cowCount = feedp["cowcount"]
		}
		if _, ok := feedp["feedtempletcode"]; ok {
			feedtempletCode = feedp["feedtempletCode"]
		}

		barCount, err := tx.SQL(" select count(1) from bar where pastureid = ? and bcode = ? ", pastureid, barCode).Count()
		if err != nil {
			tx.Rollback()
			logs.Error("syncFeedp-error-2:", err)
			return err
		}
		if barCount > 0 {
			barDataList, err := tx.SQL(" select id from bar where pastureid = ? and bcode = ?", pastureid, barCode).QueryString()
			if err != nil {
				tx.Rollback()
				logs.Error("syncFeedp-error-3:", err)
				return err
			}
			for _, barData := range barDataList {
				barId = barData["id"]
			}
		} else {
			barReq, err := tx.SQL("insert into bar(pastureid,bname,bcode)VALUES(?,?,?)", pastureid, barName, barCode).Execute()
			if err != nil {
				tx.Rollback()
				logs.Error("syncFeedp-error-4:", err)
				return err
			}
			id, err := barReq.LastInsertId()
			if err != nil {
				tx.Rollback()
				logs.Error("syncFeedp-error-5:", err)
				return err
			}

			barId = strconv.FormatInt(id, 10)
		}

		if feedtempletCode != "" {
			feedtempletDataList, err := tx.SQL(" select id,tname from feedtemplet where pastureid = ? and tcode = ?", pastureid, feedtempletCode).QueryString()
			if err != nil {
				tx.Rollback()
				logs.Error("syncFeedp-error-6:", err)
				return err
			}
			for _, feedtemplet := range feedtempletDataList {
				ftId = feedtemplet
				tname = feedtemplet
			}
		}

		insertSql := `insert into feedp(pastureid,barname,barid,ccount,ftid,ftname)VALUES(?,?,?,?,?,?) 
		ON DUPLICATE KEY UPDATE ccount = ? ,ftid = ? ,ftname = ? `
		_, err = tx.SQL(insertSql, pastureid, barName, barId, cowCount, ftId, tname, cowCount, ftId, tname).Execute()
		if err != nil {
			tx.Rollback()
			logs.Error("syncFeedp-error-7:", err)
			return err
		}
	}

	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		logs.Error("syncFeedp-error-8:", err)
		return err
	}

	return nil
}

func syncFeedtemplet(pastureid string, feedtempletList []map[string]interface{}) error {

	tx := restful.Engine.NewSession()
	defer tx.Close()
	err := tx.Begin()
	if err != nil {
		logs.Error("syncFeedtemplet-error-1:", err)
		return err
	}
	// feedtempletName		配方名称	String
	// feedtempletCode		配方编码	String
	// cowClass		牲畜类别	String
	// creater		创建人	String

	//少一个配方类型
	for _, feedtemplet := range feedtempletList {
		var feedtempletName, feedtempletCode, ccid, cowClass, fttype, fttypeid string

		if _, ok := feedtemplet["feedtempletName"]; ok {
			feedtempletName = feedtemplet["feedtempletName"].(string)
		}
		if _, ok := feedtemplet["feedtempletCode"]; ok {
			feedtempletCode = feedtemplet["feedtempletCode"].(string)
		}
		if _, ok := feedtemplet["cowClass"]; ok {
			cowClass = feedtemplet["cowClass"].(string)
		}

		err = tx.SQL("select id  from cowclass where classname = ? and  pastureid = ? ", cowClass, pastureid).GetFirst(&ccid).Error
		if err != nil {
			tx.Rollback()
			logs.Error("syncFeedtemplet-error-2:", err)
			return err
		}

		insertSQl := `insert into feedtemplet(pastureid,tcode,tname,ccid,ccname,fttype,fttypeid,enable,sort)
		values(?,?,?,?,?,?,?,?,(select max(f.sort)+1 from  feedtemplet f where f.pastureid = ? )) 
		ON DUPLICATE KEY UPDATE tname = ?,ccid = ?,ccname = ?,fttype = ?,fttypeid = ?`
		a, err := tx.SQL(insertSQl, pastureid, feedtempletCode, feedtempletName, ccid, cowClass, fttype, fttypeid, 1, pastureid, feedtempletCode, feedtempletName, ccid, cowClass, fttype, fttypeid).Execute()
		if err != nil {
			tx.Rollback()
			logs.Error("syncFeedtemplet-error-3:", err)
			return err
		}
		ftid, err := a.LastInsertId()
		if err != nil {
			tx.Rollback()
			logs.Error("syncFeedtemplet-error-4:", err)
			return err
		}

		if _, ok := feedtemplet["feeds"]; ok {
			for _, feed := range feedtemplet["feeds"].([]map[string]interface{}) {

				var feedCode, feedid, sort, weight string

				if _, ok := feed["feedCode"]; ok {
					feedCode = feed["feedCode"].(string)
				}
				if _, ok := feed["sort"]; ok {
					sort = feed["sort"].(string)
				}
				if _, ok := feed["weight"]; ok {
					weight = feed["weight"].(string)
				}

				err = tx.SQL("select id  from feed where feedcode = ? and  pastureid = ? ", feedCode, pastureid).GetFirst(&feedid).Error
				if err != nil {
					tx.Rollback()
					logs.Error("syncFeedtemplet-error-5:", err)
					return err
				}

				ftdetailInsertSQl := `insert into ftdetail(pastureid,ftid,fid,fname,fweight,sort)
				values(?,?,?,?,?,?) 
				ON DUPLICATE KEY UPDATE fweight = ?,sort = ?`

				_, err := tx.SQL(ftdetailInsertSQl, pastureid, ftid, feedtempletName, weight, sort, weight, sort).Execute()
				if err != nil {
					tx.Rollback()
					logs.Error("syncFeedtemplet-error-3:", err)
					return err
				}

				ftdetailDateInsertSQl := `insert into ftdetaildate(pastureid,ftid,fid,fname,fweight,sort,date)
				values(?,?,?,?,?,?,now()) 
				ON DUPLICATE KEY UPDATE fweight = ?,sort = ?`

				_, err = tx.SQL(ftdetailDateInsertSQl, pastureid, ftid, feedtempletName, weight, sort, weight, sort).Execute()
				if err != nil {
					tx.Rollback()
					logs.Error("syncFeedtemplet-error-3:", err)
					return err
				}

			}

		}
	}

	err = tx.Commit()
	if err != nil {
		tx.Rollback()
		logs.Error("syncBar-error-5:", err)
		return err
	}

	return nil
}

type udPastureInfo struct {
	Token     string `xorm:"token"`
	Pastureid string `xorm:"pastureid"`
}

func httpGet(farmId, method string) []interface{} {
	url := fmt.Sprintf("https://wdc.unidairy.cn/copartner_downloads/?farmId=%s&method=%s", farmId, method)
	res, err := http.Get(url)
	if err != nil {
		return nil
	}
	robots, err := ioutil.ReadAll(res.Body)
	res.Body.Close()
	if err != nil {
		return nil
	}
	var data map[string][]interface{}
	json.Unmarshal(robots, &data)
	return data["msg"]

}

func UDFeedtempletinfo(token, pastureid string) {

	tx := restful.Engine.NewSession()
	defer tx.Close()

	sqlstr := `SELECT
	ft.ID AS recipeId,
	ft.TNAME AS recipeName,
	f.feedcode AS ingId,
	f.FNAME AS ingName,
	round( f.dry * ftd.FWEIGHT, 2 ) AS dmQty,
	ftd.FWEIGHT AS afQty,
	ftd.SORT AS mixNo,
	ifnull(round( f.Uprice * ftd.FWEIGHT, 2 ),"") AS recipeCost,
	ifnull(fc.FCNAME,"") AS ingType,
	f.AllowRatio AS allowableError 
FROM
	feedtemplet ft
	LEFT JOIN ftdetail ftd ON ft.id = ftd.FTID
	LEFT JOIN feed f ON ftd.FID = f.id and  ft.pastureid = f.pastureid
	LEFT JOIN feedclass fc ON f.FCLASS = fc.id  where ft.pastureid = ? `

	data, err := tx.SQL(sqlstr, pastureid).Query().List()
	if err != nil {
		logs.Error("syncBar-error-5:", err)
		return
	}

	req := make(map[string]interface{})
	req["farmId"] = token
	req["rowCount"] = len(data)
	req["method"] = "getfeedtempletinfo"
	req["resultData"] = data

	res := new(UDUploadoverplusdataReq)
	res.ApiId = "getKPTData"
	// res.ReqMethod = "PARTY"
	// res.RegCode = "cpt180511"
	// res.Command = "202cb962ac590"
	res.Param = req

	UDPostPush(res, "application/json")

}

func UDUploadadddata(token, pastureid string) {

	tx := restful.Engine.NewSession()
	defer tx.Close()

	sqlstr := `select  date_format(d.mydate,'%Y-%m-%d') loadDate,d.SORT tmrNo,d.Times loadShift,
	IFNULL(ft.id,((SELECT feedtempletid FROM downloadplandtl2 d2 WHERE d2.pid=d.id LIMIT 1))) recipeId, 
	IFNULL(d.templetName ,((SELECT feedtempletName FROM downloadplandtl2 d2 WHERE d2.pid=d.id LIMIT 1))) recipeName, 
	ifnull(f.feedcode,"") as ingId,de.Fname as ingName,ifnull(fc.FCNAME,"")  ingType,
	ifnull(f.dry,"") dmPct,de.SORT mixNo,de.feedallowratio allowableError, de.LWEIGHT expWeight,
	de.ActualWeightMinus actualWeight,ifnull(date_format(timestamp(de.InTime,CONCAT('-',de.processTime)),'%Y-%m-%d %H:%i:%s'),"") startTime,
	date_format(de.InTime,'%Y-%m-%d %H:%i:%s') endTime,ifnull((SELECT dr.driver FROM dutyrecord dr 
WHERE dr.pastureid = d.pastureid AND dr.eqid = d.tmrid AND dr.times= d.times  AND dr.operatetime <=d.mydate 
ORDER BY dr.operatetime DESC LIMIT 1),"") tmrName ,d.tmrtname equipmentId 
	from downloadedplan d LEFT JOIN downloadplandtl1_exec de on d.id=de.PID 
	left join downloadplandtl1 de1 on de1.pid = d.id
	LEFT JOIN feed f on de1.Fid=f.id and f.pastureid = de1.pastureid LEFT JOIN feedtemplet ft on d.templetName=ft.TNAME 
	LEFT JOIN feedclass fc on f.FCLASS=fc.id 
	where d.mydate=date_format(NOW(),'%Y-%m-%d') and d.IsCompleted=1 and d.lpplanType=0  and d.pastureid = ?	
union all
select  date_format(d.mydate,'%Y-%m-%d') loadDate,d.SORT tmrNo,d.Times loadShift,
	IFNULL(ft.id,((SELECT feedtempletid FROM downloadplandtl2 d2 WHERE d2.flpid=d.id LIMIT 1))) recipeId, 
	IFNULL(d.templetName ,((SELECT feedtempletName FROM downloadplandtl2 d2 WHERE d2.flpid=d.id LIMIT 1))) recipeName, 
	ifnull(f.feedcode,"") as ingId,de.Fname as ingName,ifnull(fc.FCNAME,"")  ingType,
	ifnull(f.dry,"") dmPct,de.SORT mixNo,de.feedallowratio allowableError, de.LWEIGHT expWeight,
	de.ActualWeightMinus actualWeight,ifnull(date_format(timestamp(de.InTime,CONCAT('-',de.processTime)),'%Y-%m-%d %H:%i:%s'),"") startTime,
	date_format(de.InTime,'%Y-%m-%d %H:%i:%s') endTime,ifnull((SELECT dr.driver FROM dutyrecord dr 
WHERE dr.pastureid = d.pastureid AND dr.eqid = d.tmrid AND dr.times= d.times  AND dr.operatetime <=d.mydate 
ORDER BY dr.operatetime DESC LIMIT 1),"") tmrName ,d.tmrtname equipmentId 
	from downloadedplan d LEFT JOIN downloadplandtl1_exec de on d.id=de.PID 
			 left join downloadplandtl1 de1 on de1.pid = d.id
	LEFT JOIN feed f on de1.Fid=f.id and f.pastureid = de1.pastureid LEFT JOIN feedtemplet ft on d.templetName=ft.TNAME 
	LEFT JOIN feedclass fc on f.FCLASS=fc.id 
	where d.mydate=date_format(NOW(),'%Y-%m-%d') and d.IsCompleted=1 and d.lpplanType=1 and d.pastureid = ?
ORDER BY mixNo `

	data, err := tx.SQL(sqlstr, pastureid, pastureid).Query().List()
	if err != nil {
		logs.Error("syncBar-error-5:", err)
		return
	}
	req := make(map[string]interface{})
	req["farmId"] = token
	req["rowCount"] = len(data)
	req["method"] = "uploadadddata"
	req["resultData"] = data

	res := new(UDUploadoverplusdataReq)
	res.ApiId = "getKPTData"
	res.Param = req
	UDPostPush(res, "application/json")

}

func UDUploaddiliverdata(token, pastureid string) {

	tx := restful.Engine.NewSession()
	defer tx.Close()

	sqlstr := `SELECT
	IFNULL(ft.id,d2.feedtempletid ) recipeId, 
	IFNULL(d.templetName,d2.feedtempletName ) recipeName, 
	date_format(d.mydate,'%Y-%m-%d') as dropDate,ifnull((select sort from downloadedplan where pid=d.pid and lpplanType !=d.lpplanType and mydate=d.mydate),d.sort) as tmrNo,
	d.Times as dropShift,b.bcode as penId,d2.Fname as penName,fp.CCOUNT as cowCount, d2.SORT as feedingNo, d2.LWEIGHT as expWeight,
	d2.ActualWeightMinus as actualWeight, ifnull(date_format(timestamp(d2.InTime,CONCAT('-',d2.processTime)),'%Y-%m-%d %H:%i:%s'),"") as startTime,
	date_format(d2.InTime,'%Y-%m-%d %H:%i:%s') as endTime,ifnull((SELECT dr.driver FROM dutyrecord dr 
WHERE dr.pastureid = d.pastureid AND dr.eqid = d.tmrid AND dr.times= d.times  AND dr.operatetime <=d.mydate 
ORDER BY dr.operatetime DESC LIMIT 1),"") as tmrName ,d.tmrtname equipmentId 
	from  downloadedplan d 
	LEFT JOIN downloadplandtl2 d2 on d.id=d2.PID 
	LEFT JOIN feedp fp on d2.FBarID=fp.id 
	left join bar b on fp.barid = b.id and d.pastureid = b.pastureid
	left JOIN feedtemplet ft on d.templetName=ft.TNAME 
	where d.mydate= date_format(NOW(),'%Y-%m-%d') and d.IsCompleted=1 and d.lpplanType!=1 and d.pastureid = ? `

	data, err := tx.SQL(sqlstr, pastureid).Query().List()
	if err != nil {
		logs.Error("syncBar-error-5:", err)
		return
	}
	req := make(map[string]interface{})
	req["farmId"] = token
	req["rowCount"] = len(data)
	req["method"] = "uploaddiliverdata"
	req["resultData"] = data

	res := new(UDUploadoverplusdataReq)
	res.ApiId = "getKPTData"
	res.Param = req
	UDPostPush(res, "application/json")

}

type UDUploadoverplusdataReq struct {
	ApiId     string      `json:"apiId"`
	Param     interface{} `json:"param"`
	ReqMethod string      `json:"reqMethod"`
	RegCode   string      `json:"regCode"`
	Command   string      `json:"command"`
}

func UDUploadoverplusdata(token, pastureid string) {

	tx := restful.Engine.NewSession()
	defer tx.Close()

	sqlstr := `SELECT
	'' startTime,
	'' endTime,
	ifnull(br.RemainDate,"") AS overplusDate,
	b.bcode AS penId,  
	fp.barname AS penName,
	ifnull(br.Remain,"") AS actualWeight 
FROM
	barfeedremain br
	LEFT JOIN feedp fp ON br.barid = fp.id 
	left join bar b on fp.barid = b.id
WHERE
	RemainDate =  date_format(NOW(),'%Y-%m-%d') and br.pastureid = ? `

	data, err := tx.SQL(sqlstr, pastureid).Query().List()
	if err != nil {
		logs.Error("syncBar-error-5:", err)
		return
	}
	req := make(map[string]interface{})
	req["farmId"] = token
	req["rowCount"] = len(data)
	req["method"] = "uploadoverplusdata"
	req["code"] = "1"
	req["errMessage"] = ""

	req["resultData"] = data
	res := new(UDUploadoverplusdataReq)
	res.ApiId = "getCptData"
	res.ReqMethod = "PARTY"
	res.RegCode = "cpt180511"
	res.Command = "202cb962ac590"
	res.Param = req
	UDPostPush(res, "application/json")

}

func UDPostPush(data interface{}, contentType string) string {
	url := "https://wdc.unidairy.cn/copartner_uploads/"
	// 超时时间：5秒
	client := &http.Client{Timeout: 20 * time.Second}
	jsonStr, _ := json.Marshal(data)
	resp, err := client.Post(url, contentType, bytes.NewBuffer(jsonStr))
	if err != nil {
		panic(err)
	}
	defer resp.Body.Close()

	result, _ := ioutil.ReadAll(resp.Body)
	fmt.Println(string(result))
	return string(result)
}

func getPastureInfo() ([]map[string]interface{}, error) {

	tx := restful.Engine.NewSession()
	defer tx.Close()

	data, err := tx.SQL("select pastureid,token  from pasture where token is not null ").Query().List()
	if err != nil {
		logs.Error("syncBar-error-5:", err)
		return nil, err
	}

	return data, nil
}