gd_crontab/impl/crontab/log_query_provider_count_export.go

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/tidwall/gjson"

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

const pageSize = 1000

func zqyProviderCountSql(req *apis.LogQueryProviderCountExportReq) string {
	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()
	}

	if req.GroupByMerchantApi == true {
		return fmt.Sprintf("select response_params from %s where provider_api_id=? and merchant_id = ? and api_id = ? and raw_code=1 and create_time=? GROUP BY search limit ? offset ?", req.TabName)
	}

	return fmt.Sprintf("select response_params from %s where provider_api_id=? and raw_code=1 and create_time=? GROUP BY search limit ? offset ?", req.TabName)
}

func zqyProviderCount(req *apis.LogQueryProviderCountExportReq, countExport *apis.LogQueryProviderCountExport) {
	countExport.Charge = 0
	var rCount int64
	var err error
	sqlStr := zqyProviderCountSql(req)
	o := orm.NewOrm()
	offset := 0
	for {
		results := make([]string, 0)
		if req.GroupByMerchantApi == true {
			rCount, err = o.Raw(sqlStr, countExport.ProviderApiId, req.MerchantId, req.ApiId, countExport.Date, pageSize, offset*pageSize).QueryRows(&results)
			if err != nil {
				break
			}
		} else {
			rCount, err = o.Raw(sqlStr, countExport.ProviderApiId, countExport.Date, pageSize, offset*pageSize).QueryRows(&results)
			if err != nil {
				break
			}
		}
		for _, r := range results {
			arr := gjson.Get(r, "ds").Array()
			flag := false
			for _, a := range arr {
				count := 0
				if countExport.ProviderApiName == "车牌查车档" {
					if vin := gjson.Get(a.String(), "车辆识别代码").String(); vin == "" {
						count += 1
					}
				} else if countExport.ProviderApiName == "VIN码查车档" {
					if plateNo := gjson.Get(a.String(), "CPH").String(); plateNo == "" {
						count += 1
					}
				} else if countExport.ProviderApiName == "VIN6获取车档" {
					if vin := gjson.Get(a.String(), "车辆识别代码").String(); vin == "" {
						count += 1
					}
					if plateNo := gjson.Get(a.String(), "CPH").String(); plateNo == "" {
						count += 1
					}
				}

				if v := gjson.Get(a.String(), "品牌").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "车辆型号").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "发动机号").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "使用性质").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "初次登记时间").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "燃料种类").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "排量").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "发动机型号").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "额定载客").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "车辆名称").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "车身颜色").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "出厂日期").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "轴数").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "轴距").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "前轮距").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "后轮距").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "总质量").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "整备质量").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "额定载质量").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "功率").String(); v == "" {
					count += 1
				}
				if v := gjson.Get(a.String(), "承保日期").String(); v == "" {
					count += 1
				}

				if count < 3 {
					flag = true
				}

			}
			if flag == true {
				countExport.Charge++
			}
		}

		if rCount < pageSize {
			break
		}

		offset++
	}

}

func getProviderCountWhereExport(req *apis.LogQueryProviderCountExportReq) (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.ProviderApiIdList) != 0 {
		w += " and provider_api_id in (" + strings.Replace(strings.Trim(fmt.Sprint(req.ProviderApiIdList), "[]"), " ", ",", -1) + ")"
	}
	if req.ProviderId != 0 {
		//strings.Replace(strings.Trim(fmt.Sprint(array_or_slice), "[]"), " ", ",", -1)
		w += " and " + "provider_id" + " = ?"
		s = append(s, req.ProviderId)
	}

	if req.ApiId != 0 {
		w += " and " + "api_id" + " = ?"
		s = append(s, req.ApiId)
	}
	if req.MerchantId != 0 {
		w += " and " + "merchant_id" + " = ?"
		s = append(s, req.MerchantId)
	}

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

}

