opms_backend/opms_parent/app/service/contract/ctr_contract_sale_target.go

package service

import (
	"bytes"
	"context"
	dao "dashoo.cn/micro/app/dao/contract"
	model "dashoo.cn/micro/app/model/contract"
	"dashoo.cn/micro/app/service/partner"
	"dashoo.cn/opms_libary/micro_srv"
	"dashoo.cn/opms_libary/myerrors"
	"dashoo.cn/opms_libary/request"
	"fmt"
	"github.com/gogf/gf/frame/g"
	"github.com/gogf/gf/os/gtime"
	"github.com/gogf/gf/util/gconv"
	"github.com/gogf/gf/util/gvalid"
	"github.com/xuri/excelize/v2"
)

type CtrContractSaleTargetService struct {
	Dao       *dao.CtrContractSaleTargetDao
	Tenant    string
	userInfo  request.UserInfo
	DataScope g.Map `json:"dataScope"`
}

func NewCtrContractSaleTargetService(ctx context.Context) (*CtrContractSaleTargetService, error) {
	tenant, err := micro_srv.GetTenant(ctx)
	if err != nil {

		return nil, myerrors.TipsError(fmt.Sprintf("获取租户码异常：%s", err.Error())) //fmt.Errorf("获取租户码异常：%s", err.Error())
	}
	// 获取用户信息
	userInfo, err := micro_srv.GetUserInfo(ctx)
	if err != nil {
		return nil, myerrors.TipsError(fmt.Sprintf("获取用户信息异常：%s", err.Error())) //fmt.Errorf("获取用户信息异常：%s", err.Error())
	}
	if err != nil {
		return nil, err
	}
	return &CtrContractSaleTargetService{
		Dao:       dao.NewCtrContractSaleTargetDao(tenant),
		Tenant:    tenant,
		userInfo:  userInfo,
		DataScope: userInfo.DataScope,
	}, nil
}

// List 获取列表
func (s CtrContractSaleTargetService) List(ctx context.Context, req *model.SaleTargetReq) (int, []*model.CtrContractSaleTarget, error) {
	saleTargetDao := s.Dao.As("ctr_contract_sale_target")
	if req.Year != "" {
		saleTargetDao = saleTargetDao.Where("year = ?", req.Year)
	}

	if req.SaleName != "" {
		likeStr := fmt.Sprintf("%%%s%%", req.SaleName)
		saleTargetDao = saleTargetDao.Where("sale_name like ?", likeStr)
	}

	total, err := saleTargetDao.Count()
	if err != nil {
		return 0, nil, err
	}
	if req.PageNum != 0 {
		saleTargetDao = saleTargetDao.Page(req.GetPage())
	}
	orderBy := "id ASC"
	if req.OrderBy != "" {
		orderBy = req.OrderBy
	}
	saleTargetDao = saleTargetDao.Order(orderBy)

	results, err := saleTargetDao.FindAll()
	return total, results, err
}

// Import excel数据导入
func (s CtrContractSaleTargetService) Import(ctx context.Context, req *model.ExcelImportReq) error {
	validErr := gvalid.CheckStruct(ctx, req, nil)
	if validErr != nil {
		return myerrors.TipsError(validErr.Current().Error())
	}

	// 下载文件
	buf, err := partner.DownFile(req.ExcelUrl)
	if err != nil {
		return myerrors.TipsError(fmt.Sprintf("下载 excel 异常 %s", err.Error()))
	}

	// 解析excel，构造销售目标数据
	saleTargets, minYear, err := s.parseExcel(buf)
	if err != nil {
		return myerrors.TipsError(fmt.Sprintf("解析 excel 异常 %s", err.Error()))
	}

	// 获取销售分成数据 人/年/月份
	shareMap := make(map[string]map[string]map[string]float64)
	var shares []*model.Share
	err = s.Dao.DB.Model("ctr_contract a").LeftJoin("ctr_contract_share b", "a.id=b.contract_id").Where(fmt.Sprintf("a.contract_sign_time >= '%v'", minYear)).Fields("a.contract_amount, a.incharge_id, a.incharge_name, a.contract_sign_time, b.id, b.sale_id, b.sale_name, b.share_amount").Scan(&shares)
	if err != nil {
		return err
	}
	// 统计 人/年度/月度金额
	for _, share := range shares {
		name := ""
		amount := float64(0)
		year := share.ContractSignTime.Format("Y")
		month := share.ContractSignTime.Format("m")
		if share.Id == 0 {
			name = share.InchargeName
			amount = share.ContractAmount
		} else {
			name = share.SaleName
			amount = share.ShareAmount
		}
		if _, ok := shareMap[name]; !ok {
			shareMap[name] = make(map[string]map[string]float64)
		}
		if _, ok := shareMap[name][year]; !ok {
			shareMap[name][year] = make(map[string]float64)
		}
		shareMap[name][year][month] += amount
	}

	// 获取用户信息
	saleMap := make(map[string]int)
	users, err := s.Dao.DB.Model("sys_user").FindAll()
	if err != nil {
		return err
	}
	for _, user := range users {
		saleMap[user["nick_name"].String()] = user["id"].Int()
	}

	// 获取历史销售目标信息
	targetMap := make(map[string]map[string]*model.CtrContractSaleTarget)
	targets, err := s.Dao.Where(fmt.Sprintf("year >= '%v'", minYear)).FindAll()
	if err != nil {
		return err
	}
	for index, target := range targets {
		if _, ok := targetMap[target.SaleName]; !ok {
			targetMap[target.SaleName] = make(map[string]*model.CtrContractSaleTarget)
		}
		targetMap[target.SaleName][target.Year] = targets[index]
	}

	for _, target := range saleTargets {
		updateTargetInfo(target, targetMap, saleMap, shareMap)
	}
	// 保存销售目标数据
	if len(saleTargets) > 0 {
		_, err = s.Dao.Save(saleTargets)
	}

	return err
}

