eta_api/services/data/excel/custom_analysis.go

package excel

import (
	"encoding/json"
	"errors"
	"eta/eta_api/models/data_manage/excel"
	"eta/eta_api/models/data_manage/excel/request"
	"eta/eta_api/models/system"
	"eta/eta_api/utils"
	"strconv"
	"time"
)

var cellSplitNum = 10000 // 基础分割单元格数

// AddCustomAnalysisTable 添加自定义分析表格
func AddCustomAnalysisTable(excelName, content, excelImage string, excelClassifyId int, sysUser *system.Admin) (excelInfo *excel.ExcelInfo, err error, errMsg string, isSendEmail bool) {
	isSendEmail = true

	contentByte := []byte(content)

	var luckySheetList []LuckySheet

	err = json.Unmarshal(contentByte, &luckySheetList)
	if err != nil {
		return
	}

	// sheet内容为空
	if len(luckySheetList) <= 0 {
		errMsg = "sheet内容为空"
		err = errors.New(errMsg)
		isSendEmail = false
		return
	}

	excelClassify, err := excel.GetExcelClassifyById(excelClassifyId)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			errMsg = "分类不存在"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}
		errMsg = "获取分类信息失败"
		err = errors.New("获取分类信息失败，Err:" + err.Error())
		return
	}
	if excelClassify == nil {
		errMsg = "分类不存在"
		err = errors.New(errMsg)
		isSendEmail = false
		return
	}

	if excelClassify.Source != utils.CUSTOM_ANALYSIS_TABLE {
		errMsg = "当前分类不是自定义分析分类"
		err = errors.New("当前分类不是自定义分析分类")
		isSendEmail = false
		return
	}

	// 校验是否同名文件
	{
		var condition string
		var pars []interface{}
		condition += " AND excel_classify_id=? "
		pars = append(pars, excelClassifyId)

		condition += " AND excel_name=? "
		pars = append(pars, excelName)

		// 获取分类下是否存在该表格名称
		count, tmpErr := excel.GetExcelInfoCountByCondition(condition, pars)
		if tmpErr != nil {
			errMsg = "判断表格名称是否存在失败"
			err = errors.New("判断表格名称是否存在失败,Err:" + tmpErr.Error())
			return
		}
		if count > 0 {
			errMsg = "表格名称已存在，请重新填写表格名称"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}
	}

	timestamp := strconv.FormatInt(time.Now().UnixNano(), 10)
	// 表格
	excelInfo = &excel.ExcelInfo{
		//ExcelInfoId:     0,
		ExcelName: excelName,
		Source:    utils.CUSTOM_ANALYSIS_TABLE,
		//ExcelType:       req.ExcelType,
		UniqueCode:      utils.MD5(utils.EXCEL_DATA_PREFIX + "_" + timestamp),
		ExcelClassifyId: excelClassifyId,
		SysUserId:       sysUser.AdminId,
		SysUserRealName: sysUser.RealName,
		Content:         ``,
		ExcelImage:      excelImage,
		Sort:            0,
		IsDelete:        0,
		ModifyTime:      time.Now(),
		CreateTime:      time.Now(),
	}

	addSheetList := make([]excel.AddExcelSheetParams, 0)

	// sheet处理
	sheetNameMap := make(map[string]string)
	for k, sheetInfo := range luckySheetList {
		sheetName := utils.TrimLRStr(sheetInfo.Name)
		_, ok := sheetNameMap[sheetName]
		if ok {
			errMsg = "excel表中存在同名sheet"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}

		sheetConf, tmpErr := json.Marshal(sheetInfo.Config)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		// 计算公式
		sheetCalcChain, tmpErr := json.Marshal(sheetInfo.CalcChain)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		addSheetItem := excel.AddExcelSheetParams{
			ExcelSheetId: 0,
			ExcelInfoId:  0,
			SheetName:    sheetName,
			Sort:         k,
			Config:       string(sheetConf),
			CalcChain:    string(sheetCalcChain),
		}

		lenCellData := len(sheetInfo.CellData)

		splitLen := lenCellData / cellSplitNum
		residue := lenCellData % cellSplitNum
		if residue > 0 {
			splitLen += 1
		}

		sheetDataList := make([]*excel.ExcelSheetData, 0)
		for i := 0; i < splitLen; i++ {

			startRow := i * cellSplitNum
			endRow := (i + 1) * cellSplitNum
			if i == splitLen-1 && residue > 0 {
				endRow = lenCellData
			}

			tmpData := sheetInfo.CellData[startRow:endRow]
			tmpDataByte, tmpErr := json.Marshal(tmpData)
			if tmpErr != nil {
				errMsg = "保存失败"
				err = errors.New("保存失败：" + tmpErr.Error())
				return
			}
			sheetDataList = append(sheetDataList, &excel.ExcelSheetData{
				ExcelDataId:  0,
				ExcelInfoId:  0,
				ExcelSheetId: 0,
				Sort:         i + 1,
				Data:         string(tmpDataByte),
				ModifyTime:   time.Now(),
				CreateTime:   time.Now(),
			})
		}
		addSheetItem.DataList = sheetDataList

		addSheetList = append(addSheetList, addSheetItem)
	}

	err = excel.AddExcelInfoAndSheet(excelInfo, addSheetList)

	return
}

