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

package home

import (
	"context"
	"dashoo.cn/micro/app/model/home"
	contractService "dashoo.cn/micro/app/service/contract"
	"dashoo.cn/opms_libary/micro_srv"
	"database/sql"
	"fmt"
	"github.com/gogf/gf/database/gdb"
	"github.com/gogf/gf/os/gtime"
	"github.com/gogf/gf/util/gconv"
)

// getPersonalContractReportData 获取个人数据
// dataType：collection为回款；其他为合同
func getPersonalContractReportData(ctx context.Context, dataType string, params *map[string]interface{}) (*home.ReportData, error) {
	var reportData home.ReportData
	targetMap := make(map[int]gdb.Record, 0)
	realMap := make(map[int][]*gdb.Record, 0)
	targetField := "sales_target"
	realField := "contract_amount"
	year := gtime.Now().Format("Y")
	if params != nil && (*params)["year"] != nil {
		year = gconv.String((*params)["year"])
	}
	// 统计字段
	if dataType == "COLLECTION" {
		targetField = "collection_target"
		realField = "collection_amount"
	}

	srv, err := contractService.NewCtrContractService(ctx)
	if err != nil {
		return nil, err
	}
	// 获取用户信息
	userInfo, err := micro_srv.GetUserInfo(ctx)
	if err != nil {
		return nil, fmt.Errorf("获取用户信息异常：%s", err.Error())
	}

	targets, err := srv.Dao.DB.Model("rpt_sales_target").Where("sale_user_id", userInfo.Id).Order("monthly ASC").FindAll()
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}
	for index, target := range targets {
		targetMap[target["monthly"].Int()] = targets[index]
	}
	// 统计12个月份的数据
	// 统计目标值
	for index := 1; index <= 12; index++ {
		reportData.XData = append(reportData.XData, fmt.Sprintf("%v月", index))
		if target, ok := targetMap[index]; ok {
			reportData.YDataTarget = append(reportData.YDataTarget, target[targetField].Float64())
		} else {
			reportData.YDataTarget = append(reportData.YDataTarget, 0)
		}
	}
	// 统计合同值
	contractModel := srv.Dao.DB.Model("ctr_contract").Where("ctr_contract.incharge_id", userInfo.Id)
	if dataType != "COLLECTION" {
		contracts, err := contractModel.Where("ctr_contract.contract_start_time LIKE ?", year+"-%").Order("ctr_contract.contract_start_time ASC").FindAll()
		if err != nil && err != sql.ErrNoRows {
			return nil, err
		}
		for index, contract := range contracts {
			realMap[contract["contract_start_time"].GTime().Month()] = append(realMap[contract["contract_start_time"].GTime().Month()], &contracts[index])
		}
	} else {
		// 回款数据统计
		collections, err := contractModel.InnerJoin("ctr_contract_collection", "ctr_contract.id=ctr_contract_collection.contract_id").Where("ctr_contract_collection.appro_status", "20").Where("ctr_contract_collection.collection_datetime LIKE ?", year+"-%").Order("ctr_contract_collection.collection_datetime ASC").Fields("ctr_contract_collection.*").FindAll()
		if err != nil && err != sql.ErrNoRows {
			return nil, err
		}
		for index, collection := range collections {
			realMap[collection["collection_datetime"].GTime().Month()] = append(realMap[collection["collection_datetime"].GTime().Month()], &collections[index])
		}
	}
	// 统计12个月份的数据
	// 统计实际值
	for index := 1; index <= 12; index++ {
		if realData, ok := realMap[index]; ok {
			realAmount := float64(0)
			for _, data := range realData {
				realAmount += (*data)[realField].Float64()
			}
			reportData.YDataReal = append(reportData.YDataReal, realAmount)
		} else {
			reportData.YDataReal = append(reportData.YDataReal, 0)
		}
		if reportData.YDataTarget[index-1] == 0 {
			reportData.PercentData = append(reportData.PercentData, 0)
		} else {
			reportData.PercentData = append(reportData.PercentData, reportData.YDataReal[index-1]*100/reportData.YDataTarget[index-1])
		}
	}

	return &reportData, nil
}

