xdmy/pkg/util/mail.go

package util

import (
	"fmt"
	"log"
	"net/smtp"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/jordan-wright/email"
	"github.com/robfig/cron"
	"kpt.xdmy/apiserver/routers/restful"
	"kpt.xdmy/pkg/logging"
	"kpt.xdmy/pkg/setting"
)

// 邮件相关（现代牧业暂未启用）
func SendMail() {
	defer func() { // 必须要先声明defer
		if err := recover(); err != nil {
			logging.Error(" SendMail pnic err", err) // 这里的err其实就是panic传入的内容，55
		}
	}()
	log.Println("********  *******  *******", time.Now())

	e := email.NewEmail()
	// 发件人
	fromName := ""
	pas := ""
	mails := make([]string, 0)
	//
	subject := ""
	note := ""
	mailreport, err := restful.MapStrList("select * from mailreport where enable = 1")
	if err != nil {
		println("err sql", err.Error())
	} else {
		for _, value := range mailreport {
			//GetMailDataByPasture("集团设备指标",value["name"],value["id"],A,f)
			//GetPastureDeptData(value["name"],value["id"],f)
			if value["type"] == "1" {
				fromName = value["addr"]
				pas = value["pas"]
				subject = value["title"]
				note = value["note"]

			} else {
				mails = append(mails, value["addr"])
			}
			println(value["addr"])
		}
	}
	// 发件人
	//e.From = "864919639@qq.com"
	e.From = fromName
	// 收件人(可以有多个)
	e.To = mails
	//e.To = []string{"18322596935@163.com","1406599862@qq.com"} //"1406599862@qq.com"
	// 邮件主题
	//e.Subject = "集团设备指标报表"
	e.Subject = subject
	// 解析html模板
	//t,err := template.ParseFiles("email-template.html")

	//body := new(bytes.Buffer)
	// Execute方法将解析好的模板应用到匿名结构体上，并将输出写入body中
	e.Text = []byte(note)
	// html形式的消息
	//body.Write([]byte("测试"))
	//// 从缓冲中将内容作为附件到邮件中
	//e.Attach(body, "email-template.html", "text/html")
	// 以路径将文件作为附件添加到邮件中
	//_,err := GetMailData()
	filename := setting.CurrentPath + "uploads/mailreport/" + time.Now().Format("2006-01-02") + "集团设备指标.xlsx"
	e.AttachFile(filename)
	// 发送邮件(如果使用QQ邮箱发送邮件的话，passwd不是邮箱密码而是授权码)
	err = e.Send("smtp.qq.com:587", smtp.PlainAuth("", fromName, pas, "smtp.qq.com"))

	if err != nil {
		println("sent err", err.Error())
	} else {
		println("发送成功")
	}

}

func CronMail() {
	log.Println("Starting Cron...")
	defer func() { // 必须要先声明defer
		if err := recover(); err != nil {
			fmt.Printf("CronTest pnic err%+v \n", err)
			logging.Error("panic recover err ", err)
			//println("pnic err",err.(error).Error()) // 这里的err其实就是panic传入的内容，55
		}
	}()
	c := cron.New()
	err := c.AddFunc(setting.ServerSetting.CronFormula, SendMail) //2 * * * * *, 2 表示每分钟的第2s执行一次
	if err != nil {
		println("cron1 err", err.Error())
		err = c.AddFunc("0 9 * * *", SendMail) //2 * * * * *, 2 表示每分钟的第2s执行一次
		if err != nil {
			println("cron2 err", err.Error())
		} else {
			println("开始启动定时任务发送邮件 0 9 * * *")
		}
	} else {
		println("开始启动定时任务发送邮件 ", setting.ServerSetting.CronFormula)
	}

	err = c.AddFunc("0 1 * * *", GetMailData) //2 * * * * *, 2 表示每分钟的第2s执行一次

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

	//_, err = c.AddFunc("30 * * * *", GetMailData) //2 * * * * *, 2 表示每分钟的第2s执行一次
	//if err != nil {
	//	println("cron4 err", err.Error())
	//}
	c.Start()
}

