gd_crontab/impl/crontab/auto_export_business.go

// Copyright 2019  getensh.com. All rights reserved.
// Use of this source code is governed by  getensh.com.
package crontab

import (
	"gd_crontab/apis"
	"gd_crontab/errors"
	"fmt"
	"time"

	"gd_crontab/common.in/config"
	"gd_crontab/common.in/utils"
	"github.com/astaxie/beego/orm"
	"github.com/tealeg/xlsx"
	"go.uber.org/zap"
)

type TGdBusinessReport struct {
	Id               int64
	Date             string `json:"date"`
	ApiName          string `json:"api_name"`
	MerchantName     string
	Total            int    `json:"total"`
	Valid            int    `json:"valid"`
	DistinctCount    int    `json:"distinct_count" description:"去重调用量"`
	ApiId            int64  `json:"api_id"`
	MerchantId       int64  `json:"merchant_id"`
	Charge           int    `json:"charge"`
	UnvalidRedundant int    `json:"unvalid_redundant" description:"无效中的重复调用，即失败-失败里的去重调用"`
	ArgsFormatError  int    `json:"args_format_error"`
	ArgsUnsupport    int    `json:"args_unsupport"`
	ChargeReuse      int    `json:"charge_reuse"`
	NoRecord         int    `json:"no_record"`
	ProviderError    int    `json:"provider_error"`
	PlatformError    int    `json:"platform_error"`
	TimeoutConf      string `json:"timeout_conf"`
	TimeoutCount     int    `json:"timeout_count"`
}

// makeXlsxBusinessHeader 构建日报明细表头
func makeXlsxBusinessHeader(sheet *xlsx.Sheet) {
	row := sheet.AddRow()
	dateCell := row.AddCell()
	dateCell.Value = "日期"

	dateCell.VMerge = 1
	sheet.SetColWidth(0, 0, 20)

	merchantCell := row.AddCell()
	merchantCell.VMerge = 1
	sheet.SetColWidth(1, 1, 10)
	merchantCell.Value = "商户"

	apiCell := row.AddCell()
	apiCell.VMerge = 1
	sheet.SetColWidth(2, 2, 24)
	apiCell.Value = "api"

	totalInfoCell := row.AddCell()
	totalInfoCell.Value = "总调用详情"
	totalInfoCell.HMerge = 5
	for i := 0; i < 5; i++ {
		row.AddCell()
	}

	unvalidInfoCell := row.AddCell()
	unvalidInfoCell.Value = "无效调用分析"
	unvalidInfoCell.HMerge = 2
	for i := 0; i < 2; i++ {
		row.AddCell()
	}

	queryInfoCell := row.AddCell()
	queryInfoCell.Value = "查得分析"
	queryInfoCell.HMerge = 1
	row.AddCell()

	norecordInfoCell := row.AddCell()
	norecordInfoCell.Value = "查无及异常分析"
	norecordInfoCell.HMerge = 2
	for i := 0; i < 2; i++ {
		row.AddCell()
	}

	timeoutInfoCell := row.AddCell()
	timeoutInfoCell.Value = "时效性分析"
	timeoutInfoCell.HMerge = 2
	for i := 0; i < 2; i++ {
		row.AddCell()
	}

	row = sheet.AddRow()
	for i := 0; i < 3; i++ {
		row.AddCell()
	}
	totalCell := row.AddCell()
	totalCell.Value = "总调用量"

	distinctCell := row.AddCell()
	distinctCell.Value = "去重调用量"

	validCell := row.AddCell()
	validCell.Value = "有效调用量"

	validRateCell := row.AddCell()
	validRateCell.Value = "有效调用率"

	chargeCell := row.AddCell()
	chargeCell.Value = "计费量"

	chargeRateCell := row.AddCell()
	chargeRateCell.Value = "计费率"

	redundantCell := row.AddCell()
	redundantCell.Value = "重复调用"

	argsFormatCell := row.AddCell()
	argsFormatCell.Value = "入参格式错误"

	argsUnsupportCell := row.AddCell()
	argsUnsupportCell.Value = "不支持的查询参数"

	chargeReuseCell := row.AddCell()
	chargeReuseCell.Value = "计费复用量"

	chargeReuseRateCell := row.AddCell()
	chargeReuseRateCell.Value = "计费复用率"

	norecordCell := row.AddCell()
	norecordCell.Value = "接口查无"

	providerErrorCell := row.AddCell()
	providerErrorCell.Value = "数据源异常"

	systemErrorCell := row.AddCell()
	systemErrorCell.Value = "平台限制"

	timeoutConfCell := row.AddCell()
	timeoutConfCell.Value = "超时设置"

	timeoutCountCell := row.AddCell()
	timeoutCountCell.Value = "超时数量"

	timeoutRateCell := row.AddCell()
	timeoutRateCell.Value = "超时率"
}