func getProviderExportSql(req *apis.LogQueryProviderCountExportReq, where string) string {

	sqlStr := "select create_time as date, provider_api_id, count(state) as total, " +
		"sum(state=1) as success, sum(code<>1100 and state=1) as query, " +
		"sum(state<>1) as failed," +
		"sum(code=1100) as query_no_record, " +
		"avg(elapsed) as avg_elapsed, " +
		"sum(elapsed) as sum_elapsed" +
		"%s" +
		" from " + req.TabName + " as a %s group by create_time,provider_api_id order by timestamp"
	if req.GroupByMerchantApi {
		sqlStr = "select create_time as date, provider_api_id, merchant_id, api_id, merchant_name, api_name, count(state) as total, " +
			"sum(state=1) as success, sum(code<>1100 and state=1) as query, " +
			"sum(state<>1) as failed," +
			"sum(code=1100) as query_no_record, " +
			"avg(elapsed) as avg_elapsed, " +
			"sum(elapsed) as sum_elapsed" +
			"%s" +
			" from " + req.TabName + " as a %s group by create_time, merchant_id, api_id,provider_api_id order by merchant_id, api_id, provider_api_id, timestamp"

	}
	//if req.HasMerchantName {
	//	sqlStr = "select create_time as date, provider_api_id, merchant_name, api_name, count(state) as total, " +
	//		"sum(state=1) as success, sum(code<>20001 and state=1) as query, " +
	//		"sum(state<>1) as failed," +
	//		"sum(code=20001) as query_no_record, " +
	//		"avg(elapsed) as avg_elapsed, " +
	//		"sum(elapsed) as sum_elapsed" +
	//		"%s" +
	//		" from " + req.TabName + " as a %s group by create_time,provider_api_id order by timestamp"
	//}

	concat := ""
	subSql := ""
	timeList := strings.Split(req.TimeList, ",")
	for _, v := range timeList {
		if v == "" {
			break
		}
		if subSql == "" {
			subSql = fmt.Sprintf("sum(elapsed > %s)", v)
			continue
		}
		subSql = fmt.Sprintf("%s,sum(elapsed > %s)", subSql, v)
	}
	if subSql != "" {
		concat = fmt.Sprintf(",concat_ws(',', %s) as big_elapseds", subSql)
	}

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

type ProviderCountInfo struct {
	ProviderApiId   int64  `json:"provider_api_id"`
	CountType       int    `json:"count_type"`
	CountKey        string `json:"count_key"`
	CountValue      string `json:"count_value"`
	RawCode         string `json:"raw_code"`
	ProviderName    string `json:"provider_name"`
	ProviderApiName string `json:"provider_api_name"`
}

func getProviderCountInfo() (map[int64]ProviderCountInfo, error) {
	req := gd_management.ManagementGetProviderCountInfoReq{}
	reply, err := rpc_apis.Management.ManagementGetProviderCountInfo(context.Background(), &req)
	if err != nil {
		return nil, err
	}
	ret := map[int64]ProviderCountInfo{}
	for _, v := range reply.Infos {
		item := ProviderCountInfo{}
		item.ProviderApiId = v.ProviderApiId
		item.CountType = v.CountType
		item.CountKey = v.CountKey
		item.CountValue = v.CountValue
		item.ProviderName = v.ProviderName
		item.ProviderApiName = v.ProviderApiName
		delimer := ","
		if strings.Contains(v.CountValue, ";") {
			delimer = ";"
		}
		array := strings.Split(v.CountValue, delimer)
		for _, code := range array {
			if item.RawCode == "" {
				item.RawCode = fmt.Sprintf("'%s'", code)
				continue
			}
			item.RawCode = fmt.Sprintf("%s,'%s'", item.RawCode, code)
		}
		ret[v.ProviderApiId] = item
	}
	return ret, nil
}


func computeProviderCharge(o orm.Ormer, req *apis.LogQueryProviderCountExportReq,
	info *apis.LogQueryProviderCountExport, countInfos map[int64]ProviderCountInfo) error {
	concat := ""
	subSql := ""
	timeList := strings.Split(req.TimeList, ",")
	for _, v := range timeList {
		if v == "" {
			break
		}
		if subSql == "" {
			subSql = fmt.Sprintf("sum(elapsed > %s)", v)
			continue
		}
		subSql = fmt.Sprintf("%s,sum(elapsed > %s)", subSql, v)
	}
	if subSql != "" {
		concat = fmt.Sprintf(",concat_ws(',', %s) as big_elapseds", subSql)
	}
	chargeBigElapseds := ""
	info.ChargeBigElapsedList = make([]int, len(timeList))
	if countInfo, ok := countInfos[info.ProviderApiId]; ok {
		if countInfo.CountType == 0 {
			info.Charge = info.Total
			info.ChargeRate = "100%"
			chargeBigElapseds = info.BigElapseds
		} else if countInfo.CountValue == "" {
			info.Charge = 0
			info.ChargeRate = "0%"
			for i := 0; i < len(timeList); i++ {
				if chargeBigElapseds == "" {
					chargeBigElapseds = "0"
				} else {
					chargeBigElapseds = fmt.Sprintf("%s,0", chargeBigElapseds)
				}
			}
		} else {
			start, end := getStartEndTimestamp(req.StartTimestamp, req.EndTimestamp, info.Date)
			count := int64(0)
			sql := fmt.Sprintf("select count(1) "+concat+" from "+req.TabName+" where raw_code in(%s) and provider_api_id=%d and timestamp >= %d and timestamp < %d", countInfo.RawCode, info.ProviderApiId, start, end)

			if req.MerchantId > 0 {
				sql = fmt.Sprintf("%s and merchant_id=%d", sql, req.MerchantId)
			}
			if req.ApiId > 0 {
				sql = fmt.Sprintf("%s and api_id=%d", sql, req.ApiId)
			}
			if req.GroupByMerchantApi {
				sql = fmt.Sprintf("%s and merchant_id=%d and api_id=%d", sql, info.MerchantId, info.ApiId)
			}
			if concat == "" {
				err := o.Raw(sql).QueryRow(&count)
				if err != nil {
					return errors.DataBaseError
				}
			} else {
				err := o.Raw(sql).QueryRow(&count, &chargeBigElapseds)
				if err != nil {
					return errors.DataBaseError
				}
			}
			info.Charge = count
			info.ChargeRate = strconv.FormatFloat(float64(100*count)/float64(info.Total), 'f', 2, 64) + "%"
		}
	} else {
		info.Charge = info.Total
		info.ChargeRate = "100%"
		chargeBigElapseds = info.BigElapseds
	}

	if chargeBigElapseds != "" {
		array := strings.Split(chargeBigElapseds, ",")
		for i, _ := range array {
			vint, _ := strconv.Atoi(array[i])
			info.ChargeBigElapsedList[i] += vint
		}
	}
	return nil
}

func logQueryProviderCountExport(req *apis.LogQueryProviderCountExportReq, reply *apis.LogQueryProviderCountExportReply) error {
	o := orm.NewOrm()
	where, val := getProviderCountWhereExport(req)
	sqlStr := getProviderExportSql(req, where)

	_, err := o.Raw(sqlStr, val).QueryRows(&reply.LogQueryProviderCount)
	if err != nil && err != orm.ErrNoRows {
		return errors.DataBaseError
	}
	countInfos, err := getProviderCountInfo()
	if err != nil {
		return err
	}


	for i, v := range reply.LogQueryProviderCount {
		if info, ok := countInfos[v.ProviderApiId]; ok {
			reply.LogQueryProviderCount[i].ProviderName = info.ProviderName
			reply.LogQueryProviderCount[i].ProviderApiName = info.ProviderApiName
		}
	}

	for index, info := range reply.LogQueryProviderCount {
		if err := computeProviderCharge(o, req, &reply.LogQueryProviderCount[index], countInfos); err != nil {
			return err
		}
		if info.BigElapseds != "" {
			array := strings.Split(info.BigElapseds, ",")
			reply.LogQueryProviderCount[index].BigElapsedList = make([]int, len(array))
			for i, _ := range array {
				vint, _ := strconv.Atoi(array[i])
				reply.LogQueryProviderCount[index].BigElapsedList[i] = vint
			}
		}
		reply.LogQueryProviderCount[index].Failed = info.Total - info.Success

		if info.Total > 0 {
			reply.LogQueryProviderCount[index].FailedRate = strconv.FormatFloat(float64(100*info.Failed)/float64(info.Total), 'f', 2, 64) + "%"
			reply.LogQueryProviderCount[index].QueryNoRecordRate = strconv.FormatFloat(float64(100*info.QueryNoRecord)/float64(info.Total), 'f', 2, 64) + "%"
		} else {
			reply.LogQueryProviderCount[index].FailedRate = "0%"
			reply.LogQueryProviderCount[index].QueryNoRecordRate = "0%"
		}
	}

	return nil
}

func LogQueryProviderCountExportNew(req *apis.LogQueryProviderCountExportReq, reply *apis.LogQueryProviderCountExportReply) error {
	if req.EndTimestamp == 0 || req.StartTimestamp == 0 {
		now := time.Now()
		req.EndTimestamp = now.Unix()
		req.StartTimestamp = time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).Unix()
	}
	if req.TabName == "t_gd_thirdpart_log_day" {
		return logQueryProviderCountExport(req, reply)
	}

	exportTimes := getExportTimes(req.StartTimestamp, req.EndTimestamp)
	for _, v := range exportTimes {
		req.StartTimestamp = v.start
		req.EndTimestamp = v.end
		req.TabName = getMonthTab("t_gd_thirdpart_log_month", req.StartTimestamp)
		subReply := &apis.LogQueryProviderCountExportReply{}
		err := logQueryProviderCountExport(req, subReply)
		if err != nil {
			return err
		}
		reply.LogQueryProviderCount = append(reply.LogQueryProviderCount, subReply.LogQueryProviderCount...)
	}
	return nil

}

func LogQueryProviderCountExport(ctx context.Context, req *apis.LogQueryProviderCountExportReq, reply *apis.LogQueryProviderCountExportReply) error {
	return LogQueryProviderCountExportNew(req, reply)
}

func ExportProviderDay(ctx context.Context, req *apis.LogQueryProviderCountExportReq, reply *apis.LogQueryProviderCountExportReply) error {
	exportProviderDay(req.StartTimestamp, req.EndTimestamp, 0, true)
	return nil
}