func GetMailData() {
	defer func() { // 必须要先声明defer
		if err := recover(); err != nil {
			logging.Error("GetMailData pnic err", err) // 这里的err其实就是panic传入的内容，55
		}
	}()
	f := excelize.NewFile()
	f.SetSheetName("Sheet1", "集团设备指标")
	//f.GetSheetMap()
	//f.NewSheet("集团设备指标")
	//f.SetSheetVisible(,false)
	//隐藏名称为 Sheet1 的工作表中的 D 至 F 列：
	//f.SetColVisible("Sheet1", "D:F", false)
	//根据给定的工作表名称（大小写敏感）、列范围和宽度值设置单个或多个列的宽度。
	//例如设置名为 Sheet1 工作表上 A 到 D 列的宽度为 20：

	// 设置单元格的值
	//style, err := f.NewStyle(`{"fill":{"type":"center"}}`)
	//if err != nil {
	//	fmt.Println(err)
	//}
	//f.SetCellStyle("集团设备指标", "B1", "M1", style)
	//
	//f.MergeCell("集团设备指标", "N1", "T1")
	//err := SetSheetRow(f,"集团设备指标", "B1", &[]interface{}{"1月", nil,nil,nil,nil,nil,nil,"2月", nil,nil,nil,nil,nil,nil,
	//	"3月", nil,nil,nil,nil,nil,nil,"4月", nil,nil,nil,nil,nil,nil,"5月", nil,nil,nil,nil,nil,nil,"6月", nil,nil,nil,nil,nil,nil,"1月", nil,nil,nil,nil,nil,nil,
	//	"1月", nil,nil,nil,nil,nil,nil,"1月", nil,nil,nil,nil,nil,nil,"1月", nil,nil,nil,nil,nil,nil,"1月", nil,nil,nil,nil,nil,nil,"1月", nil,nil,nil,nil,nil,nil})
	tem := make([]interface{}, 0)
	tem1 := make([]interface{}, 0)
	c := []interface{}{}
	d := []interface{}{}
	e := []interface{}{}
	g := []interface{}{}
	h := []interface{}{}
	o := []interface{}{}
	p := []interface{}{}
	r := []interface{}{}
	for i := 1; i <= 12; i++ {
		a := []interface{}{strconv.Itoa(i) + "月", nil, nil, nil, nil, nil, nil}
		b := []interface{}{"预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "月度达成率"}
		c = append(c, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		d = append(d, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		e = append(e, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		g = append(g, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		h = append(h, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		o = append(o, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		p = append(p, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		r = append(r, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		tem = append(tem, a...)
		tem1 = append(tem1, b...)
	}
	//d := c
	//e := c
	//g := c
	//h := c
	//o := c
	//p := c

	if err := SetSheetRow(f, "集团设备指标", "N1", &tem); err != nil {
		println(err.Error())
	}
	year := time.Now().Add(-24 * time.Hour).Format("2006")
	f.SetCellValue("集团设备指标", "A1", "时间")
	f.MergeCell("集团设备指标", "B1", "M1")

	f.MergeCell("集团设备指标", "N1", "T1")
	f.MergeCell("集团设备指标", "U1", "AA1")
	f.MergeCell("集团设备指标", "AB1", "AH1")
	f.MergeCell("集团设备指标", "AI1", "AO1")
	f.MergeCell("集团设备指标", "AP1", "AV1")
	f.MergeCell("集团设备指标", "AW1", "BC1")
	f.MergeCell("集团设备指标", "BD1", "BJ1")
	f.MergeCell("集团设备指标", "BK1", "BQ1")
	f.MergeCell("集团设备指标", "BR1", "BX1")
	f.MergeCell("集团设备指标", "BY1", "CE1")
	f.MergeCell("集团设备指标", "CF1", "CL1")
	f.MergeCell("集团设备指标", "CM1", "CS1")

	f.SetCellValue("集团设备指标", "H1", year+"年")

	SetSheetRow(f, "集团设备指标", "A2", &[]interface{}{"牧场", "成本项目", "预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "年度达成率", "迄今饲养头日", "迄今产量（公斤）", "迄今实际单头牛成本", "迄今实际公斤奶成本（元/公斤）"})
	if err := SetSheetRow(f, "集团设备指标", "N2", &tem1); err != nil {
		println(err.Error())
	}
	//

	A := 3
	pastures, err := restful.MapStrList("select id,name from pasture where isdel = 0 AND id <> 18 order by sort")
	if err != nil {
		println("err sql", err.Error())
	} else {
		for _, value := range pastures {
			GetMailDataByPasture("集团设备指标", value["name"], time.Now().Format("2006-01-02"), value["id"], A, f)
			GetPastureDeptData(value["name"], value["id"], f)
			println(value["name"])
			A = A + 8
		}
	}

	valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportJY")
	valuesMap1, err1 := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportJM")
	sqls := ""
	parammaps := make(map[string]interface{}, 0)
	parammaps["month"] = time.Now().Format("2006-01-02")
	s_params := make([]interface{}, 0)
	if err == nil && len(valuesMap) > 0 {
		sqls = valuesMap["sqlstr"]
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])
			}
		}
	}

	tem = []interface{}{}
	values, err := restful.MapStrList(sqls, s_params...)
	B := A

	temMap := make(map[string][]interface{}, 0)

	if err == nil {
		for _, value := range values {

			tem = append(tem, value["ftype"])
			tem = append(tem, value["monthBudget"])
			tem = append(tem, value["monthLimit"])
			tem = append(tem, value["qmonthBudget"])
			tem = append(tem, value["sumPrice"])
			tem = append(tem, value["asave"])
			tem = append(tem, value["dac"])
			tem = append(tem, value["dacY"])
			tem = append(tem, value["cowNum"])
			tem = append(tem, value["saleNum"])
			tem = append(tem, value["cowprice"])
			tem = append(tem, value["milkprice"])
			temMap[value["ftype"]] = tem
			tem = []interface{}{}
		}
	} else {
		println(err.Error())
	}
	if m, ok := temMap["维修费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["燃动费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"燃动费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["水费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"水费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["设备指标（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"设备指标（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电量（度）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电量（度）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["用水量（吨）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"用水量（吨）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["柴油用量（L）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"柴油用量（L）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	s_params = make([]interface{}, 0)
	if err1 == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = time.Now().Format("2006-01-02")
		paramslist := strings.Split(valuesMap1["params"], ",")
		if len(paramslist) > 0 && valuesMap1["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])

			}
		}
	}
	f.SetCellValue("集团设备指标", "A"+strconv.Itoa(A), "集团")
	f.MergeCell("集团设备指标", "A"+strconv.Itoa(A), "A"+strconv.Itoa(A+7))

	values, err = restful.MapStrList(sqls, s_params...)
	tem = []interface{}{}
	tem2 := []interface{}{}
	if err == nil {
		for _, value := range values {
			tem2 = append(tem2, value["monthBudget"])
			tem2 = append(tem2, value["monthLimit"])
			tem2 = append(tem2, value["qmonthBudget"])
			tem2 = append(tem2, value["sumPrice"])
			tem2 = append(tem2, value["asave"])
			tem2 = append(tem2, value["dac"])
			tem2 = append(tem2, value["dacY"])
			num, _ := strconv.Atoi(value["num"])
			switch value["ftype"] {
			case "维修费（万元）":
				c[(num-1)*7] = tem2[0]
				c[(num-1)*7+1] = tem2[1]
				c[(num-1)*7+2] = tem2[2]
				c[(num-1)*7+3] = tem2[3]
				c[(num-1)*7+4] = tem2[4]
				c[(num-1)*7+5] = tem2[5]
				c[(num-1)*7+6] = tem2[6]
			case "电费（万元）":
				d[(num-1)*7] = tem2[0]
				d[(num-1)*7+1] = tem2[1]
				d[(num-1)*7+2] = tem2[2]
				d[(num-1)*7+3] = tem2[3]
				d[(num-1)*7+4] = tem2[4]
				d[(num-1)*7+5] = tem2[5]
				d[(num-1)*7+6] = tem2[6]
			case "燃动费（万元）":
				e[(num-1)*7] = tem2[0]
				e[(num-1)*7+1] = tem2[1]
				e[(num-1)*7+2] = tem2[2]
				e[(num-1)*7+3] = tem2[3]
				e[(num-1)*7+4] = tem2[4]
				e[(num-1)*7+5] = tem2[5]
				e[(num-1)*7+6] = tem2[6]
			case "水费（万元）":
				g[(num-1)*7] = tem2[0]
				g[(num-1)*7+1] = tem2[1]
				g[(num-1)*7+2] = tem2[2]
				g[(num-1)*7+3] = tem2[3]
				g[(num-1)*7+4] = tem2[4]
				g[(num-1)*7+5] = tem2[5]
				g[(num-1)*7+6] = tem2[6]
			case "电量（度）":
				h[(num-1)*7] = tem2[0]
				h[(num-1)*7+1] = tem2[1]
				h[(num-1)*7+2] = tem2[2]
				h[(num-1)*7+3] = tem2[3]
				h[(num-1)*7+4] = tem2[4]
				h[(num-1)*7+5] = tem2[5]
				h[(num-1)*7+6] = tem2[6]
			case "柴油用量（L）":
				o[(num-1)*7] = tem2[0]
				o[(num-1)*7+1] = tem2[1]
				o[(num-1)*7+2] = tem2[2]
				o[(num-1)*7+3] = tem2[3]
				o[(num-1)*7+4] = tem2[4]
				o[(num-1)*7+5] = tem2[5]
				o[(num-1)*7+6] = tem2[6]
			case "用水量（吨）":
				p[(num-1)*7] = tem2[0]
				p[(num-1)*7+1] = tem2[1]
				p[(num-1)*7+2] = tem2[2]
				p[(num-1)*7+3] = tem2[3]
				p[(num-1)*7+4] = tem2[4]
				p[(num-1)*7+5] = tem2[5]
				p[(num-1)*7+6] = tem2[6]
			case "设备指标（万元）":
				r[(num-1)*7] = tem2[0]
				r[(num-1)*7+1] = tem2[1]
				r[(num-1)*7+2] = tem2[2]
				r[(num-1)*7+3] = tem2[3]
				r[(num-1)*7+4] = tem2[4]
				r[(num-1)*7+5] = tem2[5]
				r[(num-1)*7+6] = tem2[6]
			}
			tem2 = []interface{}{}
		}

	} else {
		println(err.Error())
	}

	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+7), &o); err != nil {
		println(err.Error())
	}

	//sums[2] = sums[2].(int)+value["monthLimit"].(int)
	//sums[1] = sums[1].(int)+value["monthBudget"].(int)

	// 设置月份单元格字体
	monthStyle, err := f.NewStyle("{\"Alignment\":{\"Vertical\": \"center\", \"Horizontal\":\"center\"}}")
	if err != nil {
		println("monthStyle err", err.Error())
	}

	f.SetCellStyle("集团设备指标", "A1", "CS154", monthStyle)
	// 根据指定路径保存文件

	filename := setting.CurrentPath + "uploads/mailreport/" + time.Now().Format("2006-01-02") + "集团设备指标.xlsx"

	err = PathCheck(setting.CurrentPath + "uploads/mailreport") //检查路径并创建
	os.Remove(filename)
	if err != nil {
		println("PathCheck err", err)
	}

	if err := f.SaveAs(filename); err != nil {
		println(err.Error())
	}

}

func GetMailDataByPasture(sheetName, pastureName, date string, pastureId interface{}, B int, f *excelize.File) {
	tem := make([]interface{}, 0)
	tem1 := make([]interface{}, 0)
	c := []interface{}{}
	d := []interface{}{}
	e := []interface{}{}
	g := []interface{}{}
	h := []interface{}{}
	o := []interface{}{}
	p := []interface{}{}
	r := []interface{}{}
	for i := 1; i <= 12; i++ {
		a := []interface{}{strconv.Itoa(i) + "月", nil, nil, nil, nil, nil, nil}
		b := []interface{}{"预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "月度达成率"}
		c = append(c, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		d = append(d, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		e = append(e, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		g = append(g, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		h = append(h, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		o = append(o, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		p = append(p, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		r = append(r, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		tem = append(tem, a...)
		tem1 = append(tem1, b...)
	}

	//
	valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMY")
	valuesMap1, err1 := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMM")
	sqls := ""
	parammaps := make(map[string]interface{}, 0)
	parammaps["month"] = date
	parammaps["pastureId"] = pastureId
	s_params := make([]interface{}, 0)
	if err == nil && len(valuesMap) > 0 {
		sqls = valuesMap["sqlstr"]
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])
			}
		}
	}
	f.SetCellValue(sheetName, "A"+strconv.Itoa(B), pastureName)
	f.MergeCell(sheetName, "A"+strconv.Itoa(B), "A"+strconv.Itoa(B+7))
	tem = []interface{}{}
	temMap := make(map[string][]interface{}, 0)
	//sums := []interface{}{"设备指标（万元）",0,0,0,0,0,0,0,0}
	values, err := restful.MapStrList(sqls, s_params...)
	Bt := B
	if err == nil {
		for _, value := range values {
			tem = append(tem, value["ftype"])
			tem = append(tem, value["monthBudget"])
			tem = append(tem, value["monthLimit"])
			tem = append(tem, value["qmonthBudget"])
			tem = append(tem, value["sumPrice"])
			tem = append(tem, value["asave"])
			tem = append(tem, value["dac"])
			tem = append(tem, value["dacY"])
			tem = append(tem, value["cowNum"])
			tem = append(tem, value["saleNum"])
			tem = append(tem, value["cowprice"])
			tem = append(tem, value["milkprice"])
			temMap[value["ftype"]] = tem
			//if err := SetSheetRow(f,sheetName, "B"+strconv.Itoa(Bt),&tem); err != nil {
			//	println(err.Error())
			//}
			tem = []interface{}{}
			//Bt++
		}
	} else {
		println(err.Error())
	}
	if m, ok := temMap["维修费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["电费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"电费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}
	if m, ok := temMap["燃动费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"燃动费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["水费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"水费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["设备指标（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"设备指标（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["电量（度）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"电量（度）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["用水量（吨）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"用水量（吨）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}
	if m, ok := temMap["柴油用量（L）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"柴油用量（L）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	s_params = make([]interface{}, 0)
	if err1 == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = date
		parammaps["pastureId"] = pastureId
		paramslist := strings.Split(valuesMap1["params"], ",")
		if len(paramslist) > 0 && valuesMap1["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])

			}
		}
	}

	values, err = restful.MapStrList(sqls, s_params...)
	tem = []interface{}{}
	tem2 := []interface{}{}
	if err == nil {
		for _, value := range values {
			tem2 = append(tem2, value["monthBudget"])
			tem2 = append(tem2, value["monthLimit"])
			tem2 = append(tem2, value["qmonthBudget"])
			tem2 = append(tem2, value["sumPrice"])
			tem2 = append(tem2, value["asave"])
			tem2 = append(tem2, value["dac"])
			tem2 = append(tem2, value["dacY"])
			num, _ := strconv.Atoi(value["num"])
			switch value["ftype"] {
			case "维修费（万元）":
				c[(num-1)*7] = tem2[0]
				c[(num-1)*7+1] = tem2[1]
				c[(num-1)*7+2] = tem2[2]
				c[(num-1)*7+3] = tem2[3]
				c[(num-1)*7+4] = tem2[4]
				c[(num-1)*7+5] = tem2[5]
				c[(num-1)*7+6] = tem2[6]
			case "电费（万元）":
				d[(num-1)*7] = tem2[0]
				d[(num-1)*7+1] = tem2[1]
				d[(num-1)*7+2] = tem2[2]
				d[(num-1)*7+3] = tem2[3]
				d[(num-1)*7+4] = tem2[4]
				d[(num-1)*7+5] = tem2[5]
				d[(num-1)*7+6] = tem2[6]
			case "燃动费（万元）":
				e[(num-1)*7] = tem2[0]
				e[(num-1)*7+1] = tem2[1]
				e[(num-1)*7+2] = tem2[2]
				e[(num-1)*7+3] = tem2[3]
				e[(num-1)*7+4] = tem2[4]
				e[(num-1)*7+5] = tem2[5]
				e[(num-1)*7+6] = tem2[6]
			case "水费（万元）":
				g[(num-1)*7] = tem2[0]
				g[(num-1)*7+1] = tem2[1]
				g[(num-1)*7+2] = tem2[2]
				g[(num-1)*7+3] = tem2[3]
				g[(num-1)*7+4] = tem2[4]
				g[(num-1)*7+5] = tem2[5]
				g[(num-1)*7+6] = tem2[6]
			case "电量（度）":
				h[(num-1)*7] = tem2[0]
				h[(num-1)*7+1] = tem2[1]
				h[(num-1)*7+2] = tem2[2]
				h[(num-1)*7+3] = tem2[3]
				h[(num-1)*7+4] = tem2[4]
				h[(num-1)*7+5] = tem2[5]
				h[(num-1)*7+6] = tem2[6]
			case "柴油用量（L）":
				o[(num-1)*7] = tem2[0]
				o[(num-1)*7+1] = tem2[1]
				o[(num-1)*7+2] = tem2[2]
				o[(num-1)*7+3] = tem2[3]
				o[(num-1)*7+4] = tem2[4]
				o[(num-1)*7+5] = tem2[5]
				o[(num-1)*7+6] = tem2[6]
			case "用水量（吨）":
				p[(num-1)*7] = tem2[0]
				p[(num-1)*7+1] = tem2[1]
				p[(num-1)*7+2] = tem2[2]
				p[(num-1)*7+3] = tem2[3]
				p[(num-1)*7+4] = tem2[4]
				p[(num-1)*7+5] = tem2[5]
				p[(num-1)*7+6] = tem2[6]
			case "设备指标（万元）":
				r[(num-1)*7] = tem2[0]
				r[(num-1)*7+1] = tem2[1]
				r[(num-1)*7+2] = tem2[2]
				r[(num-1)*7+3] = tem2[3]
				r[(num-1)*7+4] = tem2[4]
				r[(num-1)*7+5] = tem2[5]
				r[(num-1)*7+6] = tem2[6]
			}
			tem2 = []interface{}{}
		}

	} else {
		println(err.Error())
	}

	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B), &c); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B+1), &d); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B+2), &e); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B+3), &g); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B+4), &r); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B+5), &h); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B+6), &p); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "N"+strconv.Itoa(B+7), &o); err != nil {
		println(err.Error())
	}

}

