opms_backend/opms_parent/app/service/proj/report.go

package proj

import (
	"context"
	dao "dashoo.cn/micro/app/dao/proj"
	model "dashoo.cn/micro/app/model/proj"
	"dashoo.cn/micro/app/service"
	"dashoo.cn/opms_libary/micro_srv"
	"dashoo.cn/opms_libary/myerrors"
	"fmt"
	"github.com/gogf/gf/frame/g"
)

type BusinessReportService struct {
	Dao *dao.ProjBusinessDao
	*service.ContextService
}

func NewBusinessReportService(ctx context.Context) (svc *BusinessReportService, err error) {
	tenant, err := micro_srv.GetTenant(ctx)
	if err != nil {
		err = myerrors.TipsError(fmt.Sprintf("获取租户码异常：%s", err.Error()))
		return nil, err //fmt.Errorf("获取租户码异常：%s", err.Error())
	}

	svc = new(BusinessReportService)
	if svc.ContextService, err = svc.Init(ctx); err != nil {
		return nil, err
	}
	svc.Dao = dao.NewProjBusinessDao(tenant)
	return svc, nil
}

// QueryBusinessNum 业绩指标-当月新增项目
func (s *BusinessReportService) QueryBusinessNum(date string) (interface{}, error) {
	type BusinessNumData struct {
		model.ProjBusiness
		Total float64 `orm:"total"` // 合计
	}
	where := ""
	// 权限限制（销售工程师看自己的）
	if service.StringsContains(s.CxtUser.Roles, "SalesEngineer") {
		where = fmt.Sprintf("proj_business.sale_id='%v'", s.CxtUser.Id)
	}
	// 获取数据  （储备转C，项目来源为 400热线 10、展会信息获取 50）不记作新增数据
	// opn_type 项目升级
	var businessInfos []*BusinessNumData
	err := s.Dao.LeftJoin("(SELECT * FROM proj_business_dynamics WHERE opn_content LIKE '%\"origNboType\":\"50\"%' AND opn_type='40' GROUP BY proj_business_dynamics.bus_id) b", "proj_business.Id=b.bus_id").Where(fmt.Sprintf("proj_business.created_time LIKE '%v%%' AND proj_business.appro_status='30' AND proj_business.nbo_source<>'10' AND proj_business.nbo_source<>'50' AND proj_business.nbo_type<>'50' AND b.id IS NULL", date)).Where(where).Fields("proj_business.*,COUNT(1) total").Group("proj_business.sale_id, proj_business.product_line").Order("proj_business.sale_id ASC, proj_business.product_line ASC").Scan(&businessInfos)
	if err != nil {
		return nil, err
	}
	// 获取销售数据
	userList, err := service.GetUserListByRoleCode(s.Ctx, []string{"SalesEngineer"}, 1000)
	if err != nil {
		return nil, err
	}
	// 权限限制（销售工程师看自己的）
	if service.StringsContains(s.CxtUser.Roles, "SalesEngineer") {
		userList = []map[string]interface{}{
			{
				"Id":       s.CxtUser.Id,
				"NickName": s.CxtUser.NickName,
			},
		}
	}
	// BIOBANK CELLSOP LIMS+基因 其他
	header, data := make([]g.Map, 0), make([]g.Map, 0)
	header = append(header, g.Map{"prop": "userName", "label": "销售工程师"}, g.Map{"prop": "bio", "label": "BIOBANK"}, g.Map{"prop": "cell", "label": "CELLSOP"}, g.Map{"prop": "lims", "label": "LIMS+基因"}, g.Map{"prop": "other", "label": "其他"}, g.Map{"prop": "total", "label": "合计"})

	user2Business := make(map[int][]*BusinessNumData, 0)
	total := float64(0)
	for _, col := range businessInfos {
		if _, ok := user2Business[col.SaleId]; !ok {
			user2Business[col.SaleId] = make([]*BusinessNumData, 0)
		}
		user2Business[col.SaleId] = append(user2Business[col.SaleId], col)
		total += col.Total
	}
	// 统计数据
	for _, user := range userList {
		bio, cell, lims, other, tot := float64(0), float64(0), float64(0), float64(0), float64(0)
		if len(user2Business[user["Id"].(int)]) > 0 {
			for _, col := range user2Business[user["Id"].(int)] {
				tot += col.Total
				if col.ProductLine == "10" {
					bio += col.Total
				} else if col.ProductLine == "20" {
					cell += col.Total
				} else if col.ProductLine == "30" {
					lims += col.Total
				} else {
					other += col.Total
				}
			}
		}
		data = append(data, g.Map{
			"userName": user["NickName"],
			"bio":      fmt.Sprintf("%v", bio),
			"cell":     fmt.Sprintf("%v", cell),
			"lims":     fmt.Sprintf("%v", lims),
			"other":    fmt.Sprintf("%v", other),
			"total":    fmt.Sprintf("%v", tot),
		})
	}
	data = append(data, g.Map{
		"userName": "",
		"bio":      "",
		"cell":     "",
		"lims":     "",
		"other":    "",
		"total":    fmt.Sprintf("总计：%v个", total),
	})

	return g.Map{"header": header, "data": data}, nil
}

