gd_management/impl/user_merchant/user_list.go

package user_merchant

import (
	"context"
	"fmt"
	"gd_management/apis"
	"gd_management/errors"
	"strings"
	"time"

	"gd_management/common.in/utils"
	"github.com/astaxie/beego/orm"
	"go.uber.org/zap"
)

var PAGESIZE = 10

func getUserMerchantListSql(apiIds string, dataApiId int64) string {
	sql := `SELECT
	a.name,
	a.phone,
	a.email,
	b.id,
	b.merchant_name,
	b.social_code,
	b.address,
	b.contact_name,
	b.contact_number,
	b.merchant_license,
	b.auth_status,
	b.app_key,
	b.app_secret,
	b.app_password,
	b.warning_enable,
	(
	SELECT
		count( 1 ) 
	FROM
		t_gd_data_api_order 
	WHERE
		merchant_id = b.id
		AND combo_id IN (
		SELECT
			id 
		FROM
			t_gd_data_api_combo 
		WHERE
			query_type_id IN ( SELECT id FROM t_gd_data_api_query_type WHERE data_api_id IN ( SELECT id FROM t_gd_data_api WHERE data_api_type = 0 ) ) 
		) 
	) AS datacount,
	(
	SELECT
		count( 1 ) 
	FROM
		t_gd_data_api_order 
	WHERE
		merchant_id = b.id 
		AND combo_id IN (
		SELECT
			id 
		FROM
			t_gd_data_api_combo 
		WHERE
			query_type_id IN ( SELECT id FROM t_gd_data_api_query_type WHERE data_api_id IN ( SELECT id FROM t_gd_data_api WHERE data_api_type = 1 ) ) 
		) 
	) AS h5count,
	( SELECT sum( goods_price ) FROM t_gd_data_api_order WHERE merchant_id = b.id AND STATUS = 2 ) AS totalcost 
FROM
	t_gd_user AS a
	INNER JOIN t_gd_merchants AS b ON a.id = b.user_id AND b.auth_status=1`
	if apiIds != "" {
		sql = `SELECT
		a.name,
		a.phone,
		a.email,
		d.api_id,
		b.id,
		b.merchant_name,
		b.social_code,
		b.address,
		b.contact_name,
		b.contact_number,
		b.merchant_license,
		b.auth_status,
		b.app_key,
		b.app_secret,
		b.app_password,
		b.warning_enable,
		(
		SELECT
			count( 1 ) 
		FROM
			t_gd_data_api_order 
		WHERE
			merchant_id = b.id 
			AND combo_id IN (
			SELECT
				id 
			FROM
				t_gd_data_api_combo 
			WHERE
				query_type_id IN ( SELECT id FROM t_gd_data_api_query_type WHERE data_api_id IN ( SELECT id FROM t_gd_data_api WHERE data_api_type = 0 ) ) 
			) 
		) AS datacount,
		(
		SELECT
			count( 1 ) 
		FROM
			t_gd_data_api_order 
		WHERE
			merchant_id = b.id 
			AND combo_id IN (
			SELECT
				id 
			FROM
				t_gd_data_api_combo 
			WHERE
				query_type_id IN ( SELECT id FROM t_gd_data_api_query_type WHERE data_api_id IN ( SELECT id FROM t_gd_data_api WHERE data_api_type = 1 ) ) 
			) 
		) AS h5count,
		( SELECT sum( goods_price ) FROM t_gd_data_api_order WHERE merchant_id = b.id AND STATUS = 2 ) AS totalcost 
FROM
		t_gd_user AS a
		INNER JOIN t_gd_merchants AS b ON a.id = b.user_id
		inner join t_gd_merchant_data_api as c on c.merchant_id = b.id
		inner join t_gd_merchant_child_data_api as d on d.merchant_data_api_id=c.id
		where d.api_id in(` + apiIds + `)`
		return sql
	}

	if dataApiId > 0 {
		sql = `SELECT
		a.name,
		a.phone,
		a.email,
		b.id,
		b.merchant_name,
		b.social_code,
		b.address,
		b.contact_name,
		b.contact_number,
		b.merchant_license,
		b.auth_status,
		b.app_key,
		b.app_secret,
		b.app_password,
		b.warning_enable,
		(
		SELECT
			count( 1 ) 
		FROM
			t_gd_data_api_order 
		WHERE
			merchant_id = b.id 
			AND combo_id IN (
			SELECT
				id 
			FROM
				t_gd_data_api_combo 
			WHERE
				query_type_id IN ( SELECT id FROM t_gd_data_api_query_type WHERE data_api_id IN ( SELECT id FROM t_gd_data_api WHERE data_api_type = 0 ) ) 
			) 
		) AS datacount,
		(
		SELECT
			count( 1 ) 
		FROM
			t_gd_data_api_order 
		WHERE
			merchant_id = b.id 
			AND combo_id IN (
			SELECT
				id 
			FROM
				t_gd_data_api_combo 
			WHERE
				query_type_id IN ( SELECT id FROM t_gd_data_api_query_type WHERE data_api_id IN ( SELECT id FROM t_gd_data_api WHERE data_api_type = 1 ) ) 
			) 
		) AS h5count,
		( SELECT sum( goods_price ) FROM t_gd_data_api_order WHERE merchant_id = b.id AND STATUS = 2 ) AS totalcost 
FROM
		t_gd_user AS a
		INNER JOIN t_gd_merchants AS b ON a.id = b.user_id
		inner join t_gd_merchant_data_api as c on c.merchant_id = b.id
		inner join t_gd_data_api_query_type as d on d.id = c.query_type_id
		where d.data_api_id = ` + fmt.Sprintf("%d", dataApiId) + ``
		return sql
	}

	return sql
}