func GetMailDataByDept(sheetName, DeptName, date string, DeptId, pastureId interface{}, B int, f *excelize.File) {
	tem := make([]interface{}, 0)
	tem1 := make([]interface{}, 0)
	c := []interface{}{}
	d := []interface{}{}
	e := []interface{}{}
	g := []interface{}{}
	h := []interface{}{}
	o := []interface{}{}
	p := []interface{}{}
	r := []interface{}{}
	for i := 1; i <= 12; i++ {
		a := []interface{}{strconv.Itoa(i) + "月", nil, nil, nil, nil, nil, nil}
		b := []interface{}{"预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "月度达成率"}
		c = append(c, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		d = append(d, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		e = append(e, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		g = append(g, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		h = append(h, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		o = append(o, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		p = append(p, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		r = append(r, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		tem = append(tem, a...)
		tem1 = append(tem1, b...)
	}

	//
	valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportBY")
	valuesMap1, err1 := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportBM")
	sqls := ""
	parammaps := make(map[string]interface{}, 0)
	parammaps["month"] = date
	parammaps["pastureId"] = pastureId
	parammaps["departmentId"] = DeptId
	s_params := make([]interface{}, 0)
	if err == nil && len(valuesMap) > 0 {
		sqls = valuesMap["sqlstr"]
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])
			}
		}
	}
	f.SetCellValue(sheetName, "A"+strconv.Itoa(B), DeptName)
	f.MergeCell(sheetName, "A"+strconv.Itoa(B), "A"+strconv.Itoa(B+7))
	tem = []interface{}{}
	temMap := make(map[string][]interface{}, 0)
	//sums := []interface{}{"设备指标（万元）",0,0,0,0,0,0,0,0}
	values, err := restful.MapStrList(sqls, s_params...)
	Bt := B
	if err == nil {
		for _, value := range values {
			tem = append(tem, value["ftype"])
			tem = append(tem, value["monthBudget"])
			tem = append(tem, value["monthLimit"])
			tem = append(tem, value["qmonthBudget"])
			tem = append(tem, value["sumPrice"])
			tem = append(tem, value["asave"])
			tem = append(tem, value["dac"])
			tem = append(tem, value["dacY"])
			tem = append(tem, value["cowNum"])
			tem = append(tem, value["saleNum"])
			tem = append(tem, value["cowprice"])
			tem = append(tem, value["milkprice"])
			temMap[value["ftype"]] = tem
			tem = []interface{}{}
		}
	} else {
		println(err.Error())
	}
	if m, ok := temMap["维修费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["电费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"电费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}
	if m, ok := temMap["燃动费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"燃动费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["水费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"水费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["设备指标（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"设备指标（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["电量（度）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"电量（度）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	if m, ok := temMap["用水量（吨）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"用水量（吨）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}
	if m, ok := temMap["柴油用量（L）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"柴油用量（L）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

	s_params = make([]interface{}, 0)
	if err1 == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = date
		parammaps["pastureId"] = pastureId
		parammaps["departmentId"] = DeptId
		paramslist := strings.Split(valuesMap1["params"], ",")
		if len(paramslist) > 0 && valuesMap1["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])

			}
		}
	}

	values, err = restful.MapStrList(sqls, s_params...)
	tem = []interface{}{}
	tem2 := []interface{}{}
	if err == nil {
		for _, value := range values {
			//fmt.Printf("va============== %+v\n",value)
			tem2 = append(tem2, value["monthBudget"])
			tem2 = append(tem2, value["monthLimit"])
			tem2 = append(tem2, value["qmonthBudget"])
			tem2 = append(tem2, value["sumPrice"])
			tem2 = append(tem2, value["asave"])
			tem2 = append(tem2, value["dac"])
			tem2 = append(tem2, value["dacY"])
			num := 1
			if _, ok := value["num"]; ok {
				num, _ = strconv.Atoi(value["num"])
			}

			switch value["ftype"] {
			case "维修费（万元）":
				c[(num-1)*7] = tem2[0]
				c[(num-1)*7+1] = tem2[1]
				c[(num-1)*7+2] = tem2[2]
				c[(num-1)*7+3] = tem2[3]
				c[(num-1)*7+4] = tem2[4]
				c[(num-1)*7+5] = tem2[5]
				c[(num-1)*7+6] = tem2[6]
			case "电费（万元）":
				d[(num-1)*7] = tem2[0]
				d[(num-1)*7+1] = tem2[1]
				d[(num-1)*7+2] = tem2[2]
				d[(num-1)*7+3] = tem2[3]
				d[(num-1)*7+4] = tem2[4]
				d[(num-1)*7+5] = tem2[5]
				d[(num-1)*7+6] = tem2[6]
			case "燃动费（万元）":
				e[(num-1)*7] = tem2[0]
				e[(num-1)*7+1] = tem2[1]
				e[(num-1)*7+2] = tem2[2]
				e[(num-1)*7+3] = tem2[3]
				e[(num-1)*7+4] = tem2[4]
				e[(num-1)*7+5] = tem2[5]
				e[(num-1)*7+6] = tem2[6]
			case "水费（万元）":
				g[(num-1)*7] = tem2[0]
				g[(num-1)*7+1] = tem2[1]
				g[(num-1)*7+2] = tem2[2]
				g[(num-1)*7+3] = tem2[3]
				g[(num-1)*7+4] = tem2[4]
				g[(num-1)*7+5] = tem2[5]
				g[(num-1)*7+6] = tem2[6]
			case "电量（度）":
				h[(num-1)*7] = tem2[0]
				h[(num-1)*7+1] = tem2[1]
				h[(num-1)*7+2] = tem2[2]
				h[(num-1)*7+3] = tem2[3]
				h[(num-1)*7+4] = tem2[4]
				h[(num-1)*7+5] = tem2[5]
				h[(num-1)*7+6] = tem2[6]
			case "柴油用量（L）":
				o[(num-1)*7] = tem2[0]
				o[(num-1)*7+1] = tem2[1]
				o[(num-1)*7+2] = tem2[2]
				o[(num-1)*7+3] = tem2[3]
				o[(num-1)*7+4] = tem2[4]
				o[(num-1)*7+5] = tem2[5]
				o[(num-1)*7+6] = tem2[6]
			case "用水量（吨）":
				p[(num-1)*7] = tem2[0]
				p[(num-1)*7+1] = tem2[1]
				p[(num-1)*7+2] = tem2[2]
				p[(num-1)*7+3] = tem2[3]
				p[(num-1)*7+4] = tem2[4]
				p[(num-1)*7+5] = tem2[5]
				p[(num-1)*7+6] = tem2[6]
			case "设备指标（万元）":
				r[(num-1)*7] = tem2[0]
				r[(num-1)*7+1] = tem2[1]
				r[(num-1)*7+2] = tem2[2]
				r[(num-1)*7+3] = tem2[3]
				r[(num-1)*7+4] = tem2[4]
				r[(num-1)*7+5] = tem2[5]
				r[(num-1)*7+6] = tem2[6]
			}
			tem2 = []interface{}{}
		}

	} else {
		println(err.Error())
	}

	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B), &c); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B+1), &d); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B+2), &e); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B+3), &g); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B+4), &r); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B+5), &h); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B+6), &p); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(B+7), &o); err != nil {
		println(err.Error())
	}

}

