package api

import (
	"fmt"
	"io/ioutil"
	"log"
	"math"
	"net/http"
	"sort"
	"strconv"
	"strings"
	"sync"
	"time"

	"../../utils"

	"../../pkg/app"
	"../../pkg/e"
	"../../routers/restful"
	"github.com/Anderson-Lu/gofasion/gofasion"
	"github.com/gin-gonic/gin"
	"github.com/kptyun/KPTCOMM/pkg/util"
	"github.com/tealeg/xlsx"
)

func GetProductAreas(c *gin.Context) {
	appG := app.Gin{C: c}

	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))

	offset := fsion.Get("offset").ValueDefaultInt(0)
	pagecount := fsion.Get("pagecount").ValueDefaultInt(0)

	parammaps := fsion.Get("parammaps")
	productname := parammaps.Get("productname").ValueStr()
	classids := parammaps.Get("productclassid").ValueStr()

	var recordcount int64 = 0
	queryData := make([]map[string]interface{}, 1)
	var err error
	tx := restful.Engine.NewSession()
	defer tx.Close()
	{
		sqlstr := `SELECT t1.id,t1.productname,t1.spec FROM productinfo t1 
	INNER JOIN prounit t2 ON t1.prounit = t2.id where 1=1 and t1.enable = 1 `
		if len(classids) > 0 {
			sqlstr += fmt.Sprintf(" and  t1.productclass in ('%s')", classids)
		}
		if len(productname) > 0 {
			sqlstr += fmt.Sprintf(" and  t1.productname like ('%s')", "%"+productname+"%")
		}

		tt, err := tx.SQL(sqlstr).Query().Count()
		if err != nil {
			log.Println("GetProductAreas-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
		recordcount = int64(tt)
	}

	if pagecount == 0 {
		sqlstr := `SELECT t1.id as productid,t1.productname,ifnull(t1.spec,"")   as specifications,ifnull(t2.unitname,"") as unit
		FROM productinfo t1 
		INNER JOIN prounit t2 ON t1.prounit = t2.id where 1=1 and t1.enable = 1 `
		if len(classids) > 0 {
			sqlstr += fmt.Sprintf(" and  t1.productclass in ('%s')", classids)
		}
		if len(productname) > 0 {
			sqlstr += fmt.Sprintf(" and  t1.productname like ('%s')", "%"+productname+"%")
		}

		query := tx.SQL(sqlstr)
		queryData, err = query.Query().List()
		if err != nil {
			log.Println("GetProductAreas-err2: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
	} else {
		sqlstr := `SELECT t1.id as productid,t1.productname,ifnull(t1.spec,"")  as specifications,ifnull(t2.unitname,"") as unit
		FROM productinfo t1 
		INNER JOIN prounit t2 ON t1.prounit = t2.id where 1=1  and t1.enable = 1 `

		if len(classids) > 0 {
			sqlstr += fmt.Sprintf(" and  t1.productclass in ('%s')", classids)
		}
		if len(productname) > 0 {
			sqlstr += fmt.Sprintf(" and  t1.productname like ('%s')", "%"+productname+"%")
		}

		queryData, err = tx.SQL(sqlstr).Limit(pagecount, (offset-1)*pagecount).Query().List()
		if err != nil {
			log.Println("GetProductAreas-err3: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
	}

	headlist := make([]map[string]interface{}, 0)
	areaData := make([]map[string]interface{}, 0)
	{
		sqlstr := `SELECT
		t1.id AS productareaid,
		t1.AreaId as areaid,
		t2.name,
		t1.Productid  as productid
	FROM
		productarea t1
		INNER JOIN canton t2 ON t1.AreaId = t2.ID order by areaid`

		areaData, err = tx.SQL(sqlstr).Query().List()
		if err != nil {
			log.Println("GetProductAreas-err4: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}

		areaMap := make(map[interface{}]interface{})
		contactlistMap := make(map[interface{}][]interface{})
		for _, area := range areaData {
			areaMap[area["areaid"]] = area["name"]
			contactlistMap[area["productid"]] = append(contactlistMap[area["productid"]], area["areaid"])
		}
		areaids := []int64{}
		for areaid, _ := range areaMap {
			areaids = append(areaids, areaid.(int64))
		}

		utils.QuickSort(areaids, 0, int64(len(areaids)-1))
		for _, v := range areaids {
			head := make(map[string]interface{})
			head["column_name"] = fmt.Sprintf("column%v", v)
			head["column_title"] = areaMap[v]
			head["column_id"] = v
			headlist = append(headlist, head)
		}

		for _, data := range queryData {
			for id, _ := range areaMap {
				data[fmt.Sprintf("column%v", id)] = 0
			}

			if _, ok := contactlistMap[data["productid"]]; ok {
				for _, areaid := range contactlistMap[data["productid"]] {
					data[fmt.Sprintf("column%v", areaid)] = 1
				}
			}

		}
	}

	data := make(map[string]interface{})

	data["headlist"] = headlist
	data["list"] = queryData
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if pagecount > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(pagecount))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(pagecount))
	}
	data["pageSize"] = pagecount

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

//查看发布价格
func GetPublishPrice(c *gin.Context) {
	areaids := getroleareaid(c.GetHeader("token"))
	user, _ := util.ParseToken(c.GetHeader("token"))
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	offset := fsion.Get("offset").ValueDefaultInt(0)
	pagecount := fsion.Get("pagecount").ValueDefaultInt(0)
	parammaps := fsion.Get("parammaps")
	productclass := parammaps.Get("productclassid").ValueInt64()
	date := parammaps.Get("starttime").ValueStr()
	productname := parammaps.Get("productname").ValueStr()

	data := make(map[string]interface{})
	var err error
	queryData := make([]map[string]interface{}, 0)
	var recordcount int

	tx := restful.Engine.NewSession()
	defer tx.Close()
	{
		sqlstr := `select   if(t5.code= "",0,1) as coedisnull ,ifnull(t5.code,"") as productcode ,t3.classname,t1.id as productid,t1.productname,t1.productclass,ifnull(t1.spec,"") as 	specifications ,ifnull(t2.unitname,"") as unit from productinfo  t1  
		left join prounit  t2 on t2.id = t1.prounit   join productclass t3 on t3.id = t1.productclass join productarea t4 on t1.id = t4.Productid  
		`
		if len(areaids) > 0 {
			sqlstr += fmt.Sprintf(" and t4.Areaid in (%s)", areaids)
			// args = append(args, areaids)
		}

		var args []interface{}
		args = append(args, user.Username)

		sqlstr += "left join productusercode t5  on t5.userid = ? and t5.productid = t1.id"
		sqlstr += ` where 1=1 and t1.enable = 1 `

		if productclass > 0 {
			sqlstr += " and t1.productclass = ?"
			args = append(args, productclass)
		}
		if len(productname) > 0 {
			sqlstr += " and t1.productname like CONCAT('%',?,'%')"
			args = append(args, productname)
		}

		sqlstr += " group by t1.id order by coedisnull desc ,t5.code +0  , t3.ordernum,t3.id,t1.ordernum,t1.id asc "
		if pagecount > 0 {
			sqlstr += "  limit ?,?"
			args = append(args, (offset-1)*pagecount, pagecount)
		}

		productrow := tx.SQL(sqlstr, args...)
		defer productrow.Close()
		queryData, err = productrow.Query().List()
		if err != nil {
			log.Println("GetPublishPrice-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
		// var args1 []interface{}
		// sqlstr1 := `select t1.id from productinfo  t1
		// join prounit  t2 on t2.id = t1.prounit  where 1=1 and t1.enable = 1 `

		// if productclass > 0 {
		// 	sqlstr1 += " and t1.productclass = ?"
		// 	args1 = append(args1, productclass)
		// }

		// if len(productname) > 0 {
		// 	sqlstr1 += " and t1.productname like CONCAT('%',?,'%')"
		// 	args1 = append(args1, productname)
		// }

		// row := tx.SQL(sqlstr1, args1...)
		// defer row.Close()
		// count, err := row.Query().Count()
		// if err != nil {
		// 	log.Println("GetPublishPrice-err2: ", err)
		// 	appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		// 	return
		// }
		// recordcount = count
	}

	productidlist := []string{"0"}
	for _, data := range queryData {
		productidlist = append(productidlist, strconv.FormatInt(data["productid"].(int64), 10))
	}

	type areaid struct {
		areaid   int64
		name     string
		parentid int64
		layer    int64
		sort     int64
	}

	headlist := make([]map[string]interface{}, 0)
	{
		// sqlstr := `select t1.id,t1.name,t1.parent_id as parentid ,t1.layer,t1.ord  from canton t1
		// join (select DISTINCT(areaid) as areaid,t2.name from productarea t1 join canton t2 on t2.id = t1.areaid
		// where productid in (%s)) t2 on (t1.id = t2.areaid or t1.PARENT_ID = t2.areaid)`
		sqlstr := ` select t1.id,t1.name,t1.parent_id as parentid ,t1.layer,t1.ord  from canton t1 where layer >0  `
		if len(areaids) > 0 {
			sqlstr += fmt.Sprintf(" and (parent_id in (%s) or id in (%s))", areaids, areaids)
		}
		arearow := tx.SQL(sqlstr)
		areaData, err := arearow.Query().List()
		if err != nil {
			log.Println("GetPublishPrice-err3: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}

		bigarea := make([]*areaid, 0)
		wg := sync.WaitGroup{}
		wg.Add(len(areaData))
		for _, area := range areaData {
			go func(area map[string]interface{}) {
				if area["layer"].(int64) == 1 {
					bigarea = append(bigarea, &areaid{
						areaid:   area["id"].(int64),
						name:     area["name"].(string),
						parentid: area["parentid"].(int64),
						layer:    area["layer"].(int64),
						sort:     area["ord"].(int64),
					})
				}
				wg.Done()
			}(area)
		}
		wg.Wait()

		sort.Slice(bigarea, func(i, j int) bool {
			return bigarea[i].areaid < bigarea[j].areaid // 升序
		})
		sort.Slice(bigarea, func(i, j int) bool {
			return bigarea[i].sort < bigarea[j].sort // 升序
		})

		for _, big := range bigarea {
			head := make(map[string]interface{})
			head["column_id"] = big.areaid
			head["column_name"] = fmt.Sprintf("column_%d", big.areaid)
			head["column_title"] = big.name
			smallarea := make([]*areaid, 0)
			for _, area := range areaData {
				if area["parentid"].(int64) == big.areaid {
					smallarea = append(smallarea, &areaid{
						areaid:   area["id"].(int64),
						name:     area["name"].(string),
						parentid: area["parentid"].(int64),
						layer:    area["layer"].(int64),
						sort:     area["ord"].(int64),
					})
				}
			}
			sort.Slice(smallarea, func(i, j int) bool {
				return smallarea[i].areaid < smallarea[j].areaid // 升序
			})
			sort.Slice(smallarea, func(i, j int) bool {
				return smallarea[i].sort < smallarea[j].sort // 升序
			})
			smallhead := make([]map[string]interface{}, 0)
			for _, small := range smallarea {
				data1 := make(map[string]interface{})
				data1["column_id"] = small.areaid
				data1["column_name"] = fmt.Sprintf("column_%d", small.areaid)
				data1["column_title"] = small.name
				smallhead = append(smallhead, data1)
			}
			head["headlist"] = smallhead
			headlist = append(headlist, head)
		}

		productpricesql := `SELECT  productid,marketid as smallareaid , marketclass as bigareaid,price FROM (
			SELECT * FROM productprice WHERE productid in (%s)   `

		productpricesql = fmt.Sprintf(productpricesql, strings.Join(productidlist, ","))
		if date != "" {
			start := fmt.Sprintf("%s 00:00:00", date)
			end := fmt.Sprintf("%s 23:59:59", date)
			productpricesql += fmt.Sprintf(" and pricedate BETWEEN  '%s'  and '%s' ", start, end)
		}

		productpricesql += " ORDER BY pricedate DESC limit 1000000000000000) AS t  where 1=1"
		if len(areaids) > 0 {
			productpricesql += fmt.Sprintf(" and t.marketclass in (%s)", areaids)
		}
		productpricesql += " group by productid, marketid  "
		productpricerow := tx.SQL(productpricesql)
		defer productpricerow.Close()
		productpriceData, err := productpricerow.Query().List()
		if err != nil {
			log.Println(err)
			return
		}
		type priceStruct struct {
			marketid int64
			price    float64
		}
		priceMap := make(map[int64][]priceStruct)

		for _, productprice := range productpriceData {
			priceintface, _ := strconv.ParseFloat(productprice["price"].(string), 64)
			priceMap[productprice["productid"].(int64)] = append(priceMap[productprice["productid"].(int64)], priceStruct{
				marketid: productprice["smallareaid"].(int64),
				price:    priceintface,
			})
		}

		wg.Add(len(queryData))
		for _, query := range queryData {
			go func(query map[string]interface{}) {
				for _, area := range areaData {
					if area["layer"].(int64) == 2 {
						query[fmt.Sprintf("column_%v", area["id"])] = 0
					}
				}
				query["Edit"] = false
				query["NoEdit"] = true
				query["isCreate"] = false
				query["isUpdate"] = true
				query["isUpdateSave"] = false
				wg.Done()
			}(query)
		}
		wg.Wait()

		wg.Add(len(queryData))
		for _, query := range queryData {
			go func(query map[string]interface{}) {
				for _, price := range priceMap[query["productid"].(int64)] {
					query[fmt.Sprintf("column_%d", price.marketid)] = price.price
				}
				wg.Done()
			}(query)
		}
		wg.Wait()
		data["list"] = queryData
	}

	data["headlist"] = headlist
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if pagecount > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(pagecount))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(pagecount))
	}
	data["pageSize"] = pagecount

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

//修改发布价格信息
func UpdatePublishPrice(c *gin.Context) {
	appG := app.Gin{C: c}
	user, _ := util.ParseToken(c.GetHeader("token"))

	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	params := fsion.Get("params").Array()

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

	areamap := make([]map[string]interface{}, 0)
	pricemap := make([]map[string]interface{}, 0)
	var err error
	if len(params) > 0 {
		areaidlist := []string{"0"}
		for _, key := range params[0].Keys() {
			if strings.Index(key, "column_") > -1 {
				areaidlist = append(areaidlist, key[len("column_"):])
			}
		}
		areamap, err = tx.SQL(fmt.Sprintf(`select t1.id,t1.PARENT_ID as parentid from canton t1
		 join canton t2 on t1.PARENT_ID = t2.id  where t1.id in (%s)
		  order by t2.ORD  ,t1.ORD,t1.id`, strings.Join(areaidlist, ","))).Query().List()
		if err != nil {
			log.Println("UpdatePublishPrice-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}

		productidlist := []string{}
		for _, param := range params {
			productidlist = append(productidlist, param.Get("productid").ValueStr())
		}

		pricemap, err = tx.SQL(fmt.Sprintf(`SELECT   t.marketid ,t.marketclass,t.price , t.pricedate,id,productid FROM (
			SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate DESC limit 10000000000000000
			) AS t 
			GROUP BY t.marketid  ,productid `, strings.Join(productidlist, ","))).Query().List()
		if err != nil {
			log.Println("UpdatePublishPrice-err2: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}

	}
	var wg sync.WaitGroup
	for _, param := range params {
		productid := param.Get("productid").ValueInt64()
		productclass := param.Get("productclass").ValueStr()
		productcode := param.Get("productcode").ValueStr()
		for _, area := range areamap {
			pricenum := param.Get(fmt.Sprintf("column_%v", area["id"])).ValueFloat64()
			isupdateprice := false
			exist := false
			for _, price := range pricemap {
				if area["id"].(int64) == price["marketid"].(int64) && productid == price["productid"].(int64) {
					priceintface, _ := strconv.ParseFloat(price["price"].(string), 64)
					if priceintface == pricenum {
						isupdateprice = true
					}
					exist = true
					break
				}
			}
			if (!isupdateprice && (exist)) || (!isupdateprice && !exist && pricenum != 0) {
				var args []interface{}
				args = append(args, productid, area["id"], pricenum, area["parentid"], productclass)

				_, err := tx.SQL("insert into productprice(productid,marketid,price,marketclass,productclass,pricedate)values(?,?,?,?,?,now())", args...).Execute()
				if err != nil {
					log.Println("UpdatePublishPrice-err4: ", err)
					appG.Response(http.StatusInternalServerError, e.ERROR, nil)
					return
				}

			}
		}
		wg.Add(1)
		go func(user *util.Claims, productcode string, productid int64) {
			defer wg.Done()
			_, err := tx.SQL(`insert into productusercode(userid,code,productid)value(?,?,?)
		ON DUPLICATE KEY UPDATE  code = ?`, user.Username, productcode, productid, productcode).Execute()
			if err != nil {
				log.Println("UpdatePublishPrice-err5: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}
		}(user, productcode, productid)
	}
	wg.Wait()
	appG.Response(http.StatusOK, e.SUCCESS, nil)
}

//查看产品信息 GetProductInfo
func GetProductInfo(c *gin.Context) {
	appG := app.Gin{C: c}

	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	offset := fsion.Get("offset").ValueDefaultInt(0)
	pagecount := fsion.Get("pagecount").ValueDefaultInt(0)
	parammaps := fsion.Get("parammaps")
	smallclassid := parammaps.Get("smallclassid").ValueStr()
	productname := parammaps.Get("productname").ValueStr()

	var args []interface{}

	tx := restful.Engine.NewSession()
	defer tx.Close()
	sqlstr := `  select t1.id as productid ,t1.productname,  IFNULL(t1.spec,'') as specifications  ,t2.unitname as unit,t1.prounit as unitid ,t1.enable,t1.ordernum as sort ,  t3.productclass ,t3.id as smallclassid 
	from productinfo t1 
inner join prounit t2  on t2.id = t1.prounit
inner join (select t1.classname as smallname,t2.classname as bigname,t1.id,CONCAT(t2.classname,'-',t1.classname)as productclass ,t1.ordernum as smallsort ,t2.ordernum as bigsort
from productclass t1  join productclass t2 on t2.id = t1.parentid where t1.leavel = 2) t3 on t3.id = t1.productclass 
where  1=1     `
	if smallclassid != "" {
		sqlstr += "  and t1.productclass  =  ? "
		args = append(args, smallclassid)
	}
	if productname != "" {
		sqlstr += "  and t1.productname  like CONCAT('%',?,'%')  "
		args = append(args, productname)
	}

	sqlstr += "  order  by t3.bigsort,t3.smallsort ,t1.ordernum"

	arearow := tx.SQL(sqlstr, args...)
	defer arearow.Close()
	queryData := make([]map[string]interface{}, 0)
	var err error

	if pagecount > 0 {
		queryData, err = arearow.Limit(pagecount, (offset-1)*pagecount).Query().List()
		if err != nil {
			log.Println("GetProductInfo-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
	} else {
		queryData, err = arearow.Query().List()
		if err != nil {
			log.Println("GetProductInfo-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
	}

	sqlstrcount := `select t1.id
	from productinfo t1 
inner join prounit t2  on t2.id = t1.prounit
inner join (select t1.classname as smallname,t2.classname as bigname,t1.id,CONCAT(t2.classname,'-',t1.classname)as productclass ,t1.ordernum as smallsort ,t2.ordernum as bigsort
from productclass t1  join productclass t2 on t2.id = t1.parentid where t1.leavel = 2) t3 on t3.id = t1.productclass 
where  1=1   and t1.enable = 1 `
	if smallclassid != "" {
		sqlstrcount += "  and t1.productclass  =  ? "
	}
	if productname != "" {
		sqlstrcount += "  and t1.productname  like CONCAT('%',?,'%')  "
	}

	countrow := tx.SQL(sqlstr, args...)
	recordcount, err := countrow.Query().Count()
	if err != nil {
		log.Println("GetProductInfo-err2: ", err)
		appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		return
	}

	productids := []string{"0"}
	for _, query := range queryData {
		productids = append(productids, fmt.Sprintf("%v", query["productid"]))
	}

	pricemap, err := tx.SQL(fmt.Sprintf(`SELECT  productid,price FROM (
		SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate DESC limit 10000000000000000
		) AS t   group by productid `, strings.Join(productids, ","))).Query().List()
	if err != nil {
		log.Println("GetProductInfo-err3: ", err)
		appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		return
	}

	for _, query := range queryData {
		productid := query["productid"].(int64)
		for _, price := range pricemap {
			if productid == price["productid"].(int64) {
				query["price"] = price["price"]
			}
		}
	}
	data := make(map[string]interface{})
	data["list"] = queryData
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if pagecount > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(pagecount))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(pagecount))
	}
	data["pageSize"] = pagecount

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

func GetAveragePrice(c *gin.Context) {
	areaids := getroleareaid(c.GetHeader("token"))
	user, _ := util.ParseToken(c.GetHeader("token"))
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	offset := fsion.Get("offset").ValueDefaultInt(0)
	pagecount := fsion.Get("pagecount").ValueDefaultInt(0)
	parammaps := fsion.Get("parammaps")
	productclass := parammaps.Get("productclassid").ValueStr()
	productid := parammaps.Get("productid").ValueInt64()
	starttime := parammaps.Get("starttime").ValueStr()
	endtime := parammaps.Get("endtime").ValueStr()
	productname := parammaps.Get("productname").ValueStr()
	userid := parammaps.Get("userid").ValueStr()

	var productidlist []string
	if productid != 0 {
		productidlist = append(productidlist, fmt.Sprintf("%d", productid))
	}
	data := make(map[string]interface{})
	var err error
	queryData := make([]map[string]interface{}, 0)
	var recordcount int

	tx := restful.Engine.NewSession()
	defer tx.Close()
	if userid != "" {
		productidmap, err := tx.SQL("select Productid as productid from attention where userid = ?", userid).Query().List()
		if err != nil {
			log.Println("GetAveragePrice-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
		for _, product := range productidmap {
			productidlist = append(productidlist, fmt.Sprintf("%v", product["productid"]))
		}
		if len(productidlist) == 0 {
			appG.Response(http.StatusOK, e.SUCCESS, data)
			return
		}
	}
	{
		// sqlstr := `select t1.id as productid,t1.productname,t1.productclass,ifnull(t1.spec,"") as 	specifications ,ifnull(t2.unitname,"") as unit from productinfo  t1
		// join prounit  t2 on t2.id = t1.prounit join productclass t3 on t3.id = t1.productclass   where 1=1  and t1.enable = 1 `
		sqlstr := `select  if(t5.code= "",0,1) as coedisnull, ifnull(t5.code,"") productcode,t3.classname,t1.id as productid,t1.productname,t1.productclass,ifnull(t1.spec,"") as 	specifications ,ifnull(t2.unitname,"") as unit from productinfo  t1  
		left	join prounit  t2 on t2.id = t1.prounit   join productclass t3 on t3.id = t1.productclass join productarea t4 on t1.id = t4.Productid 
		`

		if len(areaids) > 0 {
			sqlstr += fmt.Sprintf(" and t4.Areaid in (%s)", areaids)
		}

		sqlstr += "		left join productusercode t5  on t5.userid = ? and t5.productid = t1.id"
		var args []interface{}
		args = append(args, user.Username)
		sqlstr += ` where 1=1 and t1.enable = 1 `

		// var args []interface{}
		if len(productclass) > 0 {
			sqlstr += fmt.Sprintf(" and t1.productclass in (%s)", productclass)
		}
		if len(productname) > 0 {
			sqlstr += " and t1.productname like CONCAT('%',?,'%')"
			args = append(args, productname)
		}
		// if productid != 0 {
		// 	sqlstr += " and t1.id in (?) "
		// 	args = append(args, strings.Join(productidlist, ","))
		// }
		if len(productidlist) > 0 {
			sqlstr += fmt.Sprintf(" and t1.id in (%s) ", strings.Join(productidlist, ","))
		}
		sqlstr += "group by t1.id order by  coedisnull desc ,t5.code +0 , t3.ordernum,t3.id,t1.ordernum,t1.id"
		if pagecount > 0 && productid == 0 {
			sqlstr += "  limit ?,?"
			args = append(args, (offset-1)*pagecount, pagecount)
		}

		productrow := tx.SQL(sqlstr, args...)
		defer productrow.Close()
		queryData, err = productrow.Query().List()
		if err != nil {
			log.Println("GetAveragePrice-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
		if productid == 0 {
			var args1 []interface{}
			sqlstr1 := `select t1.id from productinfo  t1  
			left join prounit  t2 on t2.id = t1.prounit  join productarea t4 on t1.id = t4.Productid  where 1=1  and t1.enable = 1  `

			// var args []interface{}
			if len(areaids) > 0 {
				// sqlstr1 += " and t4.Areaid in (?)"
				sqlstr1 += fmt.Sprintf(" and t4.Areaid in (%s)", areaids)
				// args1 = append(args, areaids)
			}

			if len(productclass) > 0 {
				sqlstr1 += fmt.Sprintf(" and t1.productclass in (%s) ", productclass)
			}

			if len(productname) > 0 {
				sqlstr1 += " and t1.productname like CONCAT('%',?,'%')"
				args1 = append(args1, productname)
			}
			if len(productidlist) > 0 {
				sqlstr1 += fmt.Sprintf(" and t1.id in (%s) ", strings.Join(productidlist, ","))
			}
			sqlstr1 += " group by t1.id"
			row := tx.SQL(sqlstr1, args1...)
			defer row.Close()
			count, err := row.Query().Count()
			if err != nil {
				log.Println("GetAveragePrice-err2: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}
			recordcount = count
			defer row.Close()
		}
		defer productrow.Close()
	}

	// productidlist := []string{"0"}
	for _, data := range queryData {
		productidlist = append(productidlist, strconv.FormatInt(data["productid"].(int64), 10))
	}

	type areaid struct {
		areaid   int64
		name     string
		parentid int64
		layer    int64
		sort     int64
	}

	headlist := make([]map[string]interface{}, 0)
	if len(productidlist) > 0 {
		// sqlstr := `select t1.id,t1.name,t1.parent_id as parentid ,t1.layer,t1.ord  from canton t1
		// join (select DISTINCT(areaid) as areaid,t2.name from productarea t1 join canton t2 on t2.id = t1.areaid
		// where productid in (%s)) t2 on (t1.id = t2.areaid or t1.PARENT_ID = t2.areaid)`

		sqlstr := ` select t1.id,t1.name,t1.parent_id as parentid ,t1.layer,t1.ord  from canton t1 where layer >0  `
		if len(areaids) > 0 {
			sqlstr += fmt.Sprintf(" and PARENT_ID in (%s) or t1.id in(%s)", areaids, areaids)
		}

		arearow := tx.SQL(sqlstr)
		areaData, err := arearow.Query().List()
		if err != nil {
			log.Println("GetAveragePrice-err3: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}

		bigarea := make([]*areaid, 0)
		for _, area := range areaData {
			if area["layer"].(int64) == 1 {
				bigarea = append(bigarea, &areaid{
					areaid:   area["id"].(int64),
					name:     area["name"].(string),
					parentid: area["parentid"].(int64),
					layer:    area["layer"].(int64),
					sort:     area["ord"].(int64),
				})
			}
		}

		sort.Slice(bigarea, func(i, j int) bool {
			return bigarea[i].areaid < bigarea[j].areaid // 升序
		})
		sort.Slice(bigarea, func(i, j int) bool {
			return bigarea[i].sort < bigarea[j].sort // 升序
		})

		for _, big := range bigarea {
			head := make(map[string]interface{})
			head["column_id"] = big.areaid
			head["column_name"] = fmt.Sprintf("column_%d", big.areaid)
			head["column_title"] = big.name
			smallarea := make([]*areaid, 0)
			for _, area := range areaData {
				if area["parentid"].(int64) == big.areaid {
					smallarea = append(smallarea, &areaid{
						areaid:   area["id"].(int64),
						name:     area["name"].(string),
						parentid: area["parentid"].(int64),
						layer:    area["layer"].(int64),
						sort:     area["ord"].(int64),
					})
				}
			}
			sort.Slice(smallarea, func(i, j int) bool {
				return smallarea[i].areaid < smallarea[j].areaid // 升序
			})
			sort.Slice(smallarea, func(i, j int) bool {
				return smallarea[i].sort < smallarea[j].sort // 升序
			})
			smallhead := make([]map[string]interface{}, 0)
			for _, small := range smallarea {
				data1 := make(map[string]interface{})
				data1["column_id"] = small.areaid
				data1["column_name"] = fmt.Sprintf("column_%d", small.areaid)
				data1["column_title"] = small.name
				smallhead = append(smallhead, data1)
			}
			head["headlist"] = smallhead
			headlist = append(headlist, head)
		}

		type priceStruct struct {
			marketid  int64
			price     float64
			pricedate string
		}
		priceMap := make(map[int64][]priceStruct)
		if productid == 0 {

			productpricesql := `SELECT  productid,marketid as smallareaid , marketclass as bigareaid,avg(price) as price,pricedate FROM (
					SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate DESC limit 10000000000000000
					) AS t  where 1=1 `

			if starttime != "" && endtime != "" {
				productpricesql += fmt.Sprintf("  and pricedate BETWEEN '%s' and '%s' ", fmt.Sprintf("%s 00:00:00", starttime), fmt.Sprintf("%s 23:59:59", endtime))
			}
			if len(areaids) > 0 {
				productpricesql += fmt.Sprintf(" and t.marketclass in (%s)", areaids)
			}

			productpricesql += " group by productid, marketid  "
			if offset != 0 || pagecount != 0 {
				productpricesql += fmt.Sprintf("  limit %d,%d", (offset-1)*pagecount, pagecount)
			}
			productpricerow := tx.SQL(fmt.Sprintf(productpricesql, strings.Join(productidlist, ",")))
			defer productpricerow.Close()
			productpriceData, err := productpricerow.Query().List()
			if err != nil {
				log.Println("GetAveragePrice-err4: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}

			for _, productprice := range productpriceData {
				price, err := strconv.ParseFloat(productprice["price"].(string), 64)
				if err != nil {
					log.Println("GetAveragePrice-err6: ", err)
					appG.Response(http.StatusInternalServerError, e.ERROR, nil)
					return
				}
				priceMap[productprice["productid"].(int64)] = append(priceMap[productprice["productid"].(int64)], priceStruct{
					marketid: productprice["smallareaid"].(int64),
					price:    utils.Round(price, 2),
				})
			}

			for _, query := range queryData {
				for _, area := range areaData {
					if area["layer"].(int64) == 2 {
						query[fmt.Sprintf("column_%v", area["id"])] = 0
					}
				}
				query["Edit"] = false
				query["NoEdit"] = true
				query["isCreate"] = false
				query["isUpdate"] = true
				query["isUpdateSave"] = false
			}

			for _, query := range queryData {
				for _, price := range priceMap[query["productid"].(int64)] {
					query[fmt.Sprintf("column_%d", price.marketid)] = price.price
				}
			}
			data["list"] = queryData
		} else {
			timesql := `SELECT  date_format(pricedate, '%%Y-%%c-%%d') as pricedate FROM (
				SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate desc limit 10000000000000000
				) AS t  where 1=1  `

			if len(areaids) > 0 {
				timesql += fmt.Sprintf(" and t.marketclass in (%s)", areaids)
			}
			timesql += " group by date_format(pricedate, '%%Y-%%c-%%d')  ORDER BY pricedate desc"
			count, err := tx.SQL(fmt.Sprintf(timesql, strings.Join(productidlist, ","))).Query().Count()
			if err != nil {
				log.Println("GetAveragePrice-err4: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}
			recordcount = int(count)
			dates := []string{}

			if offset != 0 || pagecount != 0 {
				timesql += fmt.Sprintf("  limit %d,%d", (offset-1)*pagecount, pagecount)
			}
			timeData, err := tx.SQL(fmt.Sprintf(timesql, strings.Join(productidlist, ","))).Query().List()
			if err != nil {
				log.Println("GetAveragePrice-err5: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}

			productpricesql := `SELECT  productid,marketid as smallareaid , marketclass as bigareaid,avg(price) as price,date_format(pricedate, '%%Y-%%c-%%d') as pricedate FROM (
				SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate DESC limit 10000000000000000
				) AS t  where 1=1 `

			if len(areaids) > 0 {
				productpricesql += fmt.Sprintf(" and t.marketclass in (%s)", areaids)
			}
			if len(timeData) > 0 {
				starttime = fmt.Sprintf("%s 00:00:00", timeData[len(timeData)-1]["pricedate"].(string))
				endtime = fmt.Sprintf("%s 23:59:59", timeData[0]["pricedate"].(string))
				productpricesql += fmt.Sprintf("  and pricedate BETWEEN '%s' and '%s' ", fmt.Sprintf("%s 00:00:00", starttime), fmt.Sprintf("%s 23:59:59", endtime))

			}

			productpricesql += " group by marketid,date_format(pricedate, '%%Y-%%c-%%d')   "

			productpricerow := tx.SQL(fmt.Sprintf(productpricesql, strings.Join(productidlist, ",")))
			defer productpricerow.Close()
			productpriceData, err := productpricerow.Query().List()
			if err != nil {
				log.Println("GetAveragePrice-err6: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}

			for _, productprice := range productpriceData {
				dates = append(dates, productprice["pricedate"].(string))
				price, err := strconv.ParseFloat(productprice["price"].(string), 64)
				if err != nil {
					log.Println("GetAveragePrice-err7: ", err)
					appG.Response(http.StatusInternalServerError, e.ERROR, nil)
					return
				}
				priceMap[productprice["productid"].(int64)] = append(priceMap[productprice["productid"].(int64)], priceStruct{
					marketid:  productprice["smallareaid"].(int64),
					price:     utils.Round(price, 2),
					pricedate: productprice["pricedate"].(string),
				})
			}
			datelist := utils.RemoveRepeatedElement(dates)
			sort.Sort(sort.Reverse(sort.StringSlice(datelist)))
			productmap := make([]map[string]interface{}, 0)
			for _, date := range datelist {
				product := make(map[string]interface{}, 0)
				product["productid"] = productid
				for _, query := range queryData {
					if query["productid"].(int64) == productid {
						product["productclass"] = query["productclass"]
						product["productname"] = query["productname"]
						product["unit"] = query["unit"]
						product["specifications"] = query["specifications"]
						break
					}
				}
				for _, area := range areaData {
					if area["layer"].(int64) == 2 {
						product[fmt.Sprintf("column_%v", area["id"])] = 0
					}
				}
				product["Edit"] = false
				product["NoEdit"] = true
				product["isCreate"] = false
				product["isUpdate"] = true
				product["isUpdateSave"] = false
				product["pricedate"] = date
				for _, price := range priceMap[product["productid"].(int64)] {
					if date == price.pricedate {
						product[fmt.Sprintf("column_%d", price.marketid)] = price.price
					}
				}
				productmap = append(productmap, product)
			}

			data["list"] = productmap
		}
	}
	data["headlist"] = headlist
	data["total"] = recordcount
	data["code"] = 0
	data["message"] = "ok"
	if pagecount > 0 {
		data["pages"] = math.Ceil(float64(recordcount) / float64(pagecount))
		data["pageNum"] = math.Ceil(float64(offset+1) / float64(pagecount))
	}
	data["pageSize"] = pagecount

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

//查看平均价格图标信息
func GetQuotationChart(c *gin.Context) {
	// areaids := getroleareaid(c.GetHeader("token"))

	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	parammaps := fsion.Get("parammaps")
	productid := parammaps.Get("productid").ValueInt64()
	areaids := parammaps.Get("areaids").ValueStr()
	tx := restful.Engine.NewSession()
	defer tx.Close()

	areasql := `select name from  canton t1   join productarea t2 on t2.productid = ? and t1.PARENT_ID = t2.areaid where layer = 2 `
	if len(areaids) > 0 {
		areasql += fmt.Sprintf(" and t1.id in (%s)", areaids)
	}
	areamap, err := tx.SQL(areasql, productid).Query().List()
	if err != nil {
		log.Println("GetQuotationChart-err0: ", err)
		appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		return
	}

	sqlstr := `select  avg(t1.price) as price , date_format(t1.pricedate, '%Y-%c-%d') as date ,t2.name  from  productprice t1
	join canton t2 on t2.id = t1.marketid  where t1.productid = ?   `

	if len(areaids) > 0 {
		sqlstr += fmt.Sprintf(" and t1.marketid in (%s)", areaids)
	}

	sqlstr += " group by  date,name order by date  desc"
	priceData, err := tx.SQL(sqlstr, productid).Query().List()
	if err != nil {
		log.Println("GetQuotationChart-err1: ", err)
		appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		return
	}

	datelist := []string{}
	daylist := []interface{}{}
	for _, price := range priceData {
		datelist = append(datelist, price["date"].(string))
	}

	days := utils.RemoveRepeatedElement(datelist)
	sort.Sort(sort.Reverse(sort.StringSlice(days)))

	for _, day := range days {
		daylist = append(daylist, day)
	}

	var list [][]interface{}

	namelist := []interface{}{}
	for _, area := range areamap {
		namelist = append(namelist, area["name"].(string))
	}

	list = append(list, namelist)
	if len(daylist) == 0 {
		now := time.Now()
		for i := 0; i < 7; i++ {
			daylist = append(daylist, now.AddDate(0, 0, -i).Format("2006-01-02"))
		}
	}
	list = append(list, daylist)
	for _, name := range namelist {
		pricelist := []interface{}{}
		for _, day := range daylist {
			exist := false
			for _, pricemap := range priceData {
				if pricemap["name"].(string) == name.(string) && day.(string) == pricemap["date"].(string) {
					price, err := strconv.ParseFloat(pricemap["price"].(string), 64)
					if err != nil {
						log.Println("GetAveragePrice-err6: ", err)
						appG.Response(http.StatusInternalServerError, e.ERROR, nil)
						return
					}
					pricelist = append(pricelist, utils.Round(price, 2))
					exist = true
				}
			}
			if !exist {
				pricelist = append(pricelist, 0)
			}
		}
		if len(pricelist) == 0 {
			for i := 0; i < 7; i++ {
				pricelist = append(pricelist, 0)
			}
		} else {
			list = append(list, pricelist)
		}
	}
	appG.Response(http.StatusOK, e.SUCCESS, list)
}

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

	tx := restful.Engine.NewSession()
	defer tx.Close()
	productids := []string{}
	for _, product := range params {
		productids = append(productids, product.ValueStr())
		exist, err := tx.SQL("select id from attention where userid = ? and productid = ? ", userid, product.ValueInt64()).Exist()
		if err != nil {
			log.Println("AttentionProduce-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
		if !exist {
			_, err := tx.SQL("insert into attention(userid,productid)values(?,?)", userid, product.ValueInt64()).Execute()
			if err != nil {
				log.Println("AttentionProduce-err2: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}
		}
	}
	if len(productids) == 0 {
		productids = append(productids, "0")
	}
	_, err := tx.SQL(fmt.Sprintf("delete from attention where userid = ? and productid not in(%s)", strings.Join(productids, ",")), userid).Execute()
	if err != nil {
		log.Println("AttentionProduce-err3: ", err)
		appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		return
	}
	appG.Response(http.StatusOK, e.SUCCESS, true)
}

func GetAveragePriceExcel(c *gin.Context) {
	areaids := getroleareaid(c.GetHeader("token"))
	user, _ := util.ParseToken(c.GetHeader("token"))
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	offset := fsion.Get("offset").ValueDefaultInt(0)
	pagecount := fsion.Get("pagecount").ValueDefaultInt(0)
	parammaps := fsion.Get("parammaps")
	productclass := parammaps.Get("productclassid").ValueStr()
	productid := parammaps.Get("productid").ValueInt64()
	starttime := parammaps.Get("starttime").ValueStr()
	endtime := parammaps.Get("endtime").ValueStr()
	productname := parammaps.Get("productname").ValueStr()
	userid := parammaps.Get("userid").ValueStr()
	username := parammaps.Get("empname").ValueStr()

	// var productidlist []string
	// if productid != 0 {
	// 	productidlist = append(productidlist, fmt.Sprintf("%d", productid))
	// }
	// data := make(map[string]interface{})
	// var err error
	// queryData := make([]map[string]interface{}, 0)

	var productidlist []string
	if productid != 0 {
		productidlist = append(productidlist, fmt.Sprintf("%d", productid))
	}
	data := make(map[string]interface{})
	var err error
	queryData := make([]map[string]interface{}, 0)
	// var recordcount int

	tx := restful.Engine.NewSession()
	defer tx.Close()
	if userid != "" {
		productidmap, err := tx.SQL("select Productid as productid from attention where userid = ?", userid).Query().List()
		if err != nil {
			log.Println("GetAveragePrice-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
		for _, product := range productidmap {
			productidlist = append(productidlist, fmt.Sprintf("%v", product["productid"]))
		}
		if len(productidlist) == 0 {
			appG.Response(http.StatusOK, e.SUCCESS, data)
			return
		}
	}
	{
		// sqlstr := `select  t3.classname,t1.id as productid,t1.productname,t1.productclass,ifnull(t1.spec,"") as 	specifications ,ifnull(t2.unitname,"") as unit from productinfo  t1
		// join prounit  t2 on t2.id = t1.prounit join productclass t3 on t3.id = t1.productclass   where 1=1  and t1.enable = 1 `

		sqlstr := `select if(t5.code= "",0,1) as coedisnull,ifnull(t5.code,"")as productcode ,t3.classname,t1.id as productid,t1.productname,t1.productclass,ifnull(t1.spec,"") as 	specifications ,ifnull(t2.unitname,"") as unit from productinfo  t1  
		left join prounit  t2 on t2.id = t1.prounit   join productclass t3 on t3.id = t1.productclass join productarea t4 on t1.id = t4.Productid  
		`
		if len(areaids) > 0 {
			sqlstr += fmt.Sprintf(" and t4.Areaid in (%s)", areaids)
		}

		sqlstr += " left join productusercode t5  on t5.userid = ? and t5.productid = t1.id "
		var args []interface{}
		args = append(args, user.Username)
		sqlstr += ` where 1=1 and t1.enable = 1 `

		// var args []interface{}
		if len(productclass) > 0 {
			sqlstr += fmt.Sprintf(" and t1.productclass in (%s)", productclass)
		}
		if len(productname) > 0 {
			sqlstr += " and t1.productname like CONCAT('%',?,'%')"
			args = append(args, productname)
		}
		// if productid != 0 {
		// 	sqlstr += " and t1.id in (?) "
		// 	args = append(args, strings.Join(productidlist, ","))
		// }
		if len(productidlist) > 0 {
			sqlstr += fmt.Sprintf(" and t1.id in (%s) ", strings.Join(productidlist, ","))
		}
		sqlstr += " group by t1.id order by coedisnull desc, t5.code +0,t3.ordernum,t3.id,t1.ordernum,t1.id"
		// if pagecount > 0 && productid == 0 {
		// 	sqlstr += "  limit ?,?"
		// 	args = append(args, (offset-1)*pagecount, pagecount)
		// }

		productrow := tx.SQL(sqlstr, args...)
		defer productrow.Close()
		queryData, err = productrow.Query().List()
		if err != nil {
			log.Println("GetAveragePrice-err1: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}
		// if productid == 0 {
		// 	var args1 []interface{}
		// 	sqlstr1 := `select t1.id from productinfo  t1
		// join prounit  t2 on t2.id = t1.prounit  where 1=1  and t1.enable = 1 `

		// 	if len(productclass) > 0 {
		// 		sqlstr1 += fmt.Sprintf(" and t1.productclass in (%s) ", productclass)
		// 	}

		// 	if len(productname) > 0 {
		// 		sqlstr1 += " and t1.productname like CONCAT('%',?,'%')"
		// 		args1 = append(args1, productname)
		// 	}
		// 	if len(productidlist) > 0 {
		// 		sqlstr1 += fmt.Sprintf(" and t1.id in (%s) ", strings.Join(productidlist, ","))
		// 	}
		// 	row := tx.SQL(sqlstr1, args1...)
		// 	defer row.Close()
		// 	count, err := row.Query().Count()
		// 	if err != nil {
		// 		log.Println("GetAveragePrice-err2: ", err)
		// 		appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		// 		return
		// 	}
		// 	recordcount = count
		// 	defer row.Close()
		// }
		defer productrow.Close()
	}

	// productidlist := []string{"0"}
	for _, data := range queryData {
		productidlist = append(productidlist, strconv.FormatInt(data["productid"].(int64), 10))
	}

	type areaid struct {
		areaid   int64
		name     string
		parentid int64
		layer    int64
		sort     int64
	}

	headlist := make([]map[string]interface{}, 0)
	if len(productidlist) > 0 {
		// sqlstr := `select t1.id,t1.name,t1.parent_id as parentid ,t1.layer,t1.ord  from canton t1
		// join (select DISTINCT(areaid) as areaid,t2.name from productarea t1 join canton t2 on t2.id = t1.areaid
		// where productid in (%s)) t2 on (t1.id = t2.areaid or t1.PARENT_ID = t2.areaid)`

		sqlstr := ` select t1.id,t1.name,t1.parent_id as parentid ,t1.layer,t1.ord  from canton t1 where layer >0  `
		if len(areaids) > 0 {
			sqlstr += fmt.Sprintf(" and PARENT_ID in (%s) or id in (%s)", areaids, areaids)
		}
		arearow := tx.SQL(sqlstr)

		areaData, err := arearow.Query().List()
		if err != nil {
			log.Println("GetAveragePrice-err3: ", err)
			appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			return
		}

		bigarea := make([]*areaid, 0)
		for _, area := range areaData {
			if area["layer"].(int64) == 1 {
				bigarea = append(bigarea, &areaid{
					areaid:   area["id"].(int64),
					name:     area["name"].(string),
					parentid: area["parentid"].(int64),
					layer:    area["layer"].(int64),
					sort:     area["ord"].(int64),
				})
			}
		}

		sort.Slice(bigarea, func(i, j int) bool {
			return bigarea[i].areaid < bigarea[j].areaid // 升序
		})
		sort.Slice(bigarea, func(i, j int) bool {
			return bigarea[i].sort < bigarea[j].sort // 升序
		})

		for _, big := range bigarea {
			head := make(map[string]interface{})
			head["column_id"] = big.areaid
			head["column_name"] = fmt.Sprintf("column_%d", big.areaid)
			head["column_title"] = big.name
			smallarea := make([]*areaid, 0)
			for _, area := range areaData {
				if area["parentid"].(int64) == big.areaid {
					smallarea = append(smallarea, &areaid{
						areaid:   area["id"].(int64),
						name:     area["name"].(string),
						parentid: area["parentid"].(int64),
						layer:    area["layer"].(int64),
						sort:     area["ord"].(int64),
					})
				}
			}
			sort.Slice(smallarea, func(i, j int) bool {
				return smallarea[i].areaid < smallarea[j].areaid // 升序
			})
			sort.Slice(smallarea, func(i, j int) bool {
				return smallarea[i].sort < smallarea[j].sort // 升序
			})
			smallhead := make([]map[string]interface{}, 0)
			for _, small := range smallarea {
				data1 := make(map[string]interface{})
				data1["column_id"] = small.areaid
				data1["column_name"] = fmt.Sprintf("column_%d", small.areaid)
				data1["column_title"] = small.name
				smallhead = append(smallhead, data1)
			}
			head["headlist"] = smallhead
			headlist = append(headlist, head)
		}

		type priceStruct struct {
			marketid  int64
			price     float64
			pricedate string
		}
		priceMap := make(map[int64][]priceStruct)
		if productid == 0 {

			productpricesql := `SELECT  productid,marketid as smallareaid , marketclass as bigareaid,avg(price) as price,pricedate FROM (
					SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate DESC limit 10000000000000000
					) AS t  where 1=1 `

			if starttime != "" && endtime != "" {
				productpricesql += fmt.Sprintf("  and pricedate BETWEEN '%s' and '%s' ", fmt.Sprintf("%s 00:00:00", starttime), fmt.Sprintf("%s 23:59:59", endtime))
			}
			if len(areaids) > 0 {
				productpricesql += fmt.Sprintf(" and t.marketclass in (%s)", areaids)
			}

			productpricesql += " group by productid, marketid  "
			if offset != 0 || pagecount != 0 {
				productpricesql += fmt.Sprintf("  limit %d,%d", (offset-1)*pagecount, pagecount)
			}
			productpricerow := tx.SQL(fmt.Sprintf(productpricesql, strings.Join(productidlist, ",")))
			defer productpricerow.Close()
			productpriceData, err := productpricerow.Query().List()
			if err != nil {
				log.Println("GetAveragePrice-err4: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}

			for _, productprice := range productpriceData {
				price, err := strconv.ParseFloat(productprice["price"].(string), 64)
				if err != nil {
					log.Println("GetAveragePrice-err6: ", err)
					appG.Response(http.StatusInternalServerError, e.ERROR, nil)
					return
				}
				priceMap[productprice["productid"].(int64)] = append(priceMap[productprice["productid"].(int64)], priceStruct{
					marketid: productprice["smallareaid"].(int64),
					price:    utils.Round(price, 2),
				})
			}

			for _, query := range queryData {
				for _, area := range areaData {
					if area["layer"].(int64) == 2 {
						query[fmt.Sprintf("column_%v", area["id"])] = 0
					}
				}
				query["Edit"] = false
				query["NoEdit"] = true
				query["isCreate"] = false
				query["isUpdate"] = true
				query["isUpdateSave"] = false
			}

			for _, query := range queryData {
				for _, price := range priceMap[query["productid"].(int64)] {
					query[fmt.Sprintf("column_%d", price.marketid)] = price.price
				}
			}
			data["list"] = queryData
		} else {
			timesql := `SELECT  date_format(pricedate, '%%Y-%%c-%%d') as pricedate FROM (
				SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate desc limit 10000000000000000
				) AS t  where 1=1   `
			if len(areaids) > 0 {
				timesql += fmt.Sprintf(" and t.marketclass in (%s)", areaids)
			}
			timesql += "group by date_format(pricedate, '%%Y-%%c-%%d')  ORDER BY pricedate desc"
			// count, err := tx.SQL(fmt.Sprintf(timesql, strings.Join(productidlist, ","))).Query().Count()
			// if err != nil {
			// 	log.Println("GetAveragePrice-err4: ", err)
			// 	appG.Response(http.StatusInternalServerError, e.ERROR, nil)
			// 	return
			// }
			// recordcount = int(count)
			dates := []string{}

			if offset != 0 || pagecount != 0 {
				timesql += fmt.Sprintf("  limit %d,%d", (offset-1)*pagecount, pagecount)
			}
			timeData, err := tx.SQL(fmt.Sprintf(timesql, strings.Join(productidlist, ","))).Query().List()
			if err != nil {
				log.Println("GetAveragePrice-err5: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}

			productpricesql := `SELECT  productid,marketid as smallareaid , marketclass as bigareaid,avg(price) as price,date_format(pricedate, '%%Y-%%c-%%d') as pricedate FROM (
				SELECT * FROM productprice WHERE productid in (%s) ORDER BY pricedate DESC limit 10000000000000000
				) AS t  where 1=1 `

			if len(areaids) > 0 {
				productpricesql += fmt.Sprintf(" and t.marketclass in (%s)", areaids)
			}
			if len(timeData) > 0 {
				starttime = fmt.Sprintf("%s 00:00:00", timeData[len(timeData)-1]["pricedate"].(string))
				endtime = fmt.Sprintf("%s 23:59:59", timeData[0]["pricedate"].(string))
				productpricesql += fmt.Sprintf("  and pricedate BETWEEN '%s' and '%s' ", fmt.Sprintf("%s 00:00:00", starttime), fmt.Sprintf("%s 23:59:59", endtime))

			}

			productpricesql += " group by marketid,date_format(pricedate, '%%Y-%%c-%%d')   "
			productpricerow := tx.SQL(fmt.Sprintf(productpricesql, strings.Join(productidlist, ",")))
			defer productpricerow.Close()
			productpriceData, err := productpricerow.Query().List()
			if err != nil {
				log.Println("GetAveragePrice-err6: ", err)
				appG.Response(http.StatusInternalServerError, e.ERROR, nil)
				return
			}

			for _, productprice := range productpriceData {
				dates = append(dates, productprice["pricedate"].(string))
				price, err := strconv.ParseFloat(productprice["price"].(string), 64)
				if err != nil {
					log.Println("GetAveragePrice-err7: ", err)
					appG.Response(http.StatusInternalServerError, e.ERROR, nil)
					return
				}
				priceMap[productprice["productid"].(int64)] = append(priceMap[productprice["productid"].(int64)], priceStruct{
					marketid:  productprice["smallareaid"].(int64),
					price:     utils.Round(price, 2),
					pricedate: productprice["pricedate"].(string),
				})
			}
			datelist := utils.RemoveRepeatedElement(dates)
			sort.Sort(sort.Reverse(sort.StringSlice(datelist)))

			productmap := make([]map[string]interface{}, 0)
			for _, date := range datelist {
				product := make(map[string]interface{}, 0)
				product["productid"] = productid
				for _, query := range queryData {
					if query["productid"].(int64) == productid {
						product["productclass"] = query["productclass"]
						product["productname"] = query["productname"]
						product["unit"] = query["unit"]
						product["specifications"] = query["specifications"]
						product["classname"] = query["classname"]
						break
					}
				}
				for _, area := range areaData {
					if area["layer"].(int64) == 2 {
						product[fmt.Sprintf("column_%v", area["id"])] = 0
					}
				}
				product["Edit"] = false
				product["NoEdit"] = true
				product["isCreate"] = false
				product["isUpdate"] = true
				product["isUpdateSave"] = false
				product["pricedate"] = date
				for _, price := range priceMap[product["productid"].(int64)] {
					if date == price.pricedate {
						product[fmt.Sprintf("column_%d", price.marketid)] = price.price
					}
				}
				productmap = append(productmap, product)
			}

			data["list"] = productmap
		}
	}
	var file *xlsx.File
	var sheet *xlsx.Sheet
	var row *xlsx.Row
	var cell *xlsx.Cell
	style := xlsx.NewStyle()
	style.ApplyBorder = true
	border := *xlsx.NewBorder("thin", "thin", "thin", "thin")
	style.Border = border

	style.Font.Size = 15
	style.Alignment = xlsx.Alignment{
		Horizontal:  "center",
		Vertical:    "center",
		WrapText:    true,
		ShrinkToFit: true,
	}
	style.ApplyBorder = true

	file = xlsx.NewFile()
	sheet, _ = file.AddSheet("Sheet1")
	row = sheet.AddRow()

	cell = row.AddCell()
	classnum := make(map[string]int64)
	datamap := make(map[string][]interface{})
	classlist := []string{}

	for _, query := range data["list"].([]map[string]interface{}) {
		exist := false
		for _, class := range classlist {
			if query["classname"].(string) == class {
				exist = true
			}
		}
		if !exist {
			classlist = append(classlist, query["classname"].(string))
		}
		classnum[query["classname"].(string)]++
		datamap[query["classname"].(string)] = append(datamap[query["classname"].(string)], query)
	}
	var datatitle []string
	for _, data := range headlist {
		headlist := data["headlist"].([]map[string]interface{})
		for _, head := range headlist {
			datatitle = append(datatitle, head["column_name"].(string))
		}
	}

	exceltitle := "品种: "
	for _, v := range classlist {
		exceltitle += v + ","
	}

	exceltitle = exceltitle[:len(exceltitle)-1]
	exceltitle = fmt.Sprintf("%s  用户: %s", exceltitle, username)
	cell.Value = exceltitle
	cell.Merge(len(datatitle)+6, 2)
	cell.SetStyle(style)

	num := len(datatitle) + 6
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}

	row = sheet.AddRow()
	cell = row.AddCell()
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}
	row = sheet.AddRow()
	cell = row.AddCell()

	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}

	style = xlsx.NewStyle()
	style.Border = border
	style.ApplyBorder = true
	style.Alignment = xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		// WrapText:    true,
		// ShrinkToFit: true,
	}
	style.Font.Size = 13
	row = sheet.AddRow()
	cell = row.AddCell()
	cell.Value = "分类"
	cell.Merge(0, 1)
	cell.SetStyle(style)
	cell = row.AddCell()
	cell.Value = "序号"
	cell.SetStyle(style)
	cell.Merge(0, 1)
	cell = row.AddCell()
	cell.Value = "产品ID"
	cell.SetStyle(style)
	cell.Merge(0, 1)
	cell = row.AddCell()
	cell.Value = "品名"
	cell.SetStyle(style)
	cell.Merge(0, 1)
	cell = row.AddCell()
	cell.Value = "编码"
	cell.SetStyle(style)
	cell.Merge(0, 1)
	cell = row.AddCell()
	cell.Value = "规格"
	cell.SetStyle(style)
	cell.Merge(0, 1)
	cell = row.AddCell()
	cell.Value = "单位"
	cell.SetStyle(style)
	cell.Merge(0, 1)

	if data["list"] == nil {
		_ = file.Write(c.Writer)
		return
	}
	v := 0
	for _, data := range headlist {
		for i := 0; i < v; i++ {
			cell = row.AddCell()
			cell.SetStyle(style)
			cell.Value = ""
		}
		cell = row.AddCell()
		cell.Value = data["column_title"].(string)
		cell.SetStyle(style)
		head := data["headlist"].([]map[string]interface{})
		hcells := len(head) - 1
		cell.Merge(hcells, 0)
		v = hcells

	}
	for i := 0; i < v; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
		cell.Value = ""
	}

	row = sheet.AddRow()
	for i := 0; i < 7; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
		cell.Value = ""
	}

	// var datatitle []string
	for _, data := range headlist {
		headlist := data["headlist"].([]map[string]interface{})
		for _, head := range headlist {
			cell = row.AddCell()
			cell.SetStyle(style)
			cell.Value = head["column_title"].(string)
			// datatitle = append(datatitle, head["column_name"].(string))
		}
	}
	style = xlsx.NewStyle()
	style.ApplyBorder = true
	style.Alignment = xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		// WrapText:    true,
		// ShrinkToFit: true,
	}
	style.Border = border
	style.Font.Size = 10
	i := 0
	for _, v := range classlist {
		style = xlsx.NewStyle()
		style.ApplyBorder = true
		style.Border = border
		style.Alignment = xlsx.Alignment{
			Horizontal: "center",
			Vertical:   "top",
			// WrapText:    true,
			// ShrinkToFit: true,
		}
		style.Font.Size = 10
		row = sheet.AddRow()
		cell = row.AddCell()
		cell.SetStyle(style)
		cell.Value = v
		cell.Merge(0, int(classnum[v])-1)
		n := len(datamap[v]) - 1
		style = xlsx.NewStyle()
		style.ApplyBorder = true
		style.Border = border
		style.Alignment = xlsx.Alignment{
			Horizontal: "center",
			Vertical:   "center",
			// WrapText:    true,
			// ShrinkToFit: true,
		}
		style.Font.Size = 10
		for m, data := range datamap[v] {
			avgdata := data.(map[string]interface{})
			i++
			cell = row.AddCell()
			cell.SetStyle(style)
			cell.SetValue(i)

			cell = row.AddCell()
			cell.SetStyle(style)
			cell.SetValue(avgdata["productid"])

			cell = row.AddCell()
			cell.SetStyle(style)
			cell.SetValue(avgdata["productname"])

			cell = row.AddCell()
			cell.SetStyle(style)
			cell.SetValue(avgdata["productcode"])

			cell = row.AddCell()
			cell.SetStyle(style)
			cell.SetValue(avgdata["specifications"])

			cell = row.AddCell()
			cell.SetStyle(style)
			cell.SetValue(avgdata["unit"])
			for _, title := range datatitle {
				var value float64
				if _, ok := avgdata[title].(float64); ok {
					value = avgdata[title].(float64)
				}
				cell = row.AddCell()
				cell.SetStyle(style)
				// value := avgdata[title].(float64)
				cell.SetValue(value)
			}

			if n != m {
				row = sheet.AddRow()
				cell.SetStyle(style)
				cell = row.AddCell()
			}
		}
	}

	style = xlsx.NewStyle()
	style.ApplyBorder = true
	style.Border = border
	style.Alignment = xlsx.Alignment{}
	style.Font.Size = 10

	row = sheet.AddRow()
	cell = row.AddCell()
	cell.SetStyle(style)
	cell.Merge(len(datatitle)+6, 1)
	cell.Value = "备注：1、本网站市场价格为市场现金交易价，不含增值税专用发票。配送价为配送到酒店的价格(含运输费，发票，15天帐期)。"
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}
	row = sheet.AddRow()
	cell = row.AddCell()
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}
	row = sheet.AddRow()
	cell = row.AddCell()
	cell.SetStyle(style)
	cell.Merge(len(datatitle)+6, 1)
	cell.Value = " 2、本网站蔬菜类价格主要是市场上大量采购及供货商拿货时间段(凌晨4:00-6:00)的价格。"
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}
	row = sheet.AddRow()
	cell = row.AddCell()
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}
	row = sheet.AddRow()
	cell = row.AddCell()
	cell.SetStyle(style)
	cell.Merge(len(datatitle)+6, 1)
	cell.Value = " 3、会员企业可根据市场价格上浮一定比例给供应商定价，部分加工品种可根据加工程度另调上浮比例来定价。"
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}
	row = sheet.AddRow()
	cell = row.AddCell()
	cell.SetStyle(style)
	for i := 0; i < num; i++ {
		cell = row.AddCell()
		cell.SetStyle(style)
	}

	c.Header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
	c.Header("Content-Disposition", "attachment; filename="+"Workbook.xlsx")
	c.Header("Content-Transfer-Encoding", "binary")

	_ = file.Write(c.Writer)
	// appG.Response(http.StatusOK, e.SUCCESS, nil)
}

func UpdateRoleArea(c *gin.Context) {
	appG := app.Gin{C: c}
	dataByte, _ := ioutil.ReadAll(c.Request.Body)
	fsion := gofasion.NewFasion(string(dataByte))
	params := fsion.Get("params").Array()
	roleid := fsion.Get("id").ValueStr()

	arealist := []string{}
	for _, arg := range params {
		arealist = append(arealist, arg.ValueStr())
	}
	tx := restful.Engine.NewSession()
	defer tx.Close()

	_, err := tx.SQL("update role set areaid = ? where id = ?", strings.Join(arealist, ","), roleid).Execute()
	if err != nil {
		log.Println("UpdateRoleArea-err3: ", err)
		appG.Response(http.StatusInternalServerError, e.ERROR, nil)
		return
	}
	appG.Response(http.StatusOK, e.SUCCESS, true)
}

func getroleareaid(token string) string {
	user, _ := util.ParseToken(token)
	tx := restful.Engine.NewSession()
	defer tx.Close()
	sqlstr := `select areaid from user t1 
join role t3 on t3.id = t1.roleid  
where t1.username = ?`

	data, err := tx.SQL(sqlstr, user.Username).Query().List()
	if err != nil {
		log.Println("getroleareaid-err3: ", err)
		return ""
	}
	if _, ok := data[0]["areaid"]; ok {
		return data[0]["areaid"].(string)
	}
	return ""
}

type areaStruct struct {
	AreaID int64  `xorm:"id"`
	Name   string `xorm:"name"`
}

func GetAreaInfo(c *gin.Context) {
	appG := app.Gin{C: c}
	areaids := getroleareaid(c.GetHeader("token"))
	arealist := make([]*areaStruct, 0)
	tx := restful.Engine.NewSession()

	err := tx.SQL(fmt.Sprintf(`select id,name  from canton where PARENT_ID in (%s)`, areaids)).Find(&arealist)
	if err != nil {
		log.Println("GetAreaInfo-err: ", err)
		appG.Response(http.StatusOK, e.ERROR, nil)
		return
	}
	data := make(map[string]interface{})
	data["list"] = arealist
	data["total"] = len(arealist)
	data["code"] = 0
	appG.Response(http.StatusOK, e.SUCCESS, data)
}