gd_crontab/impl/crontab/log_query_user_access_business_export.go

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

package crontab

import (
	"context"
	"gd_crontab/apis"
	"gd_crontab/errors"
	"gd_crontab/rpc_apis"
	"gd_crontab/rpc_apis/gd_management"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/astaxie/beego/orm"
)

// getBusinessWhereExport 商务报表where子句
func getBusinessWhereExport(req *apis.LogQueryUserAccessBusinessExportReq) (w string, s []interface{}) {
	if req.EndTimestamp != 0 && req.StartTimestamp != 0 {
		w += " and " + "timestamp >=" + " ? and timestamp < ?"
		s = append(s, req.StartTimestamp, req.EndTimestamp)
	} else {
		nowTime := time.Now()
		zeroTime := time.Date(nowTime.Year(), nowTime.Month(), nowTime.Day(), 0, 0, 0, 0, nowTime.Location())
		startTimeStamp := zeroTime.Unix()
		endTimeStamp := nowTime.Unix()

		w += " and " + "timestamp >= " + " ? and timestamp < ?"
		s = append(s, startTimeStamp, endTimeStamp)
	}
	if len(req.ApiIdList) != 0 {
		w += " and api_id in (" + strings.Replace(strings.Trim(fmt.Sprint(req.ApiIdList), "[]"), " ", ",", -1) + ")"
	}
	if req.MerchantId != 0 {
		//strings.Replace(strings.Trim(fmt.Sprint(array_or_slice), "[]"), " ", ",", -1)
		w += " and " + "merchant_id" + " = ?"
		s = append(s, req.MerchantId)
	}

	if len(s) > 0 {
		//有条件才截取最前面的and
		w = string([]byte(w)[4:])
		w = " where" + w + " and merchant_data_api_id > 0"
		return w, s
	} else {
		return " where  merchant_data_api_id > 0", nil
	}
}

// getBusinessExportSql 商务报表统计sql
func getBusinessExportSql(req *apis.LogQueryUserAccessBusinessExportReq, where string) string {
	sqlStr := "select create_time as date, merchant_name, api_id, merchant_id, group_concat(distinct merchant_data_api_id) as merchant_data_api_id, api_name,count(state) as total ," +
		"count(distinct case when (code <> 0 and code <> 1100 and code <> 1107) then search end) as unvalid_unredundant," +
		"sum(code<>0 and code <> 1100 and code <> 1107) as unvalid ," +
		"sum(code=0) as success," +
		"count(distinct case when (code = 1100 or code = 1107) then search end) as norecord_unredundant," +
		"count(distinct case when (code <> 0) then search end) as failed_unredundant," +
		"count(distinct case when code=1102 then search end) as args_format_error," +
		"sum(code=1023) as args_unsupport," +
		"sum(raw_code=1100) as no_record, " +
		"sum(is_reuse=1 and code=0) as reuse," +
		"sum(raw_code=1101 or raw_code = 1108 or raw_code=1109 or raw_code = 1105 or raw_code=1106) as provider_error, " +
		"sum(raw_code=1107 or raw_code= 1110) as platform_error" +
		" from " + req.TabName + " as a %s group by create_time, merchant_id, api_id order by timestamp"

	sqlStr = fmt.Sprintf(sqlStr, where)
	return sqlStr
}

// getBusinessCountCodeInfo 获取计费码
func getBusinessCountCodeInfo(req *apis.LogQueryUserAccessBusinessExportReq) (map[string]gd_management.UserMerchantCountCode, []gd_management.UserMerchantCountCode, error) {

	mReq := gd_management.MangementGetUserMerchantCountCodeReq{}
	mreply, err := rpc_apis.Management.MangementGetUserMerchantCountCode(context.Background(), &mReq)
	if err != nil {
		return nil,nil, err
	}
	countMap := make(map[string]gd_management.UserMerchantCountCode, len(mreply.UserMerchantCountCode))
	for _, v := range mreply.UserMerchantCountCode {
		countMap[strconv.Itoa(int(v.MerchantDataApiId))+strconv.Itoa(int(v.ApiId))] = v
	}

	return countMap, mreply.UserMerchantCountCode, nil
}