func isContainApiIds(list []apis.UserMerchantItem, apiIds string) bool {
	array := strings.Split(apiIds, ",")
	for _, apiId := range array {
		exist := false
		for _, v := range list {
			if fmt.Sprintf("%d", v.ApiId) == apiId {
				exist = true
				break
			}
		}
		if exist == false {
			return false
		}
	}
	return true
}

func clearMerchantList(reply *apis.ManagementGetUserMerchantListReply, apiIds string) {

	m := map[int64][]apis.UserMerchantItem{}
	newList := []apis.UserMerchantItem{}
	for _, v := range reply.MerchantList {
		m[v.MerchantId] = append(m[v.MerchantId], v)
	}
	for _, v := range m {
		if isContainApiIds(v, apiIds) {
			newList = append(newList, v[0])
		}
	}
	reply.MerchantList = newList
}

func ManagementGetUserMerchantList(ctx context.Context, req *apis.ManagementGetUserMerchantListReq, reply *apis.ManagementGetUserMerchantListReply) (err error) {
	defer func() {
		if err != nil {
			return
		}
		if req.ApiIds == "" {
			return
		}
		clearMerchantList(reply, req.ApiIds)
	}()
	if req.PageNumber > 0 {
		req.PageNumber -= 1
	} else {
		req.PageNumber = 0
	}
	sql := getUserMerchantListSql(req.ApiIds, req.DataApiId)
	var maps []orm.Params
	o := orm.NewOrm()
	if req.IsAll {
		_, err = o.Raw(sql).Values(&maps)
		if err != nil {
			l.Error("func",
				zap.String("call", "ManagementGetUserMerchantList"),
				zap.String("args", utils.MarshalJsonString(req)),
				zap.String("error", err.Error()))
			return errors.DataBaseError
		}
	} else {
		sql = sql + " LIMIT ? OFFSET ?"
		_, err = o.Raw(sql, PAGESIZE, req.PageNumber*PAGESIZE).Values(&maps)
		if err != nil {
			l.Error("func",
				zap.String("call", "ManagementGetUserMerchantList"),
				zap.String("args", utils.MarshalJsonString(req)),
				zap.String("error", err.Error()))
			return errors.DataBaseError
		}
		err = o.Raw("select count(1) from t_gd_merchants where auth_status=1").QueryRow(&reply.Total)
		if err != nil {
			l.Error("func",
				zap.String("call", "ManagementGetUserMerchantList"),
				zap.String("args", utils.MarshalJsonString(req)),
				zap.String("error", err.Error()))
			return errors.DataBaseError
		}
		reply.PageSize = PAGESIZE
		reply.PageNumber = req.PageNumber + 1
	}

	for _, v := range maps {
		iterm := apis.UserMerchantItem{}
		iterm.DataApiOrderCount = int(ormStringToInt(v, "datacount"))
		iterm.H5ApiOrderCount = int(ormStringToInt(v, "h5count"))
		iterm.TotalCost = ormStringToFloat(v, "totalcost")
		iterm.User.Name, _ = v["name"].(string)
		iterm.User.Phone, _ = v["phone"].(string)
		iterm.User.Email, _ = v["email"].(string)
		iterm.Merchant.MerchantName, _ = v["merchant_name"].(string)
		iterm.Merchant.SocialCode, _ = v["social_code"].(string)
		iterm.Merchant.Address, _ = v["address"].(string)
		iterm.Merchant.ContactName, _ = v["contact_name"].(string)
		iterm.Merchant.ContactNumber, _ = v["contact_number"].(string)
		iterm.Merchant.AuthStatus = int(ormStringToInt(v, "auth_status"))
		iterm.Merchant.MerchantLicense, _ = v["merchant_license"].(string)
		iterm.Merchant.AppKey, _ = v["app_key"].(string)
		iterm.Merchant.AppSecret, _ = v["app_secret"].(string)
		iterm.Merchant.AppPassword, _ = v["app_password"].(string)
		iterm.Merchant.AppPassword = decAppPassword(iterm.Merchant.AppPassword)

		iterm.MerchantId = ormStringToInt(v, "id")
		iterm.ApiId = ormStringToInt(v, "api_id")
		if v["warning_enable"] == "1" {
			iterm.WarningEnable = true
		}
		reply.MerchantList = append(reply.MerchantList, iterm)

	}
	l.Debug(utils.MarshalJsonString(req, reply))

	return
}

