datatag/CMPlatform/util/parsxlsx.go

package util

import (
	"app.yhyue.com/moapp/jybase/common"
	"app.yhyue.com/moapp/jybase/date"
	"app.yhyue.com/moapp/jybase/encrypt"
	"app.yhyue.com/moapp/jybase/log"
	"app.yhyue.com/moapp/jybase/mongodb"
	"fmt"
	"github.com/tealeg/xlsx"
	"go.mongodb.org/mongo-driver/bson"
	"go.uber.org/zap"
	"strings"
	"time"
)

const (
	Matchkey       = "s_matchkey"       //关键词
	Addkey         = "s_addkey"         //附加词
	Notkey         = "s_notkey"         //排除词
	Buyerclass     = "s_buyerclass"     //采购单位行业
	Topscopeclass  = "s_topscopeclass"  //行业-一级
	Subscopeclass  = "s_subscopeclass"  //行业-二级
	Globaladdkey   = "s_globaladdkey"   // 全局附加词
	Globalnotkey   = "s_globalnotkey"   //全局排除词
	Globalclearkey = "s_globalclearkey" //全局排除词

	Tagname = "s_name" //标签的关键词规则名称
)

var SES = encrypt.SimpleEncrypt{Key: "topJYBX2019"}

// 通用标签相关字段
var export_fields = []string{"s_userid", "s_name", "", "s_area", "s_city", "s_district", "s_toptype", "s_subtype", "i_starttime", "i_endtime", "s_budgetlimit", "s_bidamountlimit", "s_globaltopscopeclass", "s_globalsubscopeclass", "s_globalbuyerclass", "s_globaladdkey", "s_globaladdkeymatch", "s_globalnotkey", "s_globalnotkeymatch", "s_globalclearkey", "s_globalclearkeymatch", "s_existfields", "i_extfieldstype"}

// 关键词标签相关字段
var export_o_rules = []string{"s_matchkey", "s_keymatch", "s_addkey", "s_addkeymatch", "s_notkey", "s_notkeymatch", "s_buyerclass"}

// 定义值为数字的多个匹配方式map
var export_numfield = map[string]bool{
	"s_globaladdkeymatch":   true,
	"s_globalnotkeymatch":   true,
	"s_globalclearkeymatch": true,
	"s_keymatch":            true,
	"s_addkeymatch":         true,
	"s_notkeymatch":         true,
}

// 匹配方式map
var export_matchtype = map[string]interface{}{
	"1": "标题匹配",
	"2": "全文匹配",
	"3": "标的物匹配",
	"4": "附件匹配",
	"5": "项目名称匹配",
	"6": "采购单位匹配",
	"7": "中标单位匹配",
	"8": "采购单位匹配(正则)",
	"9": "中标单位匹配(正则)",
}

// 字段包类型
var export_extfieldstype = map[string]interface{}{
	"1": "标准字段包",
	"2": "高级字段包",
}

/**
 * 导入关键词表格
 */
func Parsxlsx(filebyte []byte) (map[string]interface{}, error) {
	rdata := make(map[string]interface{})
	tmps := make([]map[string]string, 0)
	file, err := xlsx.OpenBinary(filebyte)
	if err != nil {
		return rdata, err
	}
	gaddkeyArr := []string{}
	gnotkeyArr := []string{}
	clearkeyArr := []string{}
	for _, v := range file.Sheets {
		for ii, vv := range v.Rows {
			rule := make(map[string]string)
			if ii == 0 {
				//第一行跳过
				continue
			}
			for iii, vvv := range vv.Cells {
				gaddkeyArr, gnotkeyArr, clearkeyArr = writeMap(rule, iii, vvv.Value, gaddkeyArr, gnotkeyArr, clearkeyArr)
			}
			tmps = append(tmps, rule)
		}
	}
	rdata["o_rules"] = tmps
	if len(gaddkeyArr) >= 1 {
		rdata[Globaladdkey] = strings.Join(gaddkeyArr, ",")
	}
	if len(gnotkeyArr) >= 1 {
		rdata[Globalnotkey] = strings.Join(gnotkeyArr, ",")
	}
	if len(clearkeyArr) >= 1 {
		rdata[Globalclearkey] = strings.Join(clearkeyArr, ",")
	}
	return rdata, nil
}

/**
 * 导入关键词表格
 */
func Parsxlsx1(filebyte []byte) (map[string]interface{}, error) {
	rdata := make(map[string]interface{})
	tmps := make([]map[string]string, 0)
	file, err := xlsx.OpenBinary(filebyte)
	if err != nil {
		return rdata, err
	}
	for _, v := range file.Sheets {
		for ii, vv := range v.Rows {
			rule := make(map[string]string)
			if ii == 0 {
				//第一行跳过
				continue
			}
			for iii, vvv := range vv.Cells {
				writeMap1(rule, iii, vvv.Value)
			}
			tmps = append(tmps, rule)
		}
	}
	rdata["o_rules"] = tmps
	return rdata, nil
}