func converDataToBusinessReport(info apis.LogQueryUserAcessBusinessExport) TGdBusinessReport {
	ret := TGdBusinessReport{}
	ret.TimeoutCount = info.TimeoutCount
	ret.TimeoutConf = info.TimeoutConf
	ret.PlatformError = info.PlatformError
	ret.ProviderError = info.ProviderError
	ret.NoRecord = info.NoRecord
	ret.ChargeReuse = info.ChargeReuse
	ret.ArgsUnsupport = info.ArgsUnsupport
	ret.ArgsFormatError = info.ArgsFormatError
	ret.UnvalidRedundant = info.UnvalidRedundant
	ret.Valid = info.Valid
	ret.Charge = info.Charge
	ret.DistinctCount = info.DistinctCount
	ret.Total = info.Total
	ret.ApiName = info.ApiName
	ret.Date = info.Date
	ret.ApiId = info.ApiId
	ret.MerchantId = info.MerchantId
	ret.MerchantName = info.MerchantName
	return ret
}

func getBusinessDataFromReport(start, end string) ([]apis.LogQueryUserAcessBusinessExport, error) {
	sql := fmt.Sprintf("select date, merchant_id, api_id, merchant_name, api_name, timeout_conf,"+
		"sum(total) as total,"+
		"sum(distinct_count) as distinct_count,"+
		"sum(valid) as valid,"+
		"sum(charge) as charge,"+
		"sum(unvalid_redundant) as unvalid_redundant,"+
		"sum(args_format_error) as args_format_error,"+
		"sum(args_unsupport) as args_unsupport,"+
		"sum(charge_reuse) as charge_reuse,"+
		"sum(no_record) as no_record,"+
		"sum(provider_error) as provider_error,"+
		"sum(platform_error) as platform_error,"+
		"sum(timeout_count) as timeout_count"+
		" from t_gd_business_report where date >= '%s' and date <= '%s' group by merchant_id, api_id ", start, end)
	o := orm.NewOrm()
	ret := []apis.LogQueryUserAcessBusinessExport{}
	_, err := o.Raw(sql).QueryRows(&ret)
	if err != nil && err != orm.ErrNoRows {
		return nil, errors.DataBaseError
	}
	for i, v := range ret {
		ret[i].Date = fmt.Sprintf("%s至%s", start, end)
		ret[i].ValidRate = computeBusinessPercent(float64(v.Valid), float64(v.Total))
		ret[i].ChargeRate = computeBusinessPercent(float64(v.Charge), float64(v.Valid))
		ret[i].ChargeReuseRate = computeBusinessPercent(float64(v.ChargeReuse), float64(v.Charge))
		ret[i].TimeoutRate = computeBusinessPercent(float64(v.TimeoutCount), float64(v.Total))
	}

	return ret, nil
}

func parseBusinessDetail(sheet *xlsx.Sheet, infos []apis.LogQueryUserAcessBusinessExport, reports []TGdBusinessReport, merchant string) []TGdBusinessReport {
	for _, v := range infos {
		row := sheet.AddRow()
		dateCell := row.AddCell()
		dateCell.Value = v.Date

		merchantCell := row.AddCell()
		merchantCell.Value = merchant

		apiCell := row.AddCell()
		apiCell.Value = v.ApiName

		totalCell := row.AddCell()
		totalCell.SetInt(v.Total)

		distinctCell := row.AddCell()
		distinctCell.SetInt(v.DistinctCount)

		validCell := row.AddCell()
		validCell.SetInt(v.Valid)

		validRateCell := row.AddCell()
		SetPercentForXlsx(v.ValidRate, validRateCell)

		chargeCell := row.AddCell()
		chargeCell.SetInt(v.Charge)

		chargeRateCell := row.AddCell()
		SetPercentForXlsx(v.ChargeRate, chargeRateCell)

		redundantCell := row.AddCell()
		redundantCell.SetInt(v.UnvalidRedundant)

		argsFormatCell := row.AddCell()
		argsFormatCell.SetInt(v.ArgsFormatError)

		argsUnsupportCell := row.AddCell()
		argsUnsupportCell.SetInt(v.ArgsUnsupport)

		chargeReuseCell := row.AddCell()
		chargeReuseCell.SetInt(v.ChargeReuse)

		chargeReuseRateCell := row.AddCell()
		SetPercentForXlsx(v.ChargeReuseRate, chargeReuseRateCell)

		norecordCell := row.AddCell()
		norecordCell.SetInt(v.NoRecord)

		providerErrorCell := row.AddCell()
		providerErrorCell.SetInt(v.ProviderError)

		systemErrorCell := row.AddCell()
		systemErrorCell.SetInt(v.PlatformError)

		timeoutConfCell := row.AddCell()
		timeoutConfCell.Value = v.TimeoutConf

		timeoutCountCell := row.AddCell()
		timeoutCountCell.SetInt(v.TimeoutCount)

		timeoutRateCell := row.AddCell()
		SetPercentForXlsx(v.TimeoutRate, timeoutRateCell)
		if reports != nil {
			reports = append(reports, converDataToBusinessReport(v))
		}
	}
	return reports
}

