tmrgo/http/handle/sap/sap.go

package sap

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

	"tmr-watch/conf/setting"
	"tmr-watch/http/handle/restful"
	"tmr-watch/models"
	"tmr-watch/pkg/app"
	"tmr-watch/pkg/e"

	"github.com/Anderson-Lu/gofasion/gofasion"
	"github.com/astaxie/beego/logs"
	"github.com/gin-gonic/gin"
	"github.com/xormplus/xorm"
)

func SyncMaterialOutbound() error {
	tx := restful.Engine.NewSession()
	defer tx.Close()

	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select column_default  as pastureid   from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = ? AND column_name = 'pastureid'`, setting.DatabaseSetting.Name).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	materialOutbound(pastureinfo.Pastureid, time.Now().AddDate(0, 0, -1).Format("2006-01-02"))
	return nil
}

func SyncSurplusOutbound() error {
	tx := restful.Engine.NewSession()
	defer tx.Close()

	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select column_default  as pastureid   from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = ? AND column_name = 'pastureid'`, setting.DatabaseSetting.Name).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	surplusOutbound(pastureinfo.Pastureid, time.Now().AddDate(0, 0, -1).Format("2006-01-02"))
	return nil
}

func SyncLeftoverEntry() error {
	tx := restful.Engine.NewSession()
	defer tx.Close()

	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select column_default  as pastureid   from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = ? AND column_name = 'pastureid'`, setting.DatabaseSetting.Name).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	leftoverEntry(pastureinfo.Pastureid, time.Now().AddDate(0, 0, -1).Format("2006-01-02"))

	return nil
}

func SyncWasteuse() error {
	tx := restful.Engine.NewSession()
	defer tx.Close()

	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select column_default  as pastureid   from    information_schema.COLUMNS
	WHERE table_name = 'recweight' AND table_schema = ? AND column_name = 'pastureid'`, setting.DatabaseSetting.Name).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}

	wasteuse(pastureinfo.Pastureid, time.Now().AddDate(0, 0, -1).Format("2006-01-02"))
	return nil
}