func writeMap(tmp map[string]string, i int, v string, addArr, notArr, clearkeyArr []string) ([]string, []string, []string) {
	switch i {
	case 0:
		if v != "" {
			//空格
			v = strings.TrimSpace(v)
			//中文逗号
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Matchkey] = v
		}
		break
	case 1:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Addkey] = v
		}
		break
	case 2:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Notkey] = v
		}
		break
	case 3:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Buyerclass] = v
		}
		break
	case 4:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Topscopeclass] = v
		}
		break
	case 5:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Subscopeclass] = v
		}
		break
	case 6:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			addArr = append(addArr, v)
		}
		break
	case 7:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			notArr = append(notArr, v)
		}
		break
	case 8:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			clearkeyArr = append(clearkeyArr, v)
		}
		break
	case 9:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp["s_group"] = v
		}
		break
	}
	return addArr, notArr, clearkeyArr
}

func writeMap1(tmp map[string]string, i int, v string) {
	switch i {
	case 0:
		if v != "" {
			//空格
			v = strings.TrimSpace(v)
			//中文逗号
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Matchkey] = v
		}
		break
	case 1:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Addkey] = v
		}
		break
	case 2:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Notkey] = v
		}
		break
	case 3:
		if v != "" {
			v = strings.TrimSpace(v)
			v = strings.ReplaceAll(v, "，", ",")
			tmp[Tagname] = v
		}

	}
}

var bzpath, gjpath, rpath string = "web/res/%s_数据_%s_%s.xlsx", "web/res/%s_数据_%s_%s.xlsx", "web/res/%s_规则_%s_%s.xlsx"

func ResponseXlsx_Data(id string) string {
	if id != "" {
		query := bson.M{
			"_id": mongodb.StringTOBsonId(id),
		}
		data, _ := Mgo.FindOne("cuserdepartrule", query)
		ruleName := (*data)["s_name"]
		if (*data) != nil && len(*data) > 0 {
			isStandard := true
			if common.Int64All((*data)["i_extfieldstype"]) == Advanced {
				isStandard = false
			}

			data, _ := Mgo.Find("tagsdata", bson.M{"s_dataid": common.ObjToString((*data)["s_dataid"])}, nil, nil, false, -1, -1)
			if (*data) == nil || len(*data) == 0 {
				return ""
			}
			xf, err := xlsx.OpenFile("web/res/fields.xlsx")
			if err != nil {
				log.Error("fields file not foud", zap.Error(err))
				return ""
			}
			if isStandard {
				sh := xf.Sheets[0]
				for i, v := range *data {
					row := sh.AddRow()
					row.AddCell().SetInt(i + 1)
					row.AddCell().SetValue(v["s_matchkey"])
					row.AddCell().SetValue(v["area"])
					row.AddCell().SetValue(v["city"])
					row.AddCell().SetValue(v["title"])
					row.AddCell().SetValue(v["subtype"])
					if v["publishtime"] != nil {
						row.AddCell().SetValue(time.Unix(common.Int64All(v["publishtime"]), 0).Format("2006-01-02"))
					} else {
						row.AddCell()
					}
					row.AddCell().SetValue(v["buyer"])
					row.AddCell().SetValue(v["winner"])
					if v["bidamount"] != nil {
						row.AddCell().SetFloat(common.Float64All(v["bidamount"]))
					} else {
						row.AddCell()
					}
					row.AddCell().SetValue(v["projectname"])
					row.AddCell().SetValue(v["detail"])
					row.AddCell().SetValue(v["s_jyhref"])
				}
				xf.Sheets = xf.Sheets[:1]
				xf.Sheets[0].Name = "详细数据"
				t := time.Now().Format("20060102")
				fname := fmt.Sprintf(bzpath, ruleName, t, common.GetRandom(4))
				err := xf.Save(fname)
				if err != nil {
					log.Error("", zap.Error(err))
					return ""
				}
				return fname
			} else {
				sh := xf.Sheets[1]
				for _, v := range *data {
					row := sh.AddRow()
					// row.AddCell().SetInt(i + 1)
					row.AddCell().SetValue(v["s_matchkey"])
					row.AddCell().SetValue(v["area"])
					row.AddCell().SetValue(v["city"])
					row.AddCell().SetValue(v["title"])
					row.AddCell().SetValue(v["subtype"])
					row.AddCell().SetValue(v["detail"])
					if v["publishtime"] != nil {
						row.AddCell().SetValue(time.Unix(common.Int64All(v["publishtime"]), 0).Format("2006-01-02"))
					} else {
						row.AddCell()
					}
					row.AddCell().SetValue(v["href"])
					row.AddCell().SetValue(v["s_jyhref"])
					row.AddCell().SetValue(v["projectname"])
					row.AddCell().SetValue(v["projectcode"])
					row.AddCell().SetValue(v["projectscope"])
					if v["budget"] != nil {
						row.AddCell().SetFloat(common.Float64All(v["budget"]))
					} else {
						row.AddCell()
					}
					if v["bidamount"] != nil {
						row.AddCell().SetFloat(common.Float64All(v["bidamount"]))
					} else {
						row.AddCell()
					}
					if v["bidopentime"] != nil {
						row.AddCell().SetValue(time.Unix(common.Int64All(v["bidopentime"]), 0).Format("2006-01-02"))
					} else {
						row.AddCell()
					}
					row.AddCell().SetValue(v["buyer"])
					row.AddCell().SetValue(v["buyerperson"])
					row.AddCell().SetValue(v["buyertel"])
					row.AddCell().SetValue(v["agency"])
					row.AddCell().SetValue(v["s_winner"])
					row.AddCell().SetValue(v["winnerperson"])
					row.AddCell().SetValue(v["winnertel"])
					row.AddCell().SetValue(v["legal_person"])
					row.AddCell().SetValue(v["company_phone"])
					row.AddCell().SetValue(v["company_email"])
					ids := SES.EncodeString(common.ObjToString(v["info_id"]))
					row.AddCell().SetValue(ids)
				}
				xf.Sheets = xf.Sheets[1:2]
				xf.Sheets[0].Name = "详细数据"
				t := time.Now().Format("20060102")
				fname := fmt.Sprintf(gjpath, ruleName, t, common.GetRandom(4))
				err := xf.Save(fname)
				if err != nil {
					log.Error("", zap.Error(err))
					return ""
				}
				return fname
			}
		}
	}
	return ""
}