func insertBusinessReports(reports []TGdBusinessReport, date string, merchantId int64, apiId int64) error {
	if len(reports) == 0 {
		return nil
	}
	sql := fmt.Sprintf("delete from t_gd_business_report where date='%s'", date)
	if merchantId > 0 {
		sql = fmt.Sprintf("%s and merchant_id=%d", sql, merchantId)
	}
	if apiId > 0 {
		sql = fmt.Sprintf("%s and api_id=%d", sql, apiId)
	}
	o := orm.NewOrm()
	o.Begin()
	if _, err := o.Raw(sql).Exec(); err != nil {
		o.Rollback()
	}
	if _, err := o.InsertMulti(len(reports), &reports); err != nil {
		o.Rollback()
	}
	o.Commit()
	return nil

}

func sortBusiness(datas []apis.LogQueryUserAcessBusinessExport) ([]apis.LogQueryUserAcessBusinessExport) {
	if len(datas) == 0 {
		return datas
	}
	for i := 0; i < len(datas); i++ {
		for j := 0; j < len(datas) - i - 1; j++ {
			if datas[j].Total < datas[j+1].Total {
				tmp := datas[j]
				datas[j] = datas[j+1]
				datas[j+1] = tmp
			}
		}
	}
	return datas
}

type MerchantTotalInfo struct {
	MerchantId int64
	Total int
}

func sortMerchant(m map[int64]int) []MerchantTotalInfo{
	if len(m) == 0 {
		return nil
	}
	array := make([]MerchantTotalInfo, len(m))
	i := 0
	for k, v := range m {
		array[i].Total = v
		array[i].MerchantId = k
		i++
	}
	for i = 0; i < len(array); i++ {
		for j := 0; j < len(array) - i - 1; j++ {
			if array[j].Total < array[j+1].Total {
				tmp := array[j]
				array[j] = array[j+1]
				array[j+1] = tmp
			}
		}
	}
	return array
}

// exportBusinessDay商务日报
func exportBusinessDay(start, end, merchantId, apiId int64, isInsert bool) (string, error) {
	file := xlsx.NewFile()
	date := time.Unix(start, 0).Format("2006-01-02")
	detailSheet, err := file.AddSheet("商务报表" + date)
	if err != nil {
		l.Error("func",
			zap.String("call", "file.AddSheet"),
			zap.String("args", "明细"),
			zap.String("error", err.Error()))
		return "", err
	}
	makeXlsxBusinessHeader(detailSheet)
	reports := []TGdBusinessReport{}

	//merchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}
	//lastMerchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}

	mreq := apis.LogQueryUserAccessBusinessExportReq{}
	reply := apis.LogQueryUserAccessBusinessExportReply{}

	mreq.StartTimestamp = start
	mreq.EndTimestamp = end
	mreq.MerchantId = merchantId
	if apiId != 0 {
		mreq.ApiIdList = append(mreq.ApiIdList, apiId)
	}
	if time.Now().Unix()-start < 3600*26 {
		mreq.TabName = "t_gd_access_log_day"
	}
	err = LogQueryUserAccessBusinessExport(&mreq, &reply)
	if err != nil {
		l.Error("func",
			zap.String("call", "LogQueryUserAccessBusinessExport"),
			zap.String("args", utils.MarshalJsonString(mreq)),
			zap.String("error", err.Error()))
		return "", err
	}
	merchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}
	merchantTotalMap := map[int64]int{}
	for _, v := range reply.LogQueryUserAcessBusiness {
		if value, ok := merchantTotalMap[v.MerchantId]; ok {
			merchantTotalMap[v.MerchantId] = value + v.Total
		} else {
			merchantTotalMap[v.MerchantId] = v.Total
		}
		merchantMap[v.MerchantId] = append(merchantMap[v.MerchantId], v)
	}

	merchantSorted := sortMerchant(merchantTotalMap)
	for _, v := range merchantSorted {
		array, ok := merchantMap[v.MerchantId]
		if !ok {
			continue
		}
		array = sortBusiness(array)
		if len(array) == 0 {
			continue
		}
		reports = parseBusinessDetail(detailSheet, array, reports, array[0].MerchantName)
	}

	if isInsert {
		err = insertBusinessReports(reports, date, merchantId, apiId)
		if err != nil {
			return "", err
		}
	}

	filename := fmt.Sprintf("gd商务日报-%s", time.Unix(start, 0).Format("2006-01-02"))
	path := config.Conf.ReportLocalDir + filename + ".xlsx"
	setAllCells(file)
	err = file.Save(path)
	if err != nil {
		l.Error("func",
			zap.String("call", "save excel"),
			zap.String("file_name", path),
			zap.String("error", err.Error()))
	}
	return path, err
}