// computeBusinessCharge 计费计算
// 计费方式为即查时，计费量等于总调用量
// 计费方式为查得时，如果计费码为空，计费量为0
// 计费方式为查得时，如果计费码不为空，计费量根据计费码统计
func computeBusinessCharge(req *apis.LogQueryUserAccessBusinessExportReq,
	info *apis.LogQueryUserAcessBusinessExport, countMap map[string]gd_management.UserMerchantCountCode) {
	sqlStr := ""
	mdids := strings.Split(info.MerchantDataApiId, ",")
	for _, mdid := range mdids {
		v, ok := countMap[mdid+fmt.Sprintf("%d", info.ApiId)]
		if ok == false {
			continue
		}
		count := 0
		chargeReuseCount := 0
		switch {
		case v.CountType == 0 && len(mdids) == 1:
			chargeReuseCount = info.Reuse
			count = info.Total
		case v.CountType == 0 && (len(mdids) > 1):
			sqlStr = "select count(id), sum(is_reuse) from " + req.TabName + " where merchant_data_api_id = %s and api_id = %d  and timestamp >= ? and timestamp < ?"
			sqlStr = fmt.Sprintf(sqlStr, mdid, info.ApiId)
			start, end := getStartEndTimestamp(req.StartTimestamp, req.EndTimestamp, info.Date)
			orm.NewOrm().Raw(sqlStr, start, end).QueryRow(&count, &chargeReuseCount)
		default:
			if v.CountCode == "" {
				count = 0
				chargeReuseCount = 0
			} else {
				if strings.Contains(v.CountCode, "1100") {
					v.CountCode = fmt.Sprintf("%s,1105,1106,1107,1108,1109,1110", v.CountCode)
				}
				start, end := getStartEndTimestamp(req.StartTimestamp, req.EndTimestamp, info.Date)
				sqlStr = "select count(id), sum(is_reuse) from " + req.TabName + " where merchant_data_api_id = %s and api_id = %d and raw_code in (%s) and timestamp >= ? and timestamp < ?"
				sqlStr = fmt.Sprintf(sqlStr, mdid, info.ApiId, v.CountCode)
				orm.NewOrm().Raw(sqlStr, start, end).QueryRow(&count, &chargeReuseCount)
			}
		}
		info.Charge += count
		info.ChargeReuse += chargeReuseCount
	}

	if info.Valid == 0 {
		info.ChargeRate = "0%"
	} else {
		info.ChargeRate = strconv.FormatFloat(float64(100*info.Charge)/float64(info.Valid), 'f', 2, 64) + "%"
	}
	info.ChargeRate = computeBusinessPercent(float64(info.Charge), float64(info.Valid))
	info.ChargeReuseRate = computeBusinessPercent(float64(info.ChargeReuse), float64(info.Charge))
}

// 设置默认统计时间段
func setBusinessSearchTimestamp(req *apis.LogQueryUserAccessBusinessExportReq) {
	if req.EndTimestamp == 0 || req.StartTimestamp == 0 {
		nowTime := time.Now()
		zeroTime := time.Date(nowTime.Year(), nowTime.Month(), nowTime.Day(), 0, 0, 0, 0, nowTime.Location())
		req.StartTimestamp = zeroTime.Unix()
		req.EndTimestamp = nowTime.Unix()
	}
}

// computeBusinessTimeout 计算超时信息
func computeBusinessTimeout(info *apis.LogQueryUserAcessBusinessExport, timeoutInfo map[string]int, tabname string) {
	mdids := strings.Split(info.MerchantDataApiId, ",")
	info.TimeoutConf = "0s"
	for _, mdid := range mdids {
		count := 0
		if t, ok := timeoutInfo[mdid+fmt.Sprintf("%d", info.ApiId)]; ok {
			if t > 0 {
				if info.TimeoutConf == "0s" {
					info.TimeoutConf = fmt.Sprintf("%ds", t)
				} else {
					info.TimeoutConf = fmt.Sprintf("%s,%ds", t, info.TimeoutConf)
				}
				sqlStr := "select count(id) from " + tabname + " where merchant_data_api_id = %s and api_id = %d and elapsed >= %d and create_time = ?"
				sqlStr = fmt.Sprintf(sqlStr, mdid, info.ApiId, t)
				orm.NewOrm().Raw(sqlStr, info.Date).QueryRow(&count)
			}
		}
		info.TimeoutCount += count
	}
	info.TimeoutRate = computeBusinessPercent(float64(info.TimeoutCount), float64(info.Total))
}