func ResponseXlsx_Rule(id string) string {
	defer common.Catch()
	var data *map[string]interface{}
	data, _ = Mgo.FindById("cuserdepartrule", id, `{}`)
	if len(*data) == 0 {
		return ""
	}
	exact := 0
	xf, err := xlsx.OpenFile("web/res/export_rule.xlsx") //读取导出标签模板表
	if err != nil {
		log.Error("export_rule file not foud", zap.Error(err))
		return ""
	}
	sh := xf.Sheets[0]
	fields_len := len(export_fields)
	//
	for i, r := range sh.Rows {
		if i == fields_len {
			break
		}

		field := export_fields[i] //export_fields值的顺序与模板表一致
		if field == "i_extfieldstype" {
			r.AddCell().SetValue(export_extfieldstype[fmt.Sprint((*data)[field])])
			continue
		}
		if field == "s_userid" {
			userName, _ := Mgo.FindById("cuser", (*data)[field].(string), `{}`)
			if common.IntAll((*userName)["i_exact"]) == 1 {
				exact = 1
			}
			r.AddCell().SetValue((*userName)["s_name"])
			continue
		}
		field_val := ""
		if strings.HasPrefix(field, "s_") {
			field_val = common.ObjToString((*data)[field])
			if export_numfield[field] { //找出值为数字的匹配方式，转换成中文
				field_val = numValToWord(field_val)
			}
		} else if strings.HasPrefix(field, "i_") { //i_startime i_endtime
			t := common.Int64All((*data)[field])
			if t > 0 {
				field_val = date.FormatDateByInt64(&t, date.Date_Full_Layout)
			}
		}
		r.AddCell().SetValue(field_val)
	}
	o_rules := (*data)["o_rules"].([]interface{})
	for _, o_rule := range o_rules {
		new_row := sh.AddRow()
		o_tmp := o_rule.(map[string]interface{})
		for _, match := range export_o_rules {
			cell_val := common.ObjToString(o_tmp[match])
			if export_numfield[match] { //找出值为数字的匹配方式，转换成中文
				cell_val = numValToWord(cell_val)
			}
			new_row.AddCell().SetValue(cell_val)
		}
		if s_topscopeclass := o_tmp["s_topscopeclass"]; s_topscopeclass != "" {
			new_row.AddCell().SetValue(s_topscopeclass)
		} else if s_subscopeclass := o_tmp["s_subscopeclass"]; s_subscopeclass != "" {
			new_row.AddCell().SetValue(s_subscopeclass)
		}
		if exact == 1 {
			new_row.AddCell().SetValue(o_tmp["s_group"])
		}
	}
	ruleName := (*data)["s_name"]
	t := time.Now().Format("20060102")
	fname := fmt.Sprintf(rpath, ruleName, t, common.GetRandom(4))
	xf.Save(fname)
	return fname
}

// 匹配方式的数字值，转为对应的汉字
func numValToWord(numval string) (word string) {
	field_arr := strings.Split(numval, ",")
	for i, val := range field_arr {
		if i == 0 {
			word = common.ObjToString(export_matchtype[val])
			continue
		}
		word = word + "," + common.ObjToString(export_matchtype[val])
	}
	return
}

/**
 * 导入剑鱼批量修改数据
 */
func ParsJyData(filebyte []byte) ([]map[string]interface{}, error) {
	var jyData []map[string]interface{}
	var keyName []string
	file, err := xlsx.OpenBinary(filebyte)
	if err != nil {
		return jyData, err
	}

	for i, v := range file.Sheets[0].Rows {
		data := make(map[string]interface{})
		for ii, vv := range v.Cells {
			if i == 0 {
				keyName = append(keyName, vv.Value)
			} else {
				if vv.Value != "" {
					data[keyName[ii]] = vv.Value
				}
			}
		}
		if len(data) > 0 {
			jyData = append(jyData, data)
		}
	}

	return jyData, nil
}