func SyncSapBar(c *gin.Context) {
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	start := fsion.Get("startTime").ValueStr()
	end := fsion.Get("endTime").ValueStr()
	pastureId := fsion.Get("pastureId").ValueStr()

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

	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select * from pasture where pastureid = ?`, pastureId).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return
	}

	// year := time.Now().Year()
	data := `{
		"DATA": {
			"DIDAT": "%s",
			"DITIM": "%s",
			"WERKS": "%s"
		},
		"DEST": {
			"DEST_ID": "TMRWATCH",
			"BUSS_TP": "CO005"
		}
	}`

	startTime, _ := time.ParseInLocation("2006-01-02", start, time.Local)
	endTime, _ := time.ParseInLocation("2006-01-02", end, time.Local)

	url := "http://192.168.61.117/SAPP0/Feed/CO005/QueryCowsheds"
	data = fmt.Sprintf(data, startTime.Format("20060102"), endTime.Format("20060102"), pastureinfo.Werks)
	respmap, _ := postPush(url, data, 3, tx, pastureId)
	if respmap != nil {
		if _, ok := respmap.(map[string]interface{})["ZTCO_001"]; ok {
			for _, item := range respmap.(map[string]interface{})["ZTCO_001"].([]interface{}) {
				barMap := item.(map[string]interface{})
				_, err := tx.SQL(` insert into bar(pastureid,bcode,bname,sort,class,classcode,cattle,cattlecode,sapcode)
				values(?,?,?,(select max(sort)+1  from bar b where b.pastureid = ? ),
				(select distName  from dist where distCode = ? and distType = '牛舍类型' ),?,
				(select distName  from dist where distCode = ?  and distType = '牛群类别'),?,?)
				ON DUPLICATE KEY UPDATE bname = ? ,
				class = (select distName  from dist where distCode = ?  and distType = '牛舍类型' ) ,classcode = ?,
				cattle = (select distName  from dist where distCode = ? and distType = '牛群类别' ) ,cattlecode = ?
				 `,
					pastureId, barMap["CHSNO"], fmt.Sprintf("%v_sap", barMap["CHSTX"]), pastureId, barMap["CHSTY"], barMap["CHSTY"],
					barMap["FCWTS"], barMap["FCWTS"], barMap["CHSNO"],
					fmt.Sprintf("%v_sap", barMap["CHSTX"]), barMap["CHSTY"], barMap["CHSTY"], barMap["FCWTS"], barMap["FCWTS"]).Execute()
				if err != nil {
					logs.Error(err)
					return
				}
			}
		}
	}

	appG.Response(http.StatusOK, e.SUCCESS, true)
}

type udPastureInfo struct {
	Werks     string `xorm:"werks"`
	Pastureid string `xorm:"pastureid"`
}

func MaterialOutbound(c *gin.Context) {
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	pastureId := fsion.Get("pastureid").ValueStr()
	date := fsion.Get("date").ValueStr()

	materialOutbound(pastureId, date)
	appG.Response(http.StatusOK, e.SUCCESS, true)
}

func SurplusOutbound(c *gin.Context) {
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	pastureId := fsion.Get("pastureid").ValueStr()
	date := fsion.Get("date").ValueStr()

	surplusOutbound(pastureId, date)
	appG.Response(http.StatusOK, e.SUCCESS, true)
}

type feedPlanRecord struct {
	CHSNO   string  `xorm:"'CHSNO' varchar(50)" json:"CHSNO"`       // 栏位SAP编码
	CHSTX   string  `xorm:"'CHSTX' varchar(100)" json:"CHSTX"`      // 栏位名称
	FCWTS   string  `xorm:"'FCWTS' varchar(20)" json:"FCWTS"`       // 牛群分类
	LGORT   string  `xorm:"'LGORT' varchar(50)" json:"LGORT"`       // SAP库存地点
	MAKTX   string  `xorm:"'MAKTX' varchar(100)" json:"MAKTX"`      // 饲料名称
	MATNR   string  `xorm:"'MATNR' varchar(50)" json:"MATNR"`       // SAP物料编号
	MENGE   float64 `xorm:"'MENGE' decimal(18,4)" json:"MENGE"`     // 实际用量
	ZEILE   int     `xorm:"'ZEILE' int" json:"ZEILE"`               // 行号
	Lweight float64 `xorm:"'lweight' decimal(18,4)" json:"lweight"` // 理论用量
	FeedId  int64   `xorm:"'feedId' int" json:"feedId"`
	BarId   int64   `xorm:"'fbarid' int" json:"fbarid"`
}

func materialOutbound(pastureId, date string) error {

	tx := restful.Engine.NewSession()
	defer tx.Close()
	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select * from pasture where pastureid = ?`, pastureId).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	dataList := make([]*feedPlanRecord, 0)
	err = tx.SQL(`	SELECT  tem.feedId,tem.fbarid,
	tem.sort ZEILE,
	tem.feedname MAKTX,
	sum(ifnull(ROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname="decimalPlaces" and pastureid =? )),0))lweight,
	sum(ifnull(ROUND(tem.actualweightminus,(select  inforvalue from sysopt  where inforname="decimalPlaces" and pastureid = ?)),0)) MENGE
	,tem.bname CHSTX,tem.sapcode CHSNO,tem.cattle FCWTS,tem.MATNR,tem.sapGoods LGORT

	 FROM (
	(SELECT  f.id feedId,
	d2.type,d2.fbarid,
	 d2.fname,IFNULL(ftd.fname,d1.fname ) feedname,
	SUM(
	  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.lweight*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid and ftd2.date= ftp.date  ))
	 )lweight,
	d1.sort sort,
		  SUM(
		 if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,
	  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid and ftd2.date= ftp.date )),
		
		 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid  and ftd2.date= ftp.date ))
		)
	 )actualweightminus,b.sort as d2sort,b.bname,b.sapcode, b.cattlecode ,f.sapCode MATNR,f.sapGoods ,(select cattle from feedtemplet where id = d.tempid) cattle
	 
	 FROM 
	downloadedplan d
	
	JOIN downloadplandtl2 d2
	ON d.pastureid = d2.pastureid AND d2.pid = d.id   and d2.type = 0
	JOIN downloadplandtl1 d1
	ON d.pastureid = d1.pastureid AND d1.flpid = d2.flpid AND d1.type = 0 AND d2.date = d1.date
	LEFT JOIN ftdetaildate ftd ON  ftd.pastureid =d1.pastureid AND ftd.ftid = d1.fid AND d1.feedcode = '-1' AND ftd.date = d.mydate AND ftd.version = d.tversion
	left join bar b  on b.pastureid =d1.pastureid and b.id = d2.fbarid 
	left join feed f on f.id = d1.fid
	left join feedtempletdate ftp  on ftp.id = d.tempid  AND ftp.date = ftd.date
	

	WHERE d.pastureid = ? AND  d.mydate = ? and d1.feedcode  != -1   and  b.sapCode is not null  and  f.sapCode is not null 
	GROUP BY d2.fbarid,IFNULL(ftd.fname,d1.fname )
	HAVING d2.type = 0  order by b.sort,d2.fbarid)
	
	UNION
	
		(SELECT  f.id feedId,
	d2.type,d2.fbarid,
	 d2.fname,IFNULL(ftd.fname,d1.fname ) feedname,
	SUM(
	  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.lweight*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid and ftd2.date= ftd1.date ))
	 )lweight,
	d1.sort sort,
		  SUM(
 if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)  > 0 ,
  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.date = d1.date AND ftd2.version = ftd1.version and ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid )),
	
	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE  ftd2.date = d1.date AND ftd2.version = ftd1.version and ftd1.id = ftd2.ftid  ))
	)
 )actualweightminus,b.sort as d2sort,b.bname,b.sapcode, b.cattlecode ,f.sapCode MATNR,f.sapGoods ,(select cattle from feedtemplet where id = d.tempid) cattle
	 
	 FROM 
	downloadedplan d

	JOIN downloadplandtl2 d2
	ON d.pastureid = d2.pastureid AND d2.pid = d.id   and d2.type = 0
	JOIN downloadplandtl1 d1
	ON d.pastureid = d1.pastureid AND d1.flpid = d2.flpid AND d1.type = 0 AND d2.date = d1.date
	left join bar b  on b.pastureid =d1.pastureid and b.id = d2.fbarid 
	
	left join feedtempletdate  ftd1 on   d1.feedcode = '-1'  and ftd1.date = d1.date and ftd1.id = d1.fid 
LEFT JOIN ftdetaildate ftd ON  ftd.pastureid =d1.pastureid AND ftd.ftid = ftd1.id AND d1.feedcode = '-1' AND ftd.date =d1.date AND ftd.version = ftd1.version


left join feed f on f.id = ftd.fid
	WHERE d.pastureid = ? AND  d.mydate = ? and d1.feedcode  = -1   and  b.sapCode is not null  and  f.sapCode is not null 
	GROUP BY d2.fbarid,IFNULL(ftd.fname,d1.fname )
	HAVING d2.type = 0  order by b.sort,d2.fbarid)
	
	) tem
    GROUP BY tem.fbarid,tem.feedname 
	ORDER BY tem.d2sort,tem.feedname   `, pastureId, pastureId, pastureId, date, pastureId, date).Find(&dataList)
	if err != nil {
		logs.Error(err)
		return err
	}

	wasteUseDataList := make([]*feedPlanRecord, 0)

	err = tx.SQL(`   SELECT
	p.werks WERKS,
	DATE_FORMAT( b.date, '%Y%m%d' ) BUDAT,
	ft.cattle FCWTS,
	bar.sapCode CHSNO,
	bar.bname CHSTX,
	 '11.01.01.01.000094' MATNR,
	'系统剩料' MAKTX,
	b.weight  MENGE ,"KG" MEINS,ztbar.sapCode ZCHSNO,ztbar.bname ZCHSTX
	FROM
	wasteuse b
	JOIN pasture p ON b.pastureid = p.pastureid
	JOIN feedpdate fp ON fp.date = b.date 
	AND fp.barid = b.barid
	JOIN bar ON bar.id = b.barid
	JOIN feedtemplet ft ON ft.id = fp.ftid
	left join bar ztbar on ztbar.id  = b.barid
	where  DATE_FORMAT( b.date, '%Y-%m-%d' ) = ?  and b.pastureid = ?  `, date, pastureId).Find(&dataList)
	if err != nil {
		logs.Error(err)
		return err
	}

	wasteUseList := make([]*models.WasteUse, 0)

	err = tx.Table("wasteuse").Select("id,ftid,ftname,times,date,(select feedId from surplus  where id = wasteuse.surplusId ) surplusId,surplus,pastureId,weight,barid,barname").Where("date = ? ", date).Where("pastureId = ? ", pastureId).Find(&wasteUseList)
	if err != nil {
		logs.Error(err)
		return err
	}
	for _, wasteUse := range wasteUseList {
		for _, data := range dataList {
			if data.FeedId == wasteUse.SurplusId && data.BarId == wasteUse.Barid {
				newWeight := data.MENGE - wasteUse.Weight

				if newWeight < 0 {
					wasteUse.Weight = wasteUse.Weight - data.MENGE
					data.MENGE = 0
				} else {
					data.MENGE = newWeight
					wasteUse.Weight = 0
				}
			}
			if wasteUse.Weight <= 0 {
				break
			}
		}
	}

	for _, w := range wasteUseDataList {
		dataList = append(dataList, w)
	}

	data := `{
		"DEST": {
		  "DEST_ID": "TMRWATCH",
		  "BUSS_TP": "MM016"
		},
		"DATA": {
		  "BUDAT": "%s",
		  "WERKS": "%s",
		  "ITEMS": %s
		}
	  }`
	databyte, _ := json.Marshal(dataList)

	now, _ := time.ParseInLocation("2006-01-02", date, time.Local)

	data = fmt.Sprintf(data, now.Format("20060102"), pastureinfo.Werks, string(databyte))
	url := "http://192.168.61.117/SAPP0/Feed/MM016/StockOut"
	postPush(url, data, 0, tx, pastureId)
	return nil
}