func GetStatus(merchantType int, arrearage, balance float64) string {
	if merchantType == 2 {
		if arrearage+balance <= 10 {
			return "余额不足"
		} else {
			return "正常"
		}
	} else if merchantType == 3 {
		if arrearage != 0 {
			if arrearage+balance <= 10 {
				return "余额不足"
			} else {
				return "正常"
			}
		} else {
			return "正常"
		}
	}
	return ""
}

func ManagementGetUserMerchantListNew(ctx context.Context, req *apis.ManagementGetUserMerchantListNewReq, reply *apis.ManagementGetUserMerchantListNewReply) (err error) {
	if req.PageNumber > 0 {
		req.PageNumber -= 1
	} else {
		req.PageNumber = 0
	}
	if req.Status == "" && req.StatusCode > 0 {
		for k, v := range StatusMap {
			if req.StatusCode == v {
				req.Status = k
				break
			}
		}
	}

	sql := `SELECT
    b.id,
	a.name,
	a.phone,
	a.email,
	b.merchant_name,
	b.email as memail,
	b.company_name,
	b.social_code,
	b.address,
	b.contact_name,
	b.contact_number,
	b.merchant_license,
	b.ip_whitelist,
	b.auth_status,
	b.app_key,
	b.app_secret,
	b.app_password,
	b.warning_enable,
	b.is_http_code,
	b.merchant_type,
	b.balance,
	b.arrearage
FROM
	t_gd_user AS a
	INNER JOIN t_gd_merchants AS b ON a.id = b.user_id where b.auth_status=1`
	if req.MerchantId > 0 {
		sql = fmt.Sprintf("%s and b.id=%d", sql, req.MerchantId)
	}

	if req.MerchantType > 0 {
		sql = fmt.Sprintf("%s and b.merchant_type=%d", sql, req.MerchantType)
	}

	sql = fmt.Sprintf("%s order by b.id desc", sql)
	o := orm.NewOrm()
	if req.IsAll {

		_, err = o.Raw(sql).QueryRows(&reply.MerchantList)
		if err != nil {
			l.Error("func",
				zap.String("call", "ManagementGetUserMerchantList"),
				zap.String("args", utils.MarshalJsonString(req)),
				zap.String("error", err.Error()))
			return errors.DataBaseError
		}
	} else {
		if req.Status == "" && req.DataApiId == "" {
			sql = sql + " LIMIT ? OFFSET ?"
			countSql := `select count(1) from t_gd_merchants where auth_status=1`
			if req.MerchantId > 0 {
				countSql = fmt.Sprintf("%s and id=%d", countSql, req.MerchantId)
			}

			if req.MerchantType > 0 {
				countSql = fmt.Sprintf("%s and merchant_type=%d", countSql, req.MerchantType)
			}

			err = o.Raw(countSql).QueryRow(&reply.Total)
			if err != nil {
				l.Error("func",
					zap.String("call", "ManagementGetUserMerchantList"),
					zap.String("args", utils.MarshalJsonString(req)),
					zap.String("error", err.Error()))
				return errors.DataBaseError
			}

			_, err = o.Raw(sql, PAGESIZE, req.PageNumber*PAGESIZE).QueryRows(&reply.MerchantList)
			if err != nil {
				l.Error("func",
					zap.String("call", "ManagementGetUserMerchantList"),
					zap.String("args", utils.MarshalJsonString(req)),
					zap.String("error", err.Error()))
				return errors.DataBaseError
			}
		} else {
			_, err = o.Raw(sql).QueryRows(&reply.MerchantList)
			if err != nil {
				l.Error("func",
					zap.String("call", "ManagementGetUserMerchantList"),
					zap.String("args", utils.MarshalJsonString(req)),
					zap.String("error", err.Error()))
				return errors.DataBaseError
			}
		}

	}

	for index, _ := range reply.MerchantList {
		reply.MerchantList[index].AppPassword = decAppPassword(reply.MerchantList[index].AppPassword)
		sql := `SELECT ( SELECT data_api_name FROM t_gd_data_api WHERE  id = 
				(select data_api_id from t_gd_data_api_query_type where id = a.query_type_id)) AS data_api_name,
				( SELECT id FROM t_gd_data_api WHERE  id = 
				(select data_api_id from t_gd_data_api_query_type where id = a.query_type_id)) AS id,
				case a.combo_type when 2 then ( SELECT b.count FROM t_gd_api_access_count AS b WHERE
				b.merchant_data_api_id = a.id and  create_time = ? limit 1) else  ( SELECT IFNULL(b.count,0) 
				FROM t_gd_api_access_count AS b WHERE b.merchant_data_api_id = a.id and  create_time = ? limit 1) 
				end as use_count,a.combo_type, a.count_per_day as day_count,a.count as total_count,a.end_time,a.query_type_id as  query_type_id,a.alias FROM t_gd_merchant_data_api as a where a.merchant_id = ?`
		zeroDate := "0000-00-00"
		nowDate := time.Now().Format("2006-01-02")
		_, err = o.Raw(sql, nowDate, zeroDate, reply.MerchantList[index].Id).QueryRows(&reply.MerchantList[index].DataApiInfo)
		if err != nil {
			if err == orm.ErrNoRows {
				continue
			}
			l.Error("func",
				zap.String("call", "ManagementGetUserMerchantList"),
				zap.String("args", utils.MarshalJsonString(req)),
				zap.String("error", err.Error()))
			return errors.DataBaseError
		}
		now := time.Now().Unix()
		for i, _ := range reply.MerchantList[index].DataApiInfo {
			if reply.MerchantList[index].DataApiInfo[i].ComboType == 2 {
				if reply.MerchantList[index].DataApiInfo[i].EndTime > int64(now) {
					if (reply.MerchantList[index].DataApiInfo[i].EndTime-int64(now))%(3600*24) == 0 {
						reply.MerchantList[index].DataApiInfo[i].LeftDay = int64((reply.MerchantList[index].DataApiInfo[i].EndTime - int64(now)) / (3600 * 24))

					} else {
						reply.MerchantList[index].DataApiInfo[i].LeftDay = int64((reply.MerchantList[index].DataApiInfo[i].EndTime-int64(now))/(3600*24)) + 1

					}
				} else {
					reply.MerchantList[index].DataApiInfo[i].LeftDay = 0
				}
				reply.MerchantList[index].DataApiInfo[i].LeftCount = reply.MerchantList[index].DataApiInfo[i].DayCount - reply.MerchantList[index].DataApiInfo[i].UseCount
				if reply.MerchantList[index].DataApiInfo[i].LeftDay > 0 && reply.MerchantList[index].DataApiInfo[i].LeftDay <= 1 {
					reply.MerchantList[index].DataApiInfo[i].Status = "即将过期"
				} else if reply.MerchantList[index].DataApiInfo[i].LeftDay <= 0 {
					reply.MerchantList[index].DataApiInfo[i].Status = "过期"
				} else if reply.MerchantList[index].DataApiInfo[i].LeftCount <= 0 {
					reply.MerchantList[index].DataApiInfo[i].Status = "达到单日上限"
				} else if reply.MerchantList[index].DataApiInfo[i].LeftCount > 0 && reply.MerchantList[index].DataApiInfo[i].LeftCount <= 10 {
					reply.MerchantList[index].DataApiInfo[i].Status = "即将达到单日上限"
				} else {
					reply.MerchantList[index].DataApiInfo[i].Status = "正常"
				}

			} else {

				reply.MerchantList[index].DataApiInfo[i].LeftCount = reply.MerchantList[index].DataApiInfo[i].TotalCount - reply.MerchantList[index].DataApiInfo[i].UseCount
				if reply.MerchantList[index].DataApiInfo[i].LeftCount <= 0 {
					reply.MerchantList[index].DataApiInfo[i].Status = "用量耗尽"
				} else if reply.MerchantList[index].DataApiInfo[i].LeftCount > 0 && reply.MerchantList[index].DataApiInfo[i].LeftCount <= 10 {
					reply.MerchantList[index].DataApiInfo[i].Status = "用量即将耗尽"
				} else {
					reply.MerchantList[index].DataApiInfo[i].Status = "正常"
				}

				if reply.MerchantList[index].DataApiInfo[i].ComboType == 0 {
					var is_free int
					o.Raw("select is_free from t_gd_data_api_query_type where id = ?", reply.MerchantList[index].DataApiInfo[i].QueryTypeId).QueryRow(&is_free)
					if is_free == 1 {
						reply.MerchantList[index].DataApiInfo[i].Status = "正常"
					}
				}
			}
			if reply.MerchantList[index].DataApiInfo[i].LeftDay < 0 {
				reply.MerchantList[index].DataApiInfo[i].LeftDay = 0
			}
			if reply.MerchantList[index].DataApiInfo[i].LeftCount < 0 {
				reply.MerchantList[index].DataApiInfo[i].LeftCount = 0
			}

			nowStats := GetStatus(reply.MerchantList[index].MerchantType, reply.MerchantList[index].Arrearage, reply.MerchantList[index].Balance)
			if nowStats != "" {
				reply.MerchantList[index].DataApiInfo[i].Status = nowStats
			}

			reply.MerchantList[index].DataApiInfo[i].StatusCode, _ = StatusMap[reply.MerchantList[index].DataApiInfo[i].Status]

		}
	}
	if req.DataApiId == "" && req.Status == "" {
		reply.PageSize = PAGESIZE
		reply.PageNumber = req.PageNumber + 1
		return
	}
	newList := []apis.UserMerchantItemNew{}
	total := 0

	for _, v := range reply.MerchantList {
		if len(v.DataApiInfo) == 0 {
			//newList = append(newList, v)
			continue
		}
		items := []apis.DataApiInfo{}
		for _, d := range v.DataApiInfo {
			if req.DataApiId != "" && req.Status != "" {
				if req.DataApiId == fmt.Sprintf("%d", d.Id) && req.Status == d.Status {
					items = append(items, d)
				}
			} else if req.DataApiId != "" && req.DataApiId == fmt.Sprintf("%d", d.Id) {
				items = append(items, d)
			} else {
				if req.Status == d.Status {
					items = append(items, d)
				}
			}
		}

		if len(items) > 0 {

			if total >= req.PageNumber*PAGESIZE && total < (req.PageNumber+1)*PAGESIZE {
				v.DataApiInfo = items
				newList = append(newList, v)
			}

			total++
		}

	}
	reply.Total = total
	reply.MerchantList = newList
	reply.PageSize = PAGESIZE
	reply.PageNumber = req.PageNumber + 1
	l.Debug(utils.MarshalJsonString(req, reply))

	return
}