crm/.svn/pristine/7e/7ed24e7a360a4af67cf26ff3fb9f59b30900af16.svn-base

package util

import (
	"../../models"
	"../../pkg/logging"
	"../../pkg/setting"
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/jordan-wright/email"
	"github.com/robfig/cron"
	"log"
	"net/smtp"
	"os"
	"strconv"
	"strings"
	"time"
)

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)
	Engine := models.Engine
	subject := ""
	note := ""
	mailreport, err := Engine.SQL("select * from mailreport where enable = 1").Query().List()
	if err != nil {
		println("err sql", err.Error())
	} else {
		for _, value := range mailreport {
			//GetMailDataByPasture("集团设备指标",value["name"].(string),value["id"],A,f)
			//GetPastureDeptData(value["name"].(string),value["id"],f)
			if value["type"].(string) == "1" {
				fromName = value["addr"].(string)
				pas = value["pas"].(string)
				subject = value["title"].(string)
				note = value["note"].(string)

			} else {
				mails = append(mails, value["addr"].(string))
			}
			println(value["addr"].(string))
		}
	}
	// 发件人
	//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 := f.SetSheetRow("集团设备指标", "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 := f.SetSheetRow("集团设备指标", "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+"年")

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

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

	valuesMap, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportJY").QueryString()
	valuesMap1, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportJM").QueryString()
	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[0]["sqlstr"]
		paramslist := strings.Split(valuesMap[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap[0]["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 := Engine.SQL(sqls, s_params...).Query().List()
	B := A
	//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"])
	//		if err := f.SetSheetRow("集团设备指标", "B"+strconv.Itoa(B),&tem); err != nil {
	//			println(err.Error())
	//		}
	//		tem = []interface{}{}
	//		B++
	//	}
	//}else {
	//	println(err.Error())
	//}
	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"].(string)] = tem
			//if err := f.SetSheetRow("集团设备指标", "B"+strconv.Itoa(B),&tem); err != nil {
			//	println(err.Error())
			//}
			tem = []interface{}{}
			//B++
		}
	} else {
		println(err.Error())
	}
	if m, ok := temMap["维修费（万元）"]; ok {
		if err := f.SetSheetRow("集团设备指标", "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := f.SetSheetRow("集团设备指标", "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

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

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

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

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

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

	s_params = make([]interface{}, 0)
	if err == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1[0]["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = time.Now().Format("2006-01-02")
		paramslist := strings.Split(valuesMap1[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap1[0]["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 = Engine.SQL(sqls, s_params...).Query().List()
	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"].(string))
			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 := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "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...)
	}

	Engine := models.Engine
	valuesMap, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMY").QueryString()
	valuesMap1, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMM").QueryString()
	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[0]["sqlstr"]
		paramslist := strings.Split(valuesMap[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap[0]["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 := Engine.SQL(sqls, s_params...).Query().List()
	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"].(string)] = tem
			//if err := f.SetSheetRow(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 := f.SetSheetRow(sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := f.SetSheetRow(sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

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

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

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

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

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

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

			}
		}
	}

	values, err = Engine.SQL(sqls, s_params...).Query().List()
	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"].(string))
			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 := f.SetSheetRow(sheetName, "N"+strconv.Itoa(B), &c); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "N"+strconv.Itoa(B+1), &d); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "N"+strconv.Itoa(B+2), &e); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "N"+strconv.Itoa(B+3), &g); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "N"+strconv.Itoa(B+4), &r); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "N"+strconv.Itoa(B+5), &h); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "N"+strconv.Itoa(B+6), &p); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(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...)
	}

	Engine := models.Engine
	valuesMap, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportBY").QueryString()
	valuesMap1, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportBM").QueryString()
	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[0]["sqlstr"]
		paramslist := strings.Split(valuesMap[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap[0]["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 := Engine.SQL(sqls, s_params...).Query().List()
	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"].(string)] = tem
			//if err := f.SetSheetRow(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 := f.SetSheetRow(sheetName, "B"+strconv.Itoa(Bt), &m); err != nil {
			println(err.Error())
		}
		Bt++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := f.SetSheetRow(sheetName, "B"+strconv.Itoa(Bt), &temw); err != nil {
			println(err.Error())
		}
		Bt++
	}

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

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

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

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

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

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

			}
		}
	}

	values, err = Engine.SQL(sqls, s_params...).Query().List()
	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 {
			//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"].(string))
			}

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

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

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

	valuesMap, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMY").QueryString()
	valuesMap1, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMM").QueryString()
	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[0]["sqlstr"]
		paramslist := strings.Split(valuesMap[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap[0]["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 := Engine.SQL(sqls, s_params...).Query().List()
	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"].(string)] = tem
			//if err := f.SetSheetRow(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 := f.SetSheetRow(sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := f.SetSheetRow(sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

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

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

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

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

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

	s_params = make([]interface{}, 0)
	if err == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1[0]["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = time.Now().Format("2006-01-02")
		parammaps["pastureId"] = pastureId
		paramslist := strings.Split(valuesMap1[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap1[0]["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 = Engine.SQL(sqls, s_params...).Query().List()
	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"].(string))
			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 := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(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 := f.SetSheetRow("集团设备指标", "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+"年")

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

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

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

	tem = []interface{}{}

	values, err := Engine.SQL(sqls, s_params...).Query().List()
	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"].(string)] = tem

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

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

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

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

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

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

	s_params = make([]interface{}, 0)
	if err == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1[0]["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = date
		paramslist := strings.Split(valuesMap1[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap1[0]["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 = Engine.SQL(sqls, s_params...).Query().List()
	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"].(string))
			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 := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "N"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow("集团设备指标", "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 := f.SetSheetRow(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+"年")

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

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

	valuesMap, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMY").QueryString()
	valuesMap1, err := Engine.SQL("SELECT sqlstr,params FROM apisql WHERE sqlname = ? AND ENABLE>0 ", "getMailReportMM").QueryString()
	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[0]["sqlstr"]
		paramslist := strings.Split(valuesMap[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap[0]["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 := Engine.SQL(sqls, s_params...).Query().List()
	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"].(string)] = tem
			//if err := f.SetSheetRow(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 := f.SetSheetRow(sheetName, "B"+strconv.Itoa(B), &m); err != nil {
			println(err.Error())
		}
		B++
	} else {
		temw := []interface{}{"维修费（万元）"}
		if err := f.SetSheetRow(sheetName, "B"+strconv.Itoa(B), &temw); err != nil {
			println(err.Error())
		}
		B++
	}

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

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

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

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

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

	s_params = make([]interface{}, 0)
	if err == nil && len(valuesMap1) > 0 {
		sqls = valuesMap1[0]["sqlstr"]
		parammaps := make(map[string]interface{}, 0)
		parammaps["month"] = date
		parammaps["pastureId"] = pastureId
		paramslist := strings.Split(valuesMap1[0]["params"], ",")
		if len(paramslist) > 0 && valuesMap1[0]["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 = Engine.SQL(sqls, s_params...).Query().List()
	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"].(string))
			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 := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A), &c); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+1), &d); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+2), &e); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+3), &g); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+4), &r); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+5), &h); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(sheetName, "J"+strconv.Itoa(A+6), &p); err != nil {
		println(err.Error())
	}
	if err := f.SetSheetRow(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
}