func postPush(url, data string, msgtype int, tx *xorm.Session, pastureId string) (interface{}, bool) {
	var jsonStr = []byte(data)
	fmt.Println(string(jsonStr))
	req, err := http.NewRequest("POST", url, bytes.NewBuffer(jsonStr))
	if err != nil {
		logs.Error(err)
		// return nil
	}

	// req.SetBasicAuth("zinf_TMR", "zinf_TMR@1234") //测试线密码
	req.SetBasicAuth("zinf_TMR", "$C2Cj$\\Uv#")
	req.Header.Set("Content-Type", "application/json")
	client := &http.Client{}
	resp, err := client.Do(req)
	if err != nil {
		logs.Error(err)
		// return nil
	}
	fmt.Println(resp.Body)
	defer resp.Body.Close()
	body, _ := ioutil.ReadAll(resp.Body)
	var respData map[string]interface{}
	tx.Exec(` insert into saplog(pastureId,request,response,status,msgtext,createTime,msgtype,dataDate,url)
	values(?,?,?,?,?,now(),?,now(),?)`, pastureId, data, string(body), "", "", msgtype, url)
	fmt.Println(string(body))
	err = json.Unmarshal(body, &respData)
	if err != nil {
		logs.Error(err)
		// return nil
	}

	if _, ok := respData["DATA"]; ok {
		if msgtype == 3 {
			if _, ok := respData["ZTCO_001"]; !ok {
				return respData["DATA"], false
			}
			return respData["DATA"], true
		}
		if _, ok := respData["DATA"]; !ok {
			return respData["DATA"], false
		}
		return respData["DATA"], true
	} else {
		return respData["DATA"], false
	}
}