// SaveCustomAnalysisTable 编辑自定义分析表格
func SaveCustomAnalysisTable(excelInfo *excel.ExcelInfo, excelName, content, excelImage string, excelClassifyId int, sheetOpList []request.SheetOp) (err error, errMsg string, isSendEmail bool) {
	isSendEmail = true

	contentByte := []byte(content)

	var luckySheetList []LuckySheet

	err = json.Unmarshal(contentByte, &luckySheetList)
	if err != nil {
		return
	}

	// sheet内容为空
	if len(luckySheetList) <= 0 {
		errMsg = "sheet内容为空"
		err = errors.New(errMsg)
		isSendEmail = false
		return
	}

	// sheet内容为空
	//if len(sheetOpList) <= 0 {
	//	errMsg = "sheet操作为空"
	//	err = errors.New(errMsg)
	//	isSendEmail = false
	//	return
	//}

	excelClassify, err := excel.GetExcelClassifyById(excelClassifyId)
	if err != nil {
		if err.Error() == utils.ErrNoRow() {
			errMsg = "分类不存在"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}
		errMsg = "获取分类信息失败"
		err = errors.New("获取分类信息失败，Err:" + err.Error())
		return
	}
	if excelClassify == nil {
		errMsg = "分类不存在"
		err = errors.New(errMsg)
		isSendEmail = false
		return
	}

	if excelClassify.Source != utils.CUSTOM_ANALYSIS_TABLE {
		errMsg = "当前分类不是自定义分析分类"
		err = errors.New("当前分类不是自定义分析分类")
		isSendEmail = false
		return
	}

	// 校验是否同名文件
	{
		var condition string
		var pars []interface{}
		condition += " AND excel_classify_id=?  AND excel_info_id !=?  "
		pars = append(pars, excelClassifyId, excelInfo.ExcelInfoId)

		condition += " AND excel_name=? "
		pars = append(pars, excelName)

		// 获取分类下是否存在该表格名称
		count, tmpErr := excel.GetExcelInfoCountByCondition(condition, pars)
		if tmpErr != nil {
			errMsg = "判断表格名称是否存在失败"
			err = errors.New("判断表格名称是否存在失败,Err:" + tmpErr.Error())
			return
		}
		if count > 0 {
			errMsg = "表格名称已存在，请重新填写表格名称"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}
	}

	//// 查找当前excel的sheet列表
	//currSheetList, err := excel.GetAllSheetList(excelInfo.ExcelInfoId)
	//if err != nil {
	//	errMsg = "保存失败"
	//	err = errors.New("查找当前excel的sheet列表失败，Err:" + err.Error())
	//	return
	//}
	//currSheetMap := make(map[string]string)
	//for _, currSheet := range currSheetList {
	//	currSheetMap[currSheet.SheetName] = currSheet.SheetName
	//}
	//
	//for k, sheetOp := range sheetOpList {
	//	sheetName := utils.TrimLRStr(sheetOp.SheetName)
	//	switch sheetOp.OpType {
	//	case "add":
	//		// 新增
	//		_, ok := currSheetMap[sheetName]
	//		if ok {
	//			errMsg = "存在同名sheet:" + sheetName
	//			err = errors.New(errMsg)
	//			isSendEmail = false
	//			return
	//		}
	//	case "replace":
	//		// 替换
	//	case "append":
	//		// 追加
	//	default:
	//		errMsg = fmt.Sprint("第", k+1, "个sheet,错误的操作类型")
	//		err = errors.New(errMsg + "op:" + sheetOp.OpType)
	//		isSendEmail = false
	//		return
	//	}
	//}

	// 表格
	excelInfo.ExcelName = excelName
	excelInfo.ExcelClassifyId = excelClassifyId
	excelInfo.ExcelImage = excelImage
	excelInfo.ModifyTime = time.Now()
	updateExcelInfoParam := []string{"ExcelName", "ExcelClassifyId", "ExcelImage", "ModifyTime"}

	addSheetList := make([]excel.AddExcelSheetParams, 0)

	// sheet处理
	sheetNameMap := make(map[string]string)
	for k, sheetInfo := range luckySheetList {
		sheetName := utils.TrimLRStr(sheetInfo.Name)
		_, ok := sheetNameMap[sheetName]
		if ok {
			errMsg = "excel表中存在同名sheet"
			err = errors.New(errMsg)
			isSendEmail = false
			return
		}

		sheetConf, tmpErr := json.Marshal(sheetInfo.Config)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		// 计算公式
		sheetCalcChain, tmpErr := json.Marshal(sheetInfo.CalcChain)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		addSheetItem := excel.AddExcelSheetParams{
			ExcelSheetId: 0,
			ExcelInfoId:  excelInfo.ExcelInfoId,
			SheetName:    sheetName,
			Sort:         k,
			Config:       string(sheetConf),
			CalcChain:    string(sheetCalcChain),
		}

		lenCellData := len(sheetInfo.CellData)

		splitLen := lenCellData / cellSplitNum
		residue := lenCellData % cellSplitNum
		if residue > 0 {
			splitLen += 1
		}

		sheetDataList := make([]*excel.ExcelSheetData, 0)
		for i := 0; i < splitLen; i++ {

			startRow := i * cellSplitNum
			endRow := (i + 1) * cellSplitNum
			if i == splitLen-1 && residue > 0 {
				endRow = lenCellData
			}

			tmpData := sheetInfo.CellData[startRow:endRow]
			tmpDataByte, tmpErr := json.Marshal(tmpData)
			if tmpErr != nil {
				errMsg = "保存失败"
				err = errors.New("保存失败：" + tmpErr.Error())
				return
			}
			sheetDataList = append(sheetDataList, &excel.ExcelSheetData{
				ExcelDataId:  0,
				ExcelInfoId:  excelInfo.ExcelInfoId,
				ExcelSheetId: 0,
				Sort:         i + 1,
				Data:         string(tmpDataByte),
				ModifyTime:   time.Now(),
				CreateTime:   time.Now(),
			})
		}
		addSheetItem.DataList = sheetDataList

		addSheetList = append(addSheetList, addSheetItem)
	}

	err = excel.SaveExcelInfoAndSheet(excelInfo, updateExcelInfoParam, addSheetList)

	return
}

type LuckySheet struct {
	Name string `json:"name"`
	//Config struct {
	//	Columnlen struct {
	//		Num15 int `json:"15"`
	//		Num16 int `json:"16"`
	//		Num20 int `json:"20"`
	//		Num34 int `json:"34"`
	//		Num35 int `json:"35"`
	//	} `json:"columnlen"`
	//} `json:"config"`
	Config           interface{}
	Index            string               `json:"index"`
	Order            string               `json:"order"`
	ZoomRatio        int                  `json:"zoomRatio"`
	ShowGridLines    string               `json:"showGridLines"`
	DefaultColWidth  int                  `json:"defaultColWidth"`
	DefaultRowHeight int                  `json:"defaultRowHeight"`
	CellData         []LuckySheetCellData `json:"celldata"`
	CalcChain        []interface{}        `json:"calcChain"`
	//DataVerification struct {
	//} `json:"dataVerification"`
	//Hyperlink struct {
	//} `json:"hyperlink"`
	//Hide int `json:"hide"`
}

// LuckySheetCellData 单元格数据
type LuckySheetCellData struct {
	R int         `json:"r"`
	C int         `json:"c"`
	V interface{} `json:"v,omitempty"`
}