// getCompanyContractReportData 获取总部数据
// dataType：collection为回款；其他为合同
func getCompanyContractReportData(ctx context.Context, dataType string, params *map[string]interface{}) (*home.ReportData, error) {
	var reportData home.ReportData
	realMap := make(map[int]gdb.Record, 0)
	targetField := "sales_target"
	realField := "contract_amount"
	dateWhere1 := "" // 查询条件
	dateWhere2 := "" // 查询条件
	dateWhere3 := ""
	date := gtime.Now()
	if params != nil && (*params)["date"] != nil {
		date = gconv.GTime((*params)["date"])
	}
	dateWhere1 = fmt.Sprintf("ctr_contract.contract_start_time LIKE '%v'", date.Format("Y")+"-%")
	dateWhere2 = fmt.Sprintf("ctr_contract_collection.collection_datetime LIKE '%v'", date.Format("Y")+"-%")
	if params != nil && (*params)["searchType"] != nil {
		searchType := gconv.String((*params)["searchType"])
		if searchType == "month" {
			dateWhere1 = fmt.Sprintf("ctr_contract.contract_start_time LIKE '%v'", date.Format("Y-m")+"-%")
			dateWhere2 = fmt.Sprintf("ctr_contract_collection.collection_datetime LIKE '%v'", date.Format("Y-m")+"-%")
			dateWhere3 = fmt.Sprintf("monthly=%v", date.Month())
		} else if searchType == "quarter" {
			dateWhere1 = getQuarterWhere(date, "ctr_contract.contract_start_time")
			dateWhere2 = getQuarterWhere(date, "ctr_contract_collection.collection_datetime")
			dateWhere3 = getQuarterMonthWhere(date, "monthly")
		}
	}
	// 统计字段
	if dataType == "COLLECTION" {
		targetField = "collection_target"
		realField = "collection_amount"
	}

	srv, err := contractService.NewCtrContractService(ctx)
	if err != nil {
		return nil, err
	}

	targets, err := srv.Dao.DB.Model("rpt_sales_target").Where(dateWhere3).Order("sale_user_id ASC").Group("sale_user_id").Fields(fmt.Sprintf("sale_user_id, sale_user_name, SUM(%v) %v", targetField, targetField)).FindAll()
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}
	// 统计合同值
	contractModel := srv.Dao.DB.Model("ctr_contract").Group("ctr_contract.incharge_id").Order("ctr_contract.incharge_id ASC")
	if dataType != "COLLECTION" {
		contracts, err := contractModel.Where(dateWhere1).Fields("ctr_contract.incharge_id, ctr_contract.incharge_name, SUM(ctr_contract.contract_amount) contract_amount").FindAll()
		if err != nil && err != sql.ErrNoRows {
			return nil, err
		}
		for index, contract := range contracts {
			realMap[contract["incharge_id"].Int()] = contracts[index]
		}
	} else {
		// 回款数据统计
		collections, err := contractModel.InnerJoin("ctr_contract_collection", "ctr_contract.id=ctr_contract_collection.contract_id").Where("ctr_contract_collection.appro_status", "20").Where(dateWhere2).Fields("ctr_contract.incharge_id, ctr_contract.incharge_name, SUM(ctr_contract_collection.collection_amount) collection_amount").FindAll()
		if err != nil && err != sql.ErrNoRows {
			return nil, err
		}
		for index, collection := range collections {
			realMap[collection["incharge_id"].Int()] = collections[index]
		}
	}

	// 统计目标值和实际值
	for index, target := range targets {
		reportData.XData = append(reportData.XData, target["sale_user_name"].String())
		reportData.YDataTarget = append(reportData.YDataTarget, target[targetField].Float64())
		if realData, ok := realMap[target["sale_user_id"].Int()]; ok {
			reportData.YDataReal = append(reportData.YDataReal, realData[realField].Float64())
		} else {
			reportData.YDataReal = append(reportData.YDataReal, 0)
		}
		if reportData.YDataTarget[index] == 0 {
			reportData.PercentData = append(reportData.PercentData, 0)
		} else {
			reportData.PercentData = append(reportData.PercentData, reportData.YDataReal[index]*100/reportData.YDataTarget[index])
		}
	}

	return &reportData, nil
}

func getQuarterWhere(date *gtime.Time, field string) string {
	if date.Month() >= 1 && date.Month() <= 3 {
		return fmt.Sprintf("%v >= '%v' AND %v < '%v'", field, date.Format("Y-")+"01-01 00:00:00", field, date.Format("Y-")+"04-01 00:00:00")
	} else if date.Month() >= 4 && date.Month() <= 6 {
		return fmt.Sprintf("%v >= '%v' AND %v < '%v'", field, date.Format("Y-")+"04-01 00:00:00", field, date.Format("Y-")+"07-01 00:00:00")
	} else if date.Month() >= 7 && date.Month() <= 9 {
		return fmt.Sprintf("%v >= '%v' AND %v < '%v'", field, date.Format("Y-")+"07-01 00:00:00", field, date.Format("Y-")+"10-01 00:00:00")
	} else if date.Month() >= 10 && date.Month() <= 12 {
		return fmt.Sprintf("%v >= '%v' AND %v <= '%v'", field, date.Format("Y-")+"10-01 00:00:00", field, date.Format("Y-")+"12-31 23:59:59")
	}
	return ""
}

func getQuarterMonthWhere(date *gtime.Time, field string) string {
	if date.Month() >= 1 && date.Month() <= 3 {
		return fmt.Sprintf("%v >= %v AND %v < %v", field, 1, field, 4)
	} else if date.Month() >= 4 && date.Month() <= 6 {
		return fmt.Sprintf("%v >= %v AND %v < %v", field, 4, field, 7)
	} else if date.Month() >= 7 && date.Month() <= 9 {
		return fmt.Sprintf("%v >= %v AND %v < %v", field, 7, field, 10)
	} else if date.Month() >= 10 && date.Month() <= 12 {
		return fmt.Sprintf("%v >= %v AND %v <= %v", field, 10, field, 12)
	}
	return ""
}