func SyncSapFeed(c *gin.Context) {
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	start := fsion.Get("startTime").ValueStr()
	end := fsion.Get("endTime").ValueStr()
	mtart := fsion.Get("mtart").ValueStr()
	pastureId := fsion.Get("pastureId").ValueStr()

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

	data := `{
		"DEST": {
			"DEST_ID": "TMRWATCH",
			"BUSS_TP": "MM002"
		},
		"DATA": {
			"BUDAT_B": "%s",
			"BUDAT_E": "%s",
			"TMTART": {
				"MTART": "%s"
				
			},
			"TMATKL": []
		}
	}`

	startTime, _ := time.ParseInLocation("2006-01-02", start, time.Local)
	endTime, _ := time.ParseInLocation("2006-01-02", end, time.Local)
	data = fmt.Sprintf(data, startTime.Format("20060102"), endTime.Format("20060102"), mtart)
	// "https://app.modernfarming.cn:7443/sap/Common/MM002/QueryMaterial/"
	// http://192.168.61.117/SAPP0/Common/MM002/QueryMaterial
	url := "http://192.168.61.117/SAPP0/Common/MM002/QueryMaterial"
	respmap, success := postPush(url, data, 2, tx, pastureId)

	// var a interface{} = nil
	fmt.Println(respmap, success)
	if success == false {
		appG.Response(http.StatusOK, e.SUCCESS, true)
	} else {
		for _, item := range respmap.(map[string]interface{})["TMARA"].([]interface{}) {
			sapMap := item.(map[string]interface{})
			// fmt.Println(item)
			_, err := tx.SQL(` replace into  feed_sap(MATNR,MTART,MATKL,MAKTX,MEINS,UMREZ,MEINH,UMREN,ZMINC,ZGUIG,LVORM,LAEDA)values(?,?,?,?,?,?,?,?,?,?,?,?)`,
				sapMap["MATNR"], sapMap["MTART"], sapMap["MATKL"], sapMap["MAKTX"], sapMap["MEINS"], sapMap["UMREZ"], sapMap["MEINH"],
				sapMap["UMREN"], sapMap["ZMINC"], sapMap["ZGUIG"], sapMap["LVORM"], sapMap["LAEDA"]).Execute()
			if err != nil {
				logs.Error(err)
				return
			}
		}
		appG.Response(http.StatusOK, e.SUCCESS, true)
	}

	// return
}