// exportBusinessWeek 商务周报
func exportBusinessWeek(weekStart string) (string, error) {
	start, end := getWeekExportTimestamp(weekStart)
	startDate := time.Unix(start, 0).Format("2006-01-02")
	endDate := time.Unix(end-1, 0).Format("2006-01-02")
	file := xlsx.NewFile()
	date := time.Unix(start, 0).Format("2006-01-02")
	detailSheet, err := file.AddSheet("商务报表周报" + date)
	if err != nil {
		l.Error("func",
			zap.String("call", "file.AddSheet"),
			zap.String("args", "明细"),
			zap.String("error", err.Error()))
		return "", err
	}
	makeXlsxBusinessHeader(detailSheet)

	//merchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}
	//lastMerchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}

	datas, err := getBusinessDataFromReport(startDate, endDate)
	if err != nil {
		l.Error("func",
			zap.String("call", "getBusinessDataFromReport"),
			zap.String("args", ""),
			zap.String("error", err.Error()))
		return "", err
	}


	merchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}
	merchantTotalMap := map[int64]int{}
	for _, v := range datas {
		if value, ok := merchantTotalMap[v.MerchantId]; ok {
			merchantTotalMap[v.MerchantId] = value + v.Total
		} else {
			merchantTotalMap[v.MerchantId] = v.Total
		}
		merchantMap[v.MerchantId] = append(merchantMap[v.MerchantId], v)
	}

	merchantSorted := sortMerchant(merchantTotalMap)
	for _, v := range merchantSorted {
		array, ok := merchantMap[v.MerchantId]
		if !ok {
			continue
		}
		array = sortBusiness(array)
		if len(array) == 0 {
			continue
		}
		parseBusinessDetail(detailSheet, array, nil, array[0].MerchantName)
	}


	filename := fmt.Sprintf("gd商务周报-%s-%s", startDate, endDate)
	path := config.Conf.ReportLocalDir + filename + ".xlsx"
	setAllCells(file)
	err = file.Save(path)
	if err != nil {
		l.Error("func",
			zap.String("call", "save excel"),
			zap.String("file_name", path),
			zap.String("error", err.Error()))
	}
	return path, err
}

// exportBusinessMonth商务月报
func exportBusinessMonth(monthStart string) (string, error) {
	start, end := getMonthExportTimestamp(monthStart)
	startDate := time.Unix(start, 0).Format("2006-01-02")
	endDate := time.Unix(end-1, 0).Format("2006-01-02")
	file := xlsx.NewFile()
	date := time.Unix(start, 0).Format("2006-01-02")
	detailSheet, err := file.AddSheet("商务报表月报" + date)
	if err != nil {
		l.Error("func",
			zap.String("call", "file.AddSheet"),
			zap.String("args", "明细"),
			zap.String("error", err.Error()))
		return "", err
	}
	makeXlsxBusinessHeader(detailSheet)

	//merchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}
	//lastMerchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}

	datas, err := getBusinessDataFromReport(startDate, endDate)
	if err != nil {
		l.Error("func",
			zap.String("call", "getBusinessDataFromReport"),
			zap.String("args", ""),
			zap.String("error", err.Error()))
		return "", err
	}

	merchantMap := map[int64][]apis.LogQueryUserAcessBusinessExport{}
	merchantTotalMap := map[int64]int{}
	for _, v := range datas {
		if value, ok := merchantTotalMap[v.MerchantId]; ok {
			merchantTotalMap[v.MerchantId] = value + v.Total
		} else {
			merchantTotalMap[v.MerchantId] = v.Total
		}
		merchantMap[v.MerchantId] = append(merchantMap[v.MerchantId], v)
	}

	merchantSorted := sortMerchant(merchantTotalMap)
	for _, v := range merchantSorted {
		array, ok := merchantMap[v.MerchantId]
		if !ok {
			continue
		}
		array = sortBusiness(array)
		if len(array) == 0 {
			continue
		}
		parseBusinessDetail(detailSheet, array, nil, array[0].MerchantName)
	}

	filename := fmt.Sprintf("gd商务月报-%s-%s", startDate, endDate)
	path := config.Conf.ReportLocalDir + filename + ".xlsx"
	setAllCells(file)
	err = file.Save(path)
	if err != nil {
		l.Error("func",
			zap.String("call", "save excel"),
			zap.String("file_name", path),
			zap.String("error", err.Error()))
	}
	return path, err
}