// excel解构为数据
func (s CtrContractSaleTargetService) parseExcel(b []byte) ([]*model.CtrContractSaleTarget, string, error) {
	f, err := excelize.OpenReader(bytes.NewBuffer(b))
	if err != nil {
		return nil, "", err
	}
	sheet := "销售指标"
	rows, err := f.GetRows(sheet)
	if err != nil {
		return nil, "", err
	}
	minYear := "9999"

	// 序号	销售	年度	1月计划	1月达成	2月计划	2月达成	3月计划	3月达成	第一季度计划	第一季度达成	第一季度完成率	4月计划	4月达成	5月计划	5月达成	6月计划	6月达成	第二季度计划	第二季度达成	第二季度完成率	7月计划	7月达成	8月计划	8月达成	9月计划	9月达成	第三季度计划	第三季度达成	第三季度完成率	10月计划	10月达成	11月计划	11月达成	12月计划	12月达成	年度计划	年度达成	年度完成率
	var saleTargets []*model.CtrContractSaleTarget
	for _, row := range rows[1:] {
		temp := &model.CtrContractSaleTarget{
			SaleName:    row[1],
			Year:        row[2],
			Plan1:       gconv.Float64(row[3]),
			Plan2:       gconv.Float64(row[5]),
			Plan3:       gconv.Float64(row[7]),
			Plan4:       gconv.Float64(row[12]),
			Plan5:       gconv.Float64(row[14]),
			Plan6:       gconv.Float64(row[16]),
			Plan7:       gconv.Float64(row[21]),
			Plan8:       gconv.Float64(row[23]),
			Plan9:       gconv.Float64(row[25]),
			Plan10:      gconv.Float64(row[30]),
			Plan11:      gconv.Float64(row[32]),
			Plan12:      gconv.Float64(row[34]),
			Remark:      "",
			CreatedBy:   s.userInfo.Id,
			CreatedName: s.userInfo.NickName,
			CreatedTime: gtime.Now(),
			UpdatedBy:   s.userInfo.Id,
			UpdatedName: s.userInfo.NickName,
			UpdatedTime: gtime.Now(),
		}
		if temp.Year < minYear {
			minYear = temp.Year
		}
		temp.QuarterPlan1 = temp.Plan1 + temp.Plan2 + temp.Plan3
		temp.QuarterPlan2 = temp.Plan4 + temp.Plan5 + temp.Plan6
		temp.QuarterPlan3 = temp.Plan7 + temp.Plan8 + temp.Plan9
		temp.YearPlan = temp.QuarterPlan1 + temp.QuarterPlan2 + temp.QuarterPlan3 + temp.Plan10 + temp.Plan11 + temp.Plan12

		saleTargets = append(saleTargets, temp)
	}

	return saleTargets, minYear, nil
}

func updateTargetInfo(target *model.CtrContractSaleTarget, targetMap map[string]map[string]*model.CtrContractSaleTarget, saleMap map[string]int, shareMap map[string]map[string]map[string]float64) {
	// 有则更新
	if targetMap[target.SaleName] != nil && targetMap[target.SaleName][target.Year] != nil {
		target.Id = targetMap[target.SaleName][target.Year].Id
		target.CreatedBy = targetMap[target.SaleName][target.Year].CreatedBy
		target.CreatedName = targetMap[target.SaleName][target.Year].CreatedName
		target.CreatedTime = targetMap[target.SaleName][target.Year].CreatedTime
	} else {
		target.SaleId = saleMap[target.SaleName]
	}
	if shareMap[target.SaleName] != nil && shareMap[target.SaleName][target.Year] != nil {
		for month, amount := range shareMap[target.SaleName][target.Year] {
			switch month {
			case "01":
				target.Complete1 = amount
				break
			case "02":
				target.Complete2 = amount
				break
			case "03":
				target.Complete3 = amount
				break
			case "04":
				target.Complete4 = amount
				break
			case "05":
				target.Complete5 = amount
				break
			case "06":
				target.Complete6 = amount
				break
			case "07":
				target.Complete7 = amount
				break
			case "08":
				target.Complete8 = amount
				break
			case "09":
				target.Complete9 = amount
				break
			case "10":
				target.Complete10 = amount
				break
			case "11":
				target.Complete11 = amount
				break
			case "12":
				target.Complete12 = amount
				break
			}
		}
	}
	target.QuarterComplete1 = target.Complete1 + target.Complete2 + target.Complete3
	target.QuarterComplete2 = target.Complete4 + target.Complete5 + target.Complete6
	target.QuarterComplete3 = target.Complete7 + target.Complete8 + target.Complete9
	target.YearComplete = target.QuarterComplete1 + target.QuarterComplete2 + target.QuarterComplete3 + target.Complete10 + target.Complete11 + target.Complete12
	if target.QuarterPlan1 != 0 {
		target.QuarterCompleteRatio1 = target.QuarterComplete1 / target.QuarterPlan1 * 100
	}
	if target.QuarterPlan2 != 0 {
		target.QuarterCompleteRatio2 = target.QuarterComplete2 / target.QuarterPlan2 * 100
	}
	if target.QuarterPlan3 != 0 {
		target.QuarterCompleteRatio3 = target.QuarterComplete3 / target.QuarterPlan3 * 100
	}
	if target.YearPlan != 0 {
		target.YearCompleteRatio = target.YearComplete / target.YearPlan * 100
	}
}