func surplusOutbound(pastureId, date string) error {

	tx := restful.Engine.NewSession()
	defer tx.Close()
	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select * from pasture where pastureid = ?`, pastureId).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	now, _ := time.ParseInLocation("2006-01-02", date, time.Local)

	dataList, err := tx.SQL(` SELECT 
	tem.sort ZEILE,
	tem.feedname MAKTX,
	sum(ifnull(ROUND(if(tem.lweight<0,0,tem.lweight),(select  inforvalue from sysopt  where inforname="decimalPlaces" and pastureid =? )),0))lweight,
	sum(ifnull(ROUND(tem.actualweightminus,(select  inforvalue from sysopt  where inforname="decimalPlaces" and pastureid = ?)),0)) MENGE
	,tem.bname CHSTX,tem.sapcode CHSNO,tem.cattle FCWTS,(select  inforvalue from sysopt  where inforname="excess" and pastureid = ?) MATNR,tem.sapGoods LGORT,
	? WERKS , ? BUDAT

	 FROM (
	(SELECT 
	d2.type,d2.fbarid,
	 d2.fname,IFNULL(ftd.fname,d1.fname ) feedname,
	SUM(
	  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.lweight*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid and ftd2.date= ftp.date  ))
	 )lweight,
	d1.sort sort,
		  SUM(
		 if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date) > 0 ,
	  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid and ftd2.date= ftp.date )),
		
		 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid and ftd2.date= ftp.date ))
		)
	 )actualweightminus,b.sort as d2sort,b.bname,b.sapcode, b.cattlecode ,f.sapGoods ,(select cattle from feedtemplet where id = d.tempid) cattle
	 
	 FROM 
	downloadedplan d
	
	JOIN downloadplandtl2 d2
	ON d.pastureid = d2.pastureid AND d2.pid = d.id   and d2.type = 0
	JOIN downloadplandtl1 d1
	ON d.pastureid = d1.pastureid AND d1.flpid = d2.flpid AND d1.type = 0 AND d2.date = d1.date
	LEFT JOIN ftdetaildate ftd ON  ftd.pastureid =d1.pastureid AND ftd.ftid = d1.fid AND d1.feedcode = '-1' AND ftd.date = d.mydate AND ftd.version = d.tversion
	left join bar b  on b.pastureid =d1.pastureid and b.id = d2.fbarid 
	left join feed f on f.id = d1.fid
	left join feedtempletdate ftp  on ftp.id = d.tempid  AND ftp.date = ftd.date
	

	WHERE d.pastureid = ? AND  d.mydate = ? and d1.feedcode  != -1   and  b.sapCode is not null   and  d1.fname like concat('%','_剩料','%')
	GROUP BY d2.fbarid,IFNULL(ftd.fname,d1.fname )
	HAVING d2.type = 0  order by b.sort,d2.fbarid)
	
	UNION
	
		(SELECT 
	d2.type,d2.fbarid,
	 d2.fname,IFNULL(ftd.fname,d1.fname ) feedname,
	SUM(
	  d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
	  d1.lweight*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid and ftd2.date= ftd1.date ))
	 )lweight,
	d1.sort sort,
		  SUM(
 if ( (SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)  > 0 ,
  d2.actualweightminus/(SELECT SUM(actualweightminus) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetail ftd2 WHERE ftd2.pastureid =d1.pastureid AND ftd2.ftid = d1.fid )),
	
	 d2.lweight/(SELECT SUM(lweight) FROM downloadplandtl2  d2t WHERE d2t.pastureid = d2.pastureid AND d2t.flpid = d2.flpid AND d2t.type = 0 AND d2t.date = d2.date)*
  d1.actualweightminus*IF(ftd.id IS NULL,1,ftd.fweight/(SELECT SUM(ftd2.fweight) FROM ftdetaildate ftd2 WHERE  ftd2.date = d1.date AND ftd2.version = ftd1.version and ftd1.id = ftd2.ftid  ))
	)
 )actualweightminus,b.sort as d2sort,b.bname,b.sapcode, b.cattlecode ,f.sapGoods ,(select cattle from feedtemplet where id = d.tempid) cattle
	 
	 FROM 
	downloadedplan d

	JOIN downloadplandtl2 d2
	ON d.pastureid = d2.pastureid AND d2.pid = d.id   and d2.type = 0
	JOIN downloadplandtl1 d1
	ON d.pastureid = d1.pastureid AND d1.flpid = d2.flpid AND d1.type = 0 AND d2.date = d1.date
	left join bar b  on b.pastureid =d1.pastureid and b.id = d2.fbarid 
	
	left join feedtempletdate  ftd1 on   d1.feedcode = '-1'  and ftd1.date = d1.date and ftd1.id = d1.fid 