func GetPastureDeptData(sheetName string, pastureId interface{}, f *excelize.File) {

	f.NewSheet(sheetName)

	tem := make([]interface{}, 0)
	tem1 := make([]interface{}, 0)
	c := []interface{}{}
	d := []interface{}{}
	e := []interface{}{}
	g := []interface{}{}
	h := []interface{}{}
	o := []interface{}{}
	p := []interface{}{}
	r := []interface{}{}
	for i := 1; i <= 12; i++ {
		a := []interface{}{strconv.Itoa(i) + "月", nil, nil, nil, nil, nil, nil}
		b := []interface{}{"预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "月度达成率"}
		c = append(c, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		d = append(d, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		e = append(e, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		g = append(g, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		h = append(h, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		o = append(o, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		p = append(p, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		r = append(r, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		tem = append(tem, a...)
		tem1 = append(tem1, b...)
	}
	//d := c
	//e := c
	//g := c
	//h := c
	//o := c
	//p := c

	if err := SetSheetRow(f, sheetName, "J1", &tem); err != nil {
		println(err.Error())
	}
	year := time.Now().Add(-24 * time.Hour).Format("2006")
	f.SetCellValue(sheetName, "A1", "时间")
	f.MergeCell(sheetName, "B1", "I1")

	f.MergeCell(sheetName, "J1", "P1")
	f.MergeCell(sheetName, "Q1", "W1")
	f.MergeCell(sheetName, "X1", "AD1")
	f.MergeCell(sheetName, "AE1", "AK1")
	f.MergeCell(sheetName, "AL1", "AR1")
	f.MergeCell(sheetName, "AS1", "AY1")
	f.MergeCell(sheetName, "AZ1", "BF1")
	f.MergeCell(sheetName, "BG1", "BM1")
	f.MergeCell(sheetName, "BN1", "BT1")
	f.MergeCell(sheetName, "BU1", "CA1")
	f.MergeCell(sheetName, "CB1", "CH1")
	f.MergeCell(sheetName, "CI1", "CO1")

	f.SetCellValue(sheetName, "H1", year+"年")

	SetSheetRow(f, sheetName, "A2", &[]interface{}{"部门", "成本项目", "预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "年度达成率"})
	if err := SetSheetRow(f, sheetName, "J2", &tem1); err != nil {
		println(err.Error())
	}
	//

	A := 3
	pastures, err := restful.MapStrList("select id,name from department where pastureId=?  and isDel = 0 AND enable =1 and id <> ?", pastureId, pastureId)
	if err != nil {
		println("err sql", err.Error())
	} else {
		for _, value := range pastures {
			GetMailDataByDept(sheetName, value["name"], time.Now().Format("2006-01-02"), value["id"], pastureId, A, f)
			println(value["name"])
			A = A + 8
		}
	}

	valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMY")
	valuesMap1, err1 := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMM")
	sqls := ""
	parammaps := make(map[string]interface{}, 0)
	parammaps["month"] = time.Now().Format("2006-01-02")
	parammaps["pastureId"] = pastureId
	s_params := make([]interface{}, 0)
	if err == nil && len(valuesMap) > 0 {
		sqls = valuesMap["sqlstr"]
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])
			}
		}
	}

	tem = []interface{}{}
	//sums := []interface{}{"设备指标（万元）",0,0,0,0,0,0,0,0}
	values, err := restful.MapStrList(sqls, s_params...)
	B := A

	temMap := make(map[string][]interface{}, 0)

	if err == nil {
		for _, value := range values {
			tem = append(tem, value["ftype"])
			tem = append(tem, value["monthBudget"])
			tem = append(tem, value["monthLimit"])
			tem = append(tem, value["qmonthBudget"])
			tem = append(tem, value["sumPrice"])
			tem = append(tem, value["asave"])
			tem = append(tem, value["dac"])
			tem = append(tem, value["dacY"])
			temMap[value["ftype"]] = tem
			//if err := SetSheetRow(f,sheetName, "B"+strconv.Itoa(B),&tem); err != nil {
			//	println(err.Error())
			//}
			tem = []interface{}{}
			//B++
		}
	} else {
		println(err.Error())
	}
	if m, ok := temMap["维修费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["燃动费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"燃动费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["水费（万元）"]; ok {
		if err :=
			SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"水费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["设备指标（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"设备指标（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电量（度）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电量（度）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["用水量（吨）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"用水量（吨）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["柴油用量（L）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"柴油用量（L）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	s_params = make([]interface{}, 0)
	if err1 == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = time.Now().Format("2006-01-02")
		parammaps["pastureId"] = pastureId
		paramslist := strings.Split(valuesMap1["params"], ",")
		if len(paramslist) > 0 && valuesMap1["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])

			}
		}
	}
	f.SetCellValue(sheetName, "A"+strconv.Itoa(A), sheetName)
	f.MergeCell(sheetName, "A"+strconv.Itoa(A), "A"+strconv.Itoa(A+7))

	values, err = restful.MapStrList(sqls, s_params...)
	tem = []interface{}{}
	//d := append(c,nil)
	//e := append(c,nil)
	//g := append(c,nil)
	//h := append(c,nil)
	//o := append(c,nil)
	//p := append(c,nil)
	tem2 := []interface{}{}
	if err == nil {
		for _, value := range values {
			tem2 = append(tem2, value["monthBudget"])
			tem2 = append(tem2, value["monthLimit"])
			tem2 = append(tem2, value["qmonthBudget"])
			tem2 = append(tem2, value["sumPrice"])
			tem2 = append(tem2, value["asave"])
			tem2 = append(tem2, value["dac"])
			tem2 = append(tem2, value["dacY"])
			num, _ := strconv.Atoi(value["num"])
			switch value["ftype"] {
			case "维修费（万元）":
				c[(num-1)*7] = tem2[0]
				c[(num-1)*7+1] = tem2[1]
				c[(num-1)*7+2] = tem2[2]
				c[(num-1)*7+3] = tem2[3]
				c[(num-1)*7+4] = tem2[4]
				c[(num-1)*7+5] = tem2[5]
				c[(num-1)*7+6] = tem2[6]
			case "电费（万元）":
				d[(num-1)*7] = tem2[0]
				d[(num-1)*7+1] = tem2[1]
				d[(num-1)*7+2] = tem2[2]
				d[(num-1)*7+3] = tem2[3]
				d[(num-1)*7+4] = tem2[4]
				d[(num-1)*7+5] = tem2[5]
				d[(num-1)*7+6] = tem2[6]
			case "燃动费（万元）":
				e[(num-1)*7] = tem2[0]
				e[(num-1)*7+1] = tem2[1]
				e[(num-1)*7+2] = tem2[2]
				e[(num-1)*7+3] = tem2[3]
				e[(num-1)*7+4] = tem2[4]
				e[(num-1)*7+5] = tem2[5]
				e[(num-1)*7+6] = tem2[6]
			case "水费（万元）":
				g[(num-1)*7] = tem2[0]
				g[(num-1)*7+1] = tem2[1]
				g[(num-1)*7+2] = tem2[2]
				g[(num-1)*7+3] = tem2[3]
				g[(num-1)*7+4] = tem2[4]
				g[(num-1)*7+5] = tem2[5]
				g[(num-1)*7+6] = tem2[6]
			case "电量（度）":
				h[(num-1)*7] = tem2[0]
				h[(num-1)*7+1] = tem2[1]
				h[(num-1)*7+2] = tem2[2]
				h[(num-1)*7+3] = tem2[3]
				h[(num-1)*7+4] = tem2[4]
				h[(num-1)*7+5] = tem2[5]
				h[(num-1)*7+6] = tem2[6]
			case "柴油用量（L）":
				o[(num-1)*7] = tem2[0]
				o[(num-1)*7+1] = tem2[1]
				o[(num-1)*7+2] = tem2[2]
				o[(num-1)*7+3] = tem2[3]
				o[(num-1)*7+4] = tem2[4]
				o[(num-1)*7+5] = tem2[5]
				o[(num-1)*7+6] = tem2[6]
			case "用水量（吨）":
				p[(num-1)*7] = tem2[0]
				p[(num-1)*7+1] = tem2[1]
				p[(num-1)*7+2] = tem2[2]
				p[(num-1)*7+3] = tem2[3]
				p[(num-1)*7+4] = tem2[4]
				p[(num-1)*7+5] = tem2[5]
				p[(num-1)*7+6] = tem2[6]
			case "设备指标（万元）":
				r[(num-1)*7] = tem2[0]
				r[(num-1)*7+1] = tem2[1]
				r[(num-1)*7+2] = tem2[2]
				r[(num-1)*7+3] = tem2[3]
				r[(num-1)*7+4] = tem2[4]
				r[(num-1)*7+5] = tem2[5]
				r[(num-1)*7+6] = tem2[6]
			}
			tem2 = []interface{}{}
		}

	} else {
		println(err.Error())
	}

	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+7), &o); err != nil {
		println(err.Error())
	}

	//sums[2] = sums[2].(int)+value["monthLimit"].(int)
	//sums[1] = sums[1].(int)+value["monthBudget"].(int)

	// 设置月份单元格字体
	monthStyle, err := f.NewStyle("{\"Alignment\":{\"Vertical\": \"center\", \"Horizontal\":\"center\"}}")
	if err != nil {
		println("monthStyle err", err.Error())
	}

	f.SetCellStyle(sheetName, "A1", "CS300", monthStyle)
	// 根据指定路径保存文件

}

func PathCheck(path string) (err error) {
	b, err := PathExists(path)
	if err != nil {
		println("exist err", err)
	}
	if !b {
		err = os.Mkdir(path, 0777)
		if err != nil {
			println("Mkdir err", err)
		}
	}
	return
}

func PathExists(path string) (bool, error) {
	_, err := os.Stat(path)
	if err == nil {
		return true, nil
	}
	if os.IsNotExist(err) {
		return false, nil
	}
	return false, err
}

func DownloadDataJT(date string) (string, error) {
	filename := setting.CurrentPath + "uploads/Downloadmailreport/" + date + "集团设备指标.xlsx"
	exist, _ := FileExists(filename)
	println(filename, exist)
	if exist {
		//return filename, nil
	}
	f := excelize.NewFile()
	f.SetSheetName("Sheet1", "集团设备指标")
	tem := make([]interface{}, 0)
	tem1 := make([]interface{}, 0)
	c := []interface{}{}
	d := []interface{}{}
	e := []interface{}{}
	g := []interface{}{}
	h := []interface{}{}
	o := []interface{}{}
	p := []interface{}{}
	r := []interface{}{}
	for i := 1; i <= 12; i++ {
		a := []interface{}{strconv.Itoa(i) + "月", nil, nil, nil, nil, nil, nil}
		b := []interface{}{"预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "月度达成率"}
		c = append(c, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		d = append(d, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		e = append(e, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		g = append(g, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		h = append(h, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		o = append(o, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		p = append(p, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		r = append(r, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		tem = append(tem, a...)
		tem1 = append(tem1, b...)
	}
	if err := SetSheetRow(f, "集团设备指标", "N1", &tem); err != nil {
		println(err.Error())
	}
	//year :=time.Now().Add(-24*time.Hour).Format("2006")
	year := ""
	datetem, _ := strconv.Atoi(date[:4])
	if date > time.Now().Add(-24*time.Hour).Format("2006")+"-01-01" {
		year = strconv.Itoa(datetem)
	} else {
		year = strconv.Itoa(datetem - 1)
	}

	f.SetCellValue("集团设备指标", "A1", "时间")
	f.MergeCell("集团设备指标", "B1", "M1")

	f.MergeCell("集团设备指标", "N1", "T1")
	f.MergeCell("集团设备指标", "U1", "AA1")
	f.MergeCell("集团设备指标", "AB1", "AH1")
	f.MergeCell("集团设备指标", "AI1", "AO1")
	f.MergeCell("集团设备指标", "AP1", "AV1")
	f.MergeCell("集团设备指标", "AW1", "BC1")
	f.MergeCell("集团设备指标", "BD1", "BJ1")
	f.MergeCell("集团设备指标", "BK1", "BQ1")
	f.MergeCell("集团设备指标", "BR1", "BX1")
	f.MergeCell("集团设备指标", "BY1", "CE1")
	f.MergeCell("集团设备指标", "CF1", "CL1")
	f.MergeCell("集团设备指标", "CM1", "CS1")

	f.SetCellValue("集团设备指标", "H1", year+"年")

	SetSheetRow(f, "集团设备指标", "A2", &[]interface{}{"牧场", "成本项目", "预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "年度达成率", "迄今饲养头日", "迄今产量（公斤）", "迄今实际单头牛成本", "迄今实际公斤奶成本（元/公斤）"})
	if err := SetSheetRow(f, "集团设备指标", "N2", &tem1); err != nil {
		println(err.Error())
	}

	A := 3
	pastures, err := restful.MapStrList("select id,name from pasture where isdel = 0 AND id <> 18 order by sort")
	if err != nil {
		println("err sql", err.Error())
	} else {
		for _, value := range pastures {
			GetMailDataByPasture("集团设备指标", value["name"], date, value["id"], A, f)
			println(value["name"])
			A = A + 8
		}
	}

	valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportJY")
	valuesMap1, err1 := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportJM")
	sqls := ""
	parammaps := make(map[string]interface{}, 0)
	parammaps["month"] = date
	s_params := make([]interface{}, 0)
	if err == nil && len(valuesMap) > 0 {
		sqls = valuesMap["sqlstr"]
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])
			}
		}
	}

	tem = []interface{}{}

	values, err := restful.MapStrList(sqls, s_params...)
	B := A

	temMap := make(map[string][]interface{}, 0)

	if err == nil {
		for _, value := range values {
			tem = append(tem, value["ftype"])
			tem = append(tem, value["monthBudget"])
			tem = append(tem, value["monthLimit"])
			tem = append(tem, value["qmonthBudget"])
			tem = append(tem, value["sumPrice"])
			tem = append(tem, value["asave"])
			tem = append(tem, value["dac"])
			tem = append(tem, value["dacY"])
			tem = append(tem, value["cowNum"])
			tem = append(tem, value["saleNum"])
			tem = append(tem, value["cowprice"])
			tem = append(tem, value["milkprice"])
			temMap[value["ftype"]] = tem

			tem = []interface{}{}
			//B++
		}
	} else {
		println(err.Error())
	}
	if m, ok := temMap["维修费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["燃动费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"燃动费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["水费（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"水费（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["设备指标（万元）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"设备指标（万元）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电量（度）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电量（度）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["用水量（吨）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"用水量（吨）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["柴油用量（L）"]; ok {
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"柴油用量（L）"}
		if err := SetSheetRow(f, "集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	s_params = make([]interface{}, 0)
	if err1 == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = date
		paramslist := strings.Split(valuesMap1["params"], ",")
		if len(paramslist) > 0 && valuesMap1["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])

			}
		}
	}
	f.SetCellValue("集团设备指标", "A"+strconv.Itoa(A), "集团")
	f.MergeCell("集团设备指标", "A"+strconv.Itoa(A), "A"+strconv.Itoa(A+7))

	values, err = restful.MapStrList(sqls, s_params...)
	tem = []interface{}{}
	tem2 := []interface{}{}
	if err == nil {
		for _, value := range values {
			tem2 = append(tem2, value["monthBudget"])
			tem2 = append(tem2, value["monthLimit"])
			tem2 = append(tem2, value["qmonthBudget"])
			tem2 = append(tem2, value["sumPrice"])
			tem2 = append(tem2, value["asave"])
			tem2 = append(tem2, value["dac"])
			tem2 = append(tem2, value["dacY"])
			num, _ := strconv.Atoi(value["num"])
			switch value["ftype"] {
			case "维修费（万元）":
				c[(num-1)*7] = tem2[0]
				c[(num-1)*7+1] = tem2[1]
				c[(num-1)*7+2] = tem2[2]
				c[(num-1)*7+3] = tem2[3]
				c[(num-1)*7+4] = tem2[4]
				c[(num-1)*7+5] = tem2[5]
				c[(num-1)*7+6] = tem2[6]
			case "电费（万元）":
				d[(num-1)*7] = tem2[0]
				d[(num-1)*7+1] = tem2[1]
				d[(num-1)*7+2] = tem2[2]
				d[(num-1)*7+3] = tem2[3]
				d[(num-1)*7+4] = tem2[4]
				d[(num-1)*7+5] = tem2[5]
				d[(num-1)*7+6] = tem2[6]
			case "燃动费（万元）":
				e[(num-1)*7] = tem2[0]
				e[(num-1)*7+1] = tem2[1]
				e[(num-1)*7+2] = tem2[2]
				e[(num-1)*7+3] = tem2[3]
				e[(num-1)*7+4] = tem2[4]
				e[(num-1)*7+5] = tem2[5]
				e[(num-1)*7+6] = tem2[6]
			case "水费（万元）":
				g[(num-1)*7] = tem2[0]
				g[(num-1)*7+1] = tem2[1]
				g[(num-1)*7+2] = tem2[2]
				g[(num-1)*7+3] = tem2[3]
				g[(num-1)*7+4] = tem2[4]
				g[(num-1)*7+5] = tem2[5]
				g[(num-1)*7+6] = tem2[6]
			case "电量（度）":
				h[(num-1)*7] = tem2[0]
				h[(num-1)*7+1] = tem2[1]
				h[(num-1)*7+2] = tem2[2]
				h[(num-1)*7+3] = tem2[3]
				h[(num-1)*7+4] = tem2[4]
				h[(num-1)*7+5] = tem2[5]
				h[(num-1)*7+6] = tem2[6]
			case "柴油用量（L）":
				o[(num-1)*7] = tem2[0]
				o[(num-1)*7+1] = tem2[1]
				o[(num-1)*7+2] = tem2[2]
				o[(num-1)*7+3] = tem2[3]
				o[(num-1)*7+4] = tem2[4]
				o[(num-1)*7+5] = tem2[5]
				o[(num-1)*7+6] = tem2[6]
			case "用水量（吨）":
				p[(num-1)*7] = tem2[0]
				p[(num-1)*7+1] = tem2[1]
				p[(num-1)*7+2] = tem2[2]
				p[(num-1)*7+3] = tem2[3]
				p[(num-1)*7+4] = tem2[4]
				p[(num-1)*7+5] = tem2[5]
				p[(num-1)*7+6] = tem2[6]
			case "设备指标（万元）":
				r[(num-1)*7] = tem2[0]
				r[(num-1)*7+1] = tem2[1]
				r[(num-1)*7+2] = tem2[2]
				r[(num-1)*7+3] = tem2[3]
				r[(num-1)*7+4] = tem2[4]
				r[(num-1)*7+5] = tem2[5]
				r[(num-1)*7+6] = tem2[6]
			}
			tem2 = []interface{}{}
		}

	} else {
		println(err.Error())
	}

	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, "集团设备指标", "N"+strconv.Itoa(A+7), &o); err != nil {
		println(err.Error())
	}

	// 设置月份单元格字体
	monthStyle, err := f.NewStyle("{\"Alignment\":{\"Vertical\": \"center\", \"Horizontal\":\"center\"}}")
	if err != nil {
		println("monthStyle err", err.Error())
	}

	f.SetCellStyle("集团设备指标", "A1", "CS154", monthStyle)
	// 根据指定路径保存文件

	err = PathCheck("uploads/Downloadmailreport") //检查路径并创建
	//os.Remove(filename)
	if err != nil {
		println("PathCheck err", err)
	}

	if err := f.SaveAs(filename); err != nil {
		println(err.Error())
		return "", err
	}

	return filename, err

}

func DownloadDataMC(sheetName, date string, pastureId interface{}) (string, error) {
	filename := setting.CurrentPath + "uploads/Downloadmailreport/" + date + sheetName + "设备指标.xlsx"
	exist, _ := FileExists(filename)
	if exist {
		//return filename, nil
	}
	f := excelize.NewFile()
	f.SetSheetName("Sheet1", sheetName)

	tem := make([]interface{}, 0)
	tem1 := make([]interface{}, 0)
	c := []interface{}{}
	d := []interface{}{}
	e := []interface{}{}
	g := []interface{}{}
	h := []interface{}{}
	o := []interface{}{}
	p := []interface{}{}
	r := []interface{}{}
	for i := 1; i <= 12; i++ {
		a := []interface{}{strconv.Itoa(i) + "月", nil, nil, nil, nil, nil, nil}
		b := []interface{}{"预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "月度达成率"}
		c = append(c, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		d = append(d, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		e = append(e, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		g = append(g, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		h = append(h, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		o = append(o, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		p = append(p, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		r = append(r, []interface{}{0, 0, 0, 0, 0, 0, 0}...)
		tem = append(tem, a...)
		tem1 = append(tem1, b...)
	}

	if err := SetSheetRow(f, sheetName, "J1", &tem); err != nil {
		println(err.Error())
	}
	year := ""
	datetem, _ := strconv.Atoi(date[:4])
	if date > time.Now().Add(-24*time.Hour).Format("2006")+"-01-01" {
		year = strconv.Itoa(datetem)
	} else {
		year = strconv.Itoa(datetem - 1)
	}

	f.SetCellValue(sheetName, "A1", "时间")
	f.MergeCell(sheetName, "B1", "I1")

	f.MergeCell(sheetName, "J1", "P1")
	f.MergeCell(sheetName, "Q1", "W1")
	f.MergeCell(sheetName, "X1", "AD1")
	f.MergeCell(sheetName, "AE1", "AK1")
	f.MergeCell(sheetName, "AL1", "AR1")
	f.MergeCell(sheetName, "AS1", "AY1")
	f.MergeCell(sheetName, "AZ1", "BF1")
	f.MergeCell(sheetName, "BG1", "BM1")
	f.MergeCell(sheetName, "BN1", "BT1")
	f.MergeCell(sheetName, "BU1", "CA1")
	f.MergeCell(sheetName, "CB1", "CH1")
	f.MergeCell(sheetName, "CI1", "CO1")

	f.SetCellValue(sheetName, "H1", year+"年")

	SetSheetRow(f, sheetName, "A2", &[]interface{}{"部门", "成本项目", "预算", "内控", "迄今内控", "迄今实际", "实际节约", "迄今达成率", "年度达成率"})
	if err := SetSheetRow(f, sheetName, "J2", &tem1); err != nil {
		println(err.Error())
	}

	A := 3
	pastures, err := restful.MapStrList("select id,name from department where pastureId=?  and isDel = 0 AND enable =1 and id <> ?", pastureId, pastureId)
	if err != nil {
		println("err sql", err.Error())
	} else {
		for _, value := range pastures {
			GetMailDataByDept(sheetName, value["name"], date, value["id"], pastureId, A, f)
			println(value["name"])
			A = A + 8
		}
	}

	valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMY")
	valuesMap1, err1 := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMM")
	sqls := ""
	parammaps := make(map[string]interface{}, 0)
	parammaps["month"] = date
	parammaps["pastureId"] = pastureId
	s_params := make([]interface{}, 0)
	if err == nil && len(valuesMap) > 0 {
		sqls = valuesMap["sqlstr"]
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])
			}
		}
	}

	tem = []interface{}{}
	//sums := []interface{}{"设备指标（万元）",0,0,0,0,0,0,0,0}
	values, err := restful.MapStrList(sqls, s_params...)
	B := A

	temMap := make(map[string][]interface{}, 0)

	if err == nil {
		for _, value := range values {
			tem = append(tem, value["ftype"])
			tem = append(tem, value["monthBudget"])
			tem = append(tem, value["monthLimit"])
			tem = append(tem, value["qmonthBudget"])
			tem = append(tem, value["sumPrice"])
			tem = append(tem, value["asave"])
			tem = append(tem, value["dac"])
			tem = append(tem, value["dacY"])
			temMap[value["ftype"]] = tem
			//if err := SetSheetRow(f,sheetName, "B"+strconv.Itoa(B),&tem); err != nil {
			//	println(err.Error())
			//}
			tem = []interface{}{}
			//B++
		}
	} else {
		println(err.Error())
	}
	if m, ok := temMap["维修费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["燃动费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"燃动费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["水费（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"水费（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["设备指标（万元）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"设备指标（万元）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["电量（度）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"电量（度）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	if m, ok := temMap["用水量（吨）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"用水量（吨）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}
	if m, ok := temMap["柴油用量（L）"]; ok {
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"柴油用量（L）"}
		if err := SetSheetRow(f, sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

	s_params = make([]interface{}, 0)
	if err1 == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = date
		parammaps["pastureId"] = pastureId
		paramslist := strings.Split(valuesMap1["params"], ",")
		if len(paramslist) > 0 && valuesMap1["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammaps[strings.Trim(value, " ")])

			}
		}
	}
	f.SetCellValue(sheetName, "A"+strconv.Itoa(A), sheetName)
	f.MergeCell(sheetName, "A"+strconv.Itoa(A), "A"+strconv.Itoa(A+7))

	values, err = restful.MapStrList(sqls, s_params...)
	tem = []interface{}{}
	//d := append(c,nil)
	//e := append(c,nil)
	//g := append(c,nil)
	//h := append(c,nil)
	//o := append(c,nil)
	//p := append(c,nil)
	tem2 := []interface{}{}
	if err == nil {
		for _, value := range values {
			tem2 = append(tem2, value["monthBudget"])
			tem2 = append(tem2, value["monthLimit"])
			tem2 = append(tem2, value["qmonthBudget"])
			tem2 = append(tem2, value["sumPrice"])
			tem2 = append(tem2, value["asave"])
			tem2 = append(tem2, value["dac"])
			tem2 = append(tem2, value["dacY"])
			num, _ := strconv.Atoi(value["num"])
			switch value["ftype"] {
			case "维修费（万元）":
				c[(num-1)*7] = tem2[0]
				c[(num-1)*7+1] = tem2[1]
				c[(num-1)*7+2] = tem2[2]
				c[(num-1)*7+3] = tem2[3]
				c[(num-1)*7+4] = tem2[4]
				c[(num-1)*7+5] = tem2[5]
				c[(num-1)*7+6] = tem2[6]
			case "电费（万元）":
				d[(num-1)*7] = tem2[0]
				d[(num-1)*7+1] = tem2[1]
				d[(num-1)*7+2] = tem2[2]
				d[(num-1)*7+3] = tem2[3]
				d[(num-1)*7+4] = tem2[4]
				d[(num-1)*7+5] = tem2[5]
				d[(num-1)*7+6] = tem2[6]
			case "燃动费（万元）":
				e[(num-1)*7] = tem2[0]
				e[(num-1)*7+1] = tem2[1]
				e[(num-1)*7+2] = tem2[2]
				e[(num-1)*7+3] = tem2[3]
				e[(num-1)*7+4] = tem2[4]
				e[(num-1)*7+5] = tem2[5]
				e[(num-1)*7+6] = tem2[6]
			case "水费（万元）":
				g[(num-1)*7] = tem2[0]
				g[(num-1)*7+1] = tem2[1]
				g[(num-1)*7+2] = tem2[2]
				g[(num-1)*7+3] = tem2[3]
				g[(num-1)*7+4] = tem2[4]
				g[(num-1)*7+5] = tem2[5]
				g[(num-1)*7+6] = tem2[6]
			case "电量（度）":
				h[(num-1)*7] = tem2[0]
				h[(num-1)*7+1] = tem2[1]
				h[(num-1)*7+2] = tem2[2]
				h[(num-1)*7+3] = tem2[3]
				h[(num-1)*7+4] = tem2[4]
				h[(num-1)*7+5] = tem2[5]
				h[(num-1)*7+6] = tem2[6]
			case "柴油用量（L）":
				o[(num-1)*7] = tem2[0]
				o[(num-1)*7+1] = tem2[1]
				o[(num-1)*7+2] = tem2[2]
				o[(num-1)*7+3] = tem2[3]
				o[(num-1)*7+4] = tem2[4]
				o[(num-1)*7+5] = tem2[5]
				o[(num-1)*7+6] = tem2[6]
			case "用水量（吨）":
				p[(num-1)*7] = tem2[0]
				p[(num-1)*7+1] = tem2[1]
				p[(num-1)*7+2] = tem2[2]
				p[(num-1)*7+3] = tem2[3]
				p[(num-1)*7+4] = tem2[4]
				p[(num-1)*7+5] = tem2[5]
				p[(num-1)*7+6] = tem2[6]
			case "设备指标（万元）":
				r[(num-1)*7] = tem2[0]
				r[(num-1)*7+1] = tem2[1]
				r[(num-1)*7+2] = tem2[2]
				r[(num-1)*7+3] = tem2[3]
				r[(num-1)*7+4] = tem2[4]
				r[(num-1)*7+5] = tem2[5]
				r[(num-1)*7+6] = tem2[6]
			}
			tem2 = []interface{}{}
		}

	} else {
		println(err.Error())
	}

	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := SetSheetRow(f, sheetName, "J"+strconv.Itoa(A+7), &o); err != nil {
		println(err.Error())
	}

	//sums[2] = sums[2].(int)+value["monthLimit"].(int)
	//sums[1] = sums[1].(int)+value["monthBudget"].(int)

	// 设置月份单元格字体
	monthStyle, err := f.NewStyle("{\"Alignment\":{\"Vertical\": \"center\", \"Horizontal\":\"center\"}}")
	if err != nil {
		println("monthStyle err", err.Error())
	}

	f.SetCellStyle(sheetName, "A1", "CS300", monthStyle)
	// 根据指定路径保存文件
	//filename :="uploads/Downloadmailreport/" + date +sheetName+"设备指标.xlsx"

	err = PathCheck("uploads/Downloadmailreport") //检查路径并创建
	//os.Remove(filename)
	if err != nil {
		println("PathCheck err", err)
	}

	if err := f.SaveAs(filename); err != nil {
		println(err.Error())
		return "", err
	}

	return filename, err
}

func FileExists(path string) (bool, error) {
	_, err := os.Stat(path)
	if err == nil {
		return true, nil
	}
	if os.IsNotExist(err) {
		return false, nil
	}
	return false, err
}

//测试用的
func Sssss() {
	sqls := "SELECT bbd.`buyeCode`,p.`providerName`,bd.receiveAmount,bbd.`matchCode`,bd.`amount`,bd.`sumAmount`,bd.departmentName,pa.name pastureName," +
		"bd.note,c.id,c.bigId,c.pastureId,ps.id partId,ifnull(ps.name,c.partName) partName,ifnull(ps.partCode,c.partCode) partCode," +
		"ifnull(ps.specification,c.specification) specification,ifnull(brand.brandName,c.brand) brand,ifnull(ps.unit,c.unit) unit," +
		"ifnull(TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM bd.price))),TRIM(TRAILING '.' FROM (TRIM(TRAILING '0' FROM c.price))))" +
		"price,brand.id brandId,c.inventoryType,c.planAmount,c.remark,c.enable,c.isZeroStock,e.`empname`," +
		"DATE_FORMAT(bbd.buyerDate,'%Y-%m-%d')buyerDate,DATE_FORMAT(bbd.DoneDate, '%Y-%m-%d') DoneDate " +
		"FROM buydetail bd INNER JOIN bigbuydetail bbd ON bbd.id = bd.`bigId` LEFT JOIN provider p ON p.id = bbd.`providerId`" +
		"LEFT JOIN contract c ON c.id = bd.`contractId` LEFT JOIN emp e ON e.id = bbd.`buyerPerson` " +
		"LEFT JOIN bigpartdeal  bpd ON bpd.`matchCode` = bbd.`matchCode` inner join pasture pa on pa.id  = bbd.pastureId " +
		"LEFT JOIN parts ps ON ps.partCode =bd.partCode LEFT JOIN brand  ON brand.id=bd.brandId " +
		"where bbd.`enable`=0 and  bd.amount > 0 and bbd.buyerDate < ? and bbd.buyerDate > ? and  pa.name='通辽牧场' "

	queryData, err := restful.MapStrList(sqls, "2021-08-27", "2021-06-30")
	//s_params := make([]interface{}, 0)
	//queryData, err := restful.QueryByListListT(sql, 0, 0, s_params, tx)
	if err != nil {
		fmt.Println(err)
	} else {
		f := excelize.NewFile()
		// 创建一个工作表
		//供应商	 单价	申购数量	采购数量	入库数	申购部门	采购人	配单日期 入库时间
		index := f.NewSheet("采购明细")
		f.SetColWidth("采购明细", "A", "R", 20)
		columns := []string{"备件品牌", "计量单位", "供应商", "单价", "申购数量", "采购数量", "入库数", "申购部门", "采购人", "配单日期",
			"入库时间"}
		letters := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
		for i, v := range columns {
			f.SetCellValue("采购明细", letters[i]+"1", v)
		}
		fmt.Println(len(queryData))
		for i, v := range queryData {
			// 设置单元格的值
			value := []interface{}{v["matchCode"], v["unit"], v["providerName"], v["price"], v["sumAmount"], v["amount"], v["receiveAmount"],
				v["departmentName"], v["empname"], v["buyerDate"], v["DoneDate"]}
			for a, b := range value {
				f.SetCellValue("采购明细", letters[a]+strconv.Itoa(i+2), b)
			}
		}
		// 设置工作簿的默认工作表
		f.SetActiveSheet(index)
		// 根据指定路径保存文件
		if err := f.SaveAs("D:/project/go/src/KPTcomm/test/采购明细.xlsx"); err != nil {
			fmt.Println(err)
		}
	}

}

// 测试
func DownloadEquipment(parammaps string) (string, error) {
	//appG := app.Gin{C: context}
	data := make(map[string]interface{}, 0)
	filename := setting.CurrentPath + "uploads/Downloadmailreport/" + "设备指标.xlsx"
	exist, _ := FileExists(filename)
	if exist {
		//return filename, nil
	}
	f := excelize.NewFile()
	Style, err := f.NewStyle("{\"Alignment\":{\"Vertical\": \"center\", \"Horizontal\":\"center\"}}")
	columns := []string{"设备指标", "维修费", "水费", "电费", "柴油费", "水量", "电量", "柴油用量"}
	//序号	牧场	 部门	预算	 内控	迄今内控 	迄今实际	 实际节约	实际达成率	单头牛预算	单头牛内控	单头牛迄今内控	单头牛迄今实际	单头牛实际节约
	columns1 := []string{"序号", "牧场", "部门", "预算", "内控", "迄今内控", "迄今实际", "实际节约", "实际达成率", "单头牛预算",
		"单头牛内控", "单头牛迄今内控", "单头牛迄今实际", "单头牛实际节约"}
	letters := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
	for i, v := range columns {
		f.NewSheet("Sheet" + strconv.Itoa(i+1))
		f.SetSheetName("Sheet"+strconv.Itoa(i+1), v)
		f.MergeCell(v, "A1", "C1")
		f.MergeCell(v, "D1", "I1")
		f.MergeCell(v, "J1", "N1")
		f.SetCellValue(v, "A1", "设备指标")
		f.SetCellValue(v, "D1", "设备指标")
		f.SetCellValue(v, "J1", "单头牛指标")
		f.SetCellStyle(v, "A1", "CS154", Style)
		f.SetCellStyle(v, "D1", "CS154", Style)
		f.SetCellStyle(v, "J1", "CS154", Style)
		f.SetColWidth(v, "A", "N", 20)
		for i1, v1 := range columns1 {
			f.SetCellValue(v, letters[i1]+"2", v1)
		}

	}

	sqlnames := []string{"getChooseEqMonthBudgetListSumAll", "getChooseMonthWX", "getChooseElectricityPrice", "getChooseMonthiolPrice",
		"getChooseWaterPrice", "getChooseMonthElectricity", "getChooseMonthWater", "getChooseMonthOilT"}
	xx := 0
	for z, sqlname := range sqlnames {
		valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", sqlname)
		parammap := make(map[string]interface{}, 0)
		json.Unmarshal([]byte(parammaps), &parammap)
		s_params := make([]interface{}, 0)
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammap[strings.Trim(value, " ")])
			}
		}
		values, err := restful.MapStrList(valuesMap["sqlstr"], s_params...)
		if err != nil {
			fmt.Println(err)
		} else {
			for i, v := range values {
				value := []interface{}{columns[z], v["pastureName"], "全厂区", v["monthBudget"], v["monthLimit"], v["qmonthBudget"], v["sumPrice"],
					v["asave"], v["realReason"], v["oneCowBudget"], v["oneCowLimit"], v["oneCowQmonthBudget"], v["oneCowSumPrice"], v["oneCowAsave"]}
				for a, b := range value {
					f.SetCellValue("设备指标", letters[a]+strconv.Itoa(i+xx+3), b)
				}
			}
		}
		xx += len(values)
	}

	//"维修费","水费","电费","柴油费","水量","电量","柴油用量"
	// 其他的表格
	sqlnamesDepartment := []string{
		"getChooseMonthWX",
		"getChooseWaterPrice",
		"getChooseElectricityPrice",
		"getChooseMonthiolPrice",
		"getChooseMonthWater",
		"getChooseMonthElectricity",
		"getChooseMonthOilT"}
	// sqlnamesDepartment := []string{"getChooseMonthWXDepartment", "getChooseWaterPriceDepartment", "getChooseElectricityPriceDepartment", "getChooseMonthiolPriceDepartment",
	// 	"getChooseMonthWaterDepartment", "getChooseMonthElectricityDepartment", "getChooseMonthOilTDepartment"}
	columnss := []string{"维修费", "水费", "电费", "柴油费", "水量", "电量", "柴油用量"}

	for z, sqlname := range sqlnamesDepartment {
		valuesMap, err := restful.MapStr("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", sqlname)
		parammap := make(map[string]interface{}, 0)
		json.Unmarshal([]byte(parammaps), &parammap)
		s_params := make([]interface{}, 0)
		paramslist := strings.Split(valuesMap["params"], ",")
		if len(paramslist) > 0 && valuesMap["params"] != "" {
			for _, value := range paramslist {
				s_params = append(s_params, parammap[strings.Trim(value, " ")])
			}
		}
		values, err := restful.MapStrList(valuesMap["sqlstr"], s_params...)
		if err != nil {
			fmt.Println(err)
		} else {
			for i, v := range values {
				value := []interface{}{v["sort"], v["pastureName"], v["deptname"], v["monthBudget"], v["monthLimit"], v["qmonthBudget"], v["sumPrice"],
					v["asave"], v["realReason"], v["oneCowBudget"], v["oneCowLimit"], v["oneCowQmonthBudget"], v["oneCowSumPrice"], v["oneCowAsave"]}
				for a, b := range value {
					f.SetCellValue(columnss[z], letters[a]+strconv.Itoa(i+3), b)
				}
			}
		}
	}
	if err != nil {
		data["err_count"] = err
		logging.Error("设备指标导出__error:", err)
	} else {

		err = PathCheck("uploads/Downloadmailreport") //检查路径并创建
		//os.Remove(filename)
		if err != nil {
			println("PathCheck err", err)
		}

		if err := f.SaveAs(filename); err != nil {
			println(err.Error())
			return "", err
		}
		//data["success"] = "ok"
		//context.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
		//context.Header("Content-Type","application/octet-stream")
		//context.Header("Content-Disposition", "attachment; filename="+"Workbook.xlsx")
		//context.Header("Content-Transfer-Encoding", "binary")
		//_ = f.Write(context.Writer)
	}
	return filename, err
	//appG.Response(http.StatusOK, e.SUCCESS, data)
}
func SetSheetRow(f *excelize.File, sheet, axis string, slice interface{}) error {
	SetSheetRow(f, sheet, axis, slice)
	return nil
}