eta_api/services/data/excel/custom_analysis.go

package excel

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

// AddCustomAnalysisTable 添加自定义分析表格
func AddCustomAnalysisTable(excelName, content, excelImage string, excelClassifyId int, sysUser *system.Admin) (excelInfo *data_manage.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 := data_manage.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 := data_manage.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 = &data_manage.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)
	splitNum := 2000 // 基础分割单元格数

	// 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
		}
		addSheetItem := excel.AddExcelSheetParams{
			ExcelSheetId: 0,
			ExcelInfoId:  0,
			SheetName:    sheetName,
			Sort:         k,
		}

		lenCellData := len(sheetInfo.Celldata)

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

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

			startRow := i * splitNum
			endRow := (i + 1) * splitNum
			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,
				Data:         string(tmpDataByte),
				ModifyTime:   time.Now(),
				CreateTime:   time.Now(),
			})
		}
		addSheetItem.DataList = sheetDataList

		addSheetList = append(addSheetList, addSheetItem)
	}

	err = data_manage.AddExcelInfoAndSheet(excelInfo, addSheetList)

	return
}

func SaveCustomAnalysisTable(excelInfo *data_manage.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 := data_manage.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 := data_manage.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.ModifyTime = time.Now()

	addSheetList := make([]excel.AddExcelSheetParams, 0)
	splitNum := 2000 // 基础分割单元格数

	// 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
		}
		addSheetItem := excel.AddExcelSheetParams{
			ExcelSheetId: 0,
			ExcelInfoId:  0,
			SheetName:    sheetName,
			Sort:         k,
		}

		lenCellData := len(sheetInfo.Celldata)

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

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

			startRow := i * splitNum
			endRow := (i + 1) * splitNum
			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,
				Data:         string(tmpDataByte),
				ModifyTime:   time.Now(),
				CreateTime:   time.Now(),
			})
		}
		addSheetItem.DataList = sheetDataList

		addSheetList = append(addSheetList, addSheetItem)
	}

	err = data_manage.AddExcelInfoAndSheet(excelInfo, 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"`
	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         []struct {
		R int         `json:"r"`
		C int         `json:"c"`
		V interface{} `json:"v,omitempty"`
	} `json:"celldata"`
	CalcChain []interface{} `json:"calcChain"`
	//DataVerification struct {
	//} `json:"dataVerification"`
	//Hyperlink struct {
	//} `json:"hyperlink"`
	//Hide int `json:"hide"`
}