LEFT JOIN ftdetaildate ftd ON  ftd.pastureid =d1.pastureid AND ftd.ftid = ftd1.id AND d1.feedcode = '-1' AND ftd.date =d1.date AND ftd.version = ftd1.version


left join feed f on f.id = ftd.fid
	WHERE d.pastureid = ? AND  d.mydate = ? and d1.feedcode  = -1   and  b.sapCode is not null    and d1.fname like concat('%','_剩料','%')
	GROUP BY d2.fbarid,IFNULL(ftd.fname,d1.fname )
	HAVING d2.type = 0  order by b.sort,d2.fbarid)
	
	) tem
	 	GROUP BY tem.fbarid,tem.feedname 
	ORDER BY tem.d2sort,tem.feedname  `, pastureId, pastureId, pastureId, pastureinfo.Werks, now.Format("20060102"), pastureId, date, pastureId, date).Query().List()
	if err != nil {
		logs.Error(err)
		return err
	}

	data := `{
		"DEST": {
		  "DEST_ID": "TMRWATCH",
		  "BUSS_TP": "MM016"
		},
		"DATA":  %s
		
	  }`
	databyte, _ := json.Marshal(dataList)

	data = fmt.Sprintf(data, now.Format("20060102"), pastureinfo.Werks, string(databyte))
	url := "https://app.modernfarming.cn:7443/sap/QAS/Feed/MM032/LeftoverEntry"
	postPush(url, data, 0, tx, pastureId)
	return nil
}

//剩料使用
func Wasteuse(c *gin.Context) {
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	pastureId := fsion.Get("pastureId").ValueStr()
	date := fsion.Get("date").ValueStr()

	wasteuse(pastureId, date)
	appG.Response(http.StatusOK, e.SUCCESS, true)
}

func LeftoverEntry(c *gin.Context) {
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	pastureId := fsion.Get("pastureId").ValueStr()
	date := fsion.Get("date").ValueStr()

	leftoverEntry(pastureId, date)
	appG.Response(http.StatusOK, e.SUCCESS, true)
}

//剩料使用
func wasteuse(pastureId, date string) error {
	tx := restful.Engine.NewSession()
	defer tx.Close()
	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select * from pasture where pastureid = ?`, pastureId).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	now, _ := time.ParseInLocation("2006-01-02", date, time.Local)

	// 	dataList, err := tx.SQL(`select  ftd.cattle FCWTS,
	// b.sapCode CHSNO ,b.bname CHSTX, '11.01.01.01.000094' MATNR,'系统剩料' MAKTX,w.weight MENGE,'' MEINS
	// from  wasteuse w join feedtemplet ftd on ftd.id = w.ftid   join bar b on b.id = w.barid
	// where w.date = ? and  b.pastureid = ? `, date, pastureId).Query().List()
	dataList, err := tx.SQL(`   SELECT
p.werks WERKS,
DATE_FORMAT( b.date, '%Y%m%d' ) BUDAT,
ft.cattle FCWTS,
bar.sapCode CHSNO,
bar.bname CHSTX,
 '11.01.01.01.000094' MATNR,
'系统剩料' MAKTX,
b.weight  MENGE ,"KG" MEINS,ztbar.sapCode ZCHSNO,ztbar.bname ZCHSTX
FROM
wasteuse b
JOIN pasture p ON b.pastureid = p.pastureid
JOIN feedpdate fp ON fp.date = b.date 
AND fp.barid = b.barid
JOIN bar ON bar.id = b.barid
JOIN feedtemplet ft ON ft.id = fp.ftid
left join bar ztbar on ztbar.id  = b.barid
where  DATE_FORMAT( b.date, '%Y-%m-%d' ) = ?  and b.pastureid = ?  `, date, pastureId).Query().List()
	if err != nil {
		logs.Error(err)
		return err
	}

	data := `{
		"DEST": {
		  "DEST_ID": "TMRWATCH",
		  "BUSS_TP": "MM016"
		},
		"DATA": {
		  "BUDAT": "%s",
		  "WERKS": "%s",
		  "ITEMS": %s
		}
	  }`

	for i, data := range dataList {
		data["ZEILE"] = i
	}
	databyte, _ := json.Marshal(dataList)

	// now, _ := time.ParseInLocation("2006-01-02", date, time.Local)

	data = fmt.Sprintf(data, now.Format("20060102"), pastureinfo.Werks, string(databyte))
	url := "http://192.168.61.117/SAPP0/Feed/MM016/StockOut"
	postPush(url, data, 0, tx, pastureId)

	return nil
}