// QueryBusinessTransformNum 业绩指标-转化项目项目
func (s *BusinessReportService) QueryBusinessTransformNum(date string) (interface{}, error) {
	type BusinessNumData struct {
		model.ProjBusiness
		Total float64 `orm:"total"` // 合计
	}
	where := ""
	// 权限限制（销售工程师看自己的）
	if service.StringsContains(s.CxtUser.Roles, "SalesEngineer") {
		where = fmt.Sprintf(" AND proj_business.sale_id='%v'", s.CxtUser.Id)
	}
	// 获取数据  去掉储备升级而来的数据
	// opn_type 项目升级
	var businessInfos1 []*BusinessNumData
	var businessInfos2 []*BusinessNumData
	err := s.Dao.DB.Model(fmt.Sprintf("(SELECT proj_business.* FROM proj_business INNER JOIN proj_business_dynamics ON proj_business.id=proj_business_dynamics.bus_id WHERE proj_business_dynamics.created_time LIKE '%v%%' AND proj_business.nbo_type<>'50' AND proj_business.appro_status='30' AND proj_business_dynamics.opn_type='40' AND proj_business.deleted_time IS NULL AND proj_business_dynamics.deleted_time IS NULL %v) a", date, where)).LeftJoin("(SELECT * FROM proj_business_dynamics WHERE opn_content LIKE '%\"origNboType\":\"50\"%' AND opn_type='40' AND proj_business_dynamics.deleted_time IS NULL GROUP BY proj_business_dynamics.bus_id) b", "a.id=b.bus_id").Unscoped().Where("b.id IS NULL").Fields("a.*,COUNT(1) total").Group("a.sale_id, a.product_line").Order("a.sale_id ASC, a.product_line ASC").Scan(&businessInfos1)
	if err != nil {
		return nil, err
	}
	err = s.Dao.DB.Model(fmt.Sprintf("(SELECT proj_business.* FROM proj_business INNER JOIN ((SELECT ctr_contract.* FROM ctr_contract INNER JOIN (SELECT nbo_id,MIN(contract_sign_time) contract_sign_time FROM ctr_contract WHERE ctr_contract.appro_status='30' AND ctr_contract.deleted_time IS NULL GROUP BY nbo_id) t ON ctr_contract.nbo_id=t.nbo_id AND ctr_contract.contract_sign_time=t.contract_sign_time WHERE ctr_contract.appro_status='30' AND ctr_contract.deleted_time IS NULL GROUP BY ctr_contract.id) c) ON proj_business.id=c.nbo_id WHERE c.contract_sign_time LIKE '%v%%' AND proj_business.nbo_type<>'50' AND proj_business.appro_status='30' AND proj_business.deleted_time IS NULL %v GROUP BY proj_business.id) a", date, where)).LeftJoin("(SELECT * FROM proj_business_dynamics WHERE opn_content LIKE '%\"origNboType\":\"50\"%' AND opn_type='40' AND proj_business_dynamics.deleted_time IS NULL GROUP BY proj_business_dynamics.bus_id) b", "a.id=b.bus_id").Unscoped().Where("b.id IS NULL").Fields("a.*,COUNT(1) total").Group("a.sale_id, a.product_line").Order("a.sale_id ASC, a.product_line ASC").Scan(&businessInfos2)
	if err != nil {
		return nil, err
	}

	// 获取销售数据
	userList, err := service.GetUserListByRoleCode(s.Ctx, []string{"SalesEngineer"}, 1000)
	if err != nil {
		return nil, err
	}
	// 权限限制（销售工程师看自己的）
	if service.StringsContains(s.CxtUser.Roles, "SalesEngineer") {
		userList = []map[string]interface{}{
			{
				"Id":       s.CxtUser.Id,
				"NickName": s.CxtUser.NickName,
			},
		}
	}
	// BIOBANK CELLSOP LIMS+基因 其他
	header, data := make([]g.Map, 0), make([]g.Map, 0)
	header = append(header, g.Map{"prop": "userName", "label": "销售工程师"}, g.Map{"prop": "bio", "label": "BIOBANK"}, g.Map{"prop": "cell", "label": "CELLSOP"}, g.Map{"prop": "lims", "label": "LIMS+基因"}, g.Map{"prop": "other", "label": "其他"}, g.Map{"prop": "total", "label": "合计"})

	user2Business := make(map[int][]*BusinessNumData, 0)
	total := float64(0)
	for _, col := range businessInfos1 {
		if _, ok := user2Business[col.SaleId]; !ok {
			user2Business[col.SaleId] = make([]*BusinessNumData, 0)
		}
		user2Business[col.SaleId] = append(user2Business[col.SaleId], col)
		total += col.Total
	}
	for _, col := range businessInfos2 {
		if _, ok := user2Business[col.SaleId]; !ok {
			user2Business[col.SaleId] = make([]*BusinessNumData, 0)
		}
		user2Business[col.SaleId] = append(user2Business[col.SaleId], col)
		total += col.Total
	}
	// 统计数据
	for _, user := range userList {
		bio, cell, lims, other, tot := float64(0), float64(0), float64(0), float64(0), float64(0)
		if len(user2Business[user["Id"].(int)]) > 0 {
			for _, col := range user2Business[user["Id"].(int)] {
				tot += col.Total
				if col.ProductLine == "10" {
					bio += col.Total
				} else if col.ProductLine == "20" {
					cell += col.Total
				} else if col.ProductLine == "30" {
					lims += col.Total
				} else {
					other += col.Total
				}
			}
		}
		data = append(data, g.Map{
			"userName": user["NickName"],
			"bio":      fmt.Sprintf("%v", bio),
			"cell":     fmt.Sprintf("%v", cell),
			"lims":     fmt.Sprintf("%v", lims),
			"other":    fmt.Sprintf("%v", other),
			"total":    fmt.Sprintf("%v", tot),
		})
	}
	data = append(data, g.Map{
		"userName": "",
		"bio":      "",
		"cell":     "",
		"lims":     "",
		"other":    "",
		"total":    fmt.Sprintf("总计：%v个", total),
	})

	return g.Map{"header": header, "data": data}, nil
}