// computeBusinessPercent 计算百分率，参数依次为分子和分母
func computeBusinessPercent(Numerator float64, denominator float64) string {
	if denominator == 0 {
		return "0.00%"
	}
	return strconv.FormatFloat(100.0*Numerator/denominator, 'f', 2, 64) + "%"
}

func logQueryUserAccessBusinessExport(req *apis.LogQueryUserAccessBusinessExportReq, reply *apis.LogQueryUserAccessBusinessExportReply, noShowMap map[int64]bool, timeoutInfo map[string]int, countMap map[string]gd_management.UserMerchantCountCode) error {
	// 查询
	where, val := getBusinessWhereExport(req)
	sqlStr := getBusinessExportSql(req, where)

	o := orm.NewOrm()
	_, err := o.Raw(sqlStr, val).QueryRows(&reply.LogQueryUserAcessBusiness)
	if err != nil && err != orm.ErrNoRows {
		return errors.DataBaseError
	}
	// 计算指标
	for index, info := range reply.LogQueryUserAcessBusiness {
		// 无效中的重复调用
		reply.LogQueryUserAcessBusiness[index].UnvalidRedundant = info.Unvalid - info.UnvalidUnredundant
		// 去重调用
		reply.LogQueryUserAcessBusiness[index].DistinctCount = info.Success + reply.LogQueryUserAcessBusiness[index].FailedUnredundant
		// 有效调用
		reply.LogQueryUserAcessBusiness[index].Valid = reply.LogQueryUserAcessBusiness[index].Success + info.NorecordUnredundant
		// 有效率
		reply.LogQueryUserAcessBusiness[index].ValidRate = computeBusinessPercent(float64(reply.LogQueryUserAcessBusiness[index].Valid), float64(info.Total))

		computeBusinessCharge(req, &reply.LogQueryUserAcessBusiness[index], countMap)

		computeBusinessTimeout(&reply.LogQueryUserAcessBusiness[index], timeoutInfo, req.TabName)
	}

	countMap = nil
	noShowMap = nil
	timeoutInfo = nil

	return nil
}

// LogQueryUserAccessBusinessExport 商务报表统计
func LogQueryUserAccessBusinessExport(req *apis.LogQueryUserAccessBusinessExportReq, reply *apis.LogQueryUserAccessBusinessExportReply) error {
	setBusinessSearchTimestamp(req)
	showInfo,err := getNotShow()
	if err != nil {
		return err
	}
	timeoutInfo,err := getTimeoutInfo()
	if err != nil {
		return err
	}
	countCodeInfo, _, err := getBusinessCountCodeInfo(req)
	if err != nil {
		return err
	}

	if req.TabName == "t_gd_access_log_day" {
		return logQueryUserAccessBusinessExport(req, reply, showInfo, timeoutInfo, countCodeInfo)
	}

	exportTimes := getExportTimes(req.StartTimestamp, req.EndTimestamp)
	for _, v := range exportTimes {
		req.StartTimestamp = v.start
		req.EndTimestamp = v.end
		req.TabName = getMonthTab("t_gd_access_log_month", req.StartTimestamp)
		subReply := &apis.LogQueryUserAccessBusinessExportReply{}
		err := logQueryUserAccessBusinessExport(req, subReply, showInfo, timeoutInfo, countCodeInfo)
		if err != nil {
			return err
		}
		reply.LogQueryUserAcessBusiness = append(reply.LogQueryUserAcessBusiness, subReply.LogQueryUserAcessBusiness...)
	}
	return nil

}