func leftoverEntry(pastureId, date string) error {
	tx := restful.Engine.NewSession()
	defer tx.Close()
	pastureinfo := new(udPastureInfo)
	err := tx.SQL(`select * from pasture where pastureid = ?`, pastureId).GetFirst(pastureinfo).Error
	if err != nil {
		logs.Error(err)
		return err
	}
	// now, _ := time.ParseInLocation("2006-01-02", date, time.Local)

	dataList, err := tx.SQL(`   SELECT
	p.werks WERKS,
	DATE_FORMAT( b.date, '%Y%m%d' ) BUDAT,
	ft.cattle FCWTS,
	bar.sapCode CHSNO,
	bar.bname CHSTX,
	 '11.01.01.01.000094' MATNR,
	'系统剩料' MAKTX,
  b.weight  MENGE ,"KG" MEINS,ztbar.sapCode ZCHSNO,ztbar.bname ZCHSTX
FROM
	wasteuse b
	JOIN pasture p ON b.pastureid = p.pastureid
	JOIN feedpdate fp ON fp.date = b.date 
	AND fp.barid = b.barid
	JOIN bar ON bar.id = b.barid
	JOIN feedtemplet ft ON ft.id = fp.ftid
	left join bar ztbar on ztbar.id  = b.barid
	where  DATE_FORMAT( b.date, '%Y-%m-%d' ) = ?  and b.pastureid = ?  `, date, pastureId).Query().List()
	if err != nil {
		logs.Error(err)
		return err
	}

	for i, data := range dataList {
		data["ZEILE"] = i
	}

	data := `{"DEST": {"DEST_ID": "TMRWATCH","BUSS_TP": "MM032"},"DATA": %s} `
	databyte, _ := json.Marshal(dataList)

	data = fmt.Sprintf(data, string(databyte))
	url := "http://192.168.61.117/SAPP0/Feed/MM032/LeftoverEntry"
	postPush(url, data, 0, tx, pastureId)
	return nil
}