jyOrderManager/internal/logic/order/list.go

package order

import (
	"app.yhyue.com/moapp/jybase/common"
	"context"
	"fmt"
	"github.com/gogf/gf/v2/frame/g"
	"github.com/gogf/gf/v2/util/gconv"
	"jyOrderManager/internal/jyutil"
	"jyOrderManager/internal/model"
	"log"
	"strings"
)

func List(ctx context.Context, param model.OrderListParams) (map[string]interface{}, error) {
	var (
		orderSql, productArr   []string
		productSql, channelSql string
		adminId                = jyutil.GetUserMsgFromCtx(ctx).EntUserId
	)
	if param.Page <= 0 {
		param.Page = 1
	}
	if param.Size <= 0 || param.Size > 100 {
		param.Size = 50
	}
	switch param.ListSource { //默认全部订单
	case 1: //我的订单
		orderSql = append(orderSql, fmt.Sprintf(`(a.salesperson_entUserId ='%d' or a.order_code in (SELECT ordercode FROM order_sale_record WHERE ent_userId = %d and  state in (1,2) GROUP BY ordercode))`, adminId, adminId))
	case 2: //代用户下单
		// 我的订单、高翔、沈炳义查看所有得帮助用户下单的订单
		productSql = ` INNER JOIN (SELECT DISTINCT(order_code)  FROM jy_order_detail WHERE product_type = 'VIP订阅' and status =1) ptc on ptc.order_code = a.order_code `
		orderSql = append(orderSql, " a.del_status = 0 and  a.order_channel = 'xdqd04' ")
		var isHelpList bool
		for _, s := range g.Cfg().MustGet(ctx, "request_auth").Int64s() {
			if s == adminId {
				isHelpList = false
				break
			}
		}
		if !isHelpList { //查看个人
			orderSql = append(orderSql, fmt.Sprintf(`(a.salesperson_entUserId ='%d' or a.order_code in (SELECT ordercode FROM order_sale_record WHERE ent_userId = %d and state in (1,2) GROUP BY ordercode))`, adminId, adminId))
			//orderSql = append(orderSql, fmt.Sprintf(" f.ent_userId= '%d' ", adminId))
		}
	default: //默认全部订单
		//	SalesChannelClass1 string `json:"sales_channel_class1"` //一级销售渠道
		//		SalesChannelClass2 string `json:"sales_channel_class2"` //二级销售渠道
		//		PayMoneyMin        int    `json:"payMoneyMin"`          //实付金额最小
		//		PayMoneyMax        int    `json:"payMoneyMax"`          //实付金额最大
		//		PayType            int    `json:"payType"`              //付费类型
		//		PayWay             string `json:"payWay"`               //付款方式
		//		AuditStatus        int    `json:"auditStatus"`          // 审核状态
		//		ContractStatus     int    `json:"contractStatus"`       //合同状态
		if param.SalesChannelClass1 != "" { //一级销售渠道
			if param.SalesChannelClass2 == "" { //二级销售渠道
				//根据销售渠道一级分类代码查询二级分类
				class, _ := g.DB().Query(ctx, fmt.Sprintf(`SELECT item_code FROM dict_item WHERE parent_code = '%s'`, param.SalesChannelClass1))
				if !class.IsEmpty() {
					var distributionChannel []string
					for _, val := range class.List() {
						distributionChannel = append(distributionChannel, fmt.Sprintf("'%s'", gconv.String(val["item_code"])))
					}
					channelSql = fmt.Sprintf(` INNER JOIN (
						   SELECT ordercode FROM order_sale_record WHERE distribution_channel in (%s) and  state in (1,2) GROUP BY ordercode
						) f ON a.order_code = f.ordercode `, strings.Join(distributionChannel, " or "))
				}
			} else {
				channelSql = fmt.Sprintf(` INNER JOIN (
						   SELECT ordercode FROM order_sale_record WHERE distribution_channel = '%s' and  state in (1,2) GROUP BY ordercode
						) f ON a.order_code = f.ordercode `, param.SalesChannelClass2)
			}
		}

		if param.PayMoneyMin >= 0 { //实付金额最小
			orderSql = append(orderSql, fmt.Sprintf(" a.pay_money>=%d ", param.PayMoneyMin))
		}
		if param.PayMoneyMax >= 0 { //实付金额最大
			orderSql = append(orderSql, fmt.Sprintf(" a.pay_money<= %d", param.PayMoneyMax))
		}
		// 0  支付宝	1 微信	2 线下支付	3 对公转账  4其他    全部传空
		switch param.PayWay {
		case "0":
			orderSql = append(orderSql, " locate('ali', a.pay_way) > 0 ")
		case "1":
			orderSql = append(orderSql, " locate('wx', a.pay_way) > 0 ")
		case "2":
			orderSql = append(orderSql, "  a.pay_way = '线下支付' ")
		case "3":
			orderSql = append(orderSql, " a.pay_way = 'transferAccounts'  ")
		case "4":
			orderSql = append(orderSql, " locate('ali', a.pay_way) = 0 and locate('wx', a.pay_way) = 0 and  a.pay_way != '线下支付' and a.pay_way != 'transferAccounts' and  a.pay_way != '测试使用'  ")
		case "5":
			orderSql = append(orderSql, " a.pay_way = '测试使用'  ")
		}

		if param.AuditStatus > 0 { // 	1 微信	2 线下支付	3 对公转账
			orderSql = append(orderSql, fmt.Sprintf(` a.course_status = %d `, param.AuditStatus))
		}
		if param.PayType != "" {
			productArr = append(productArr, fmt.Sprintf(` service_type = %s `, param.PayType))
		}

		switch param.ContractStatus { //协议上传
		case 1: //未归档
			orderSql = append(orderSql, `e.contract_status is null or e.contract_status = 0`)
		case 2: //已归档
			orderSql = append(orderSql, `e.contract_status = 1`)
		}
	}
	if len(param.ProductType) > 0 {
		productArr = append(productArr, fmt.Sprintf(" product_type in ('%s') ", strings.Join(param.ProductType, "','")))
	}
	if len(productArr) > 0 {
		productSql = fmt.Sprintf(` INNER JOIN (SELECT DISTINCT(order_code)  FROM jy_order_detail WHERE %s and status =1) ptc on ptc.order_code = a.order_code `, strings.Join(productArr, " and "))
	}
	if param.SearchContent != "" {
		orderSql = append(orderSql, fmt.Sprintf(`  (a.order_code = '%s' or a.company_name LIKE '%s' or a.user_phone LIKE '%s')`,
			param.SearchContent, `%`+param.SearchContent+`%`, `%`+param.SearchContent+`%`))
	}
	if len(param.CourseStatus) > 0 {
		var statusArr []string
		for _, status := range param.CourseStatus {
			switch status {
			case 4: //已退回
				statusArr = append(statusArr, "-2,-3,-4")
			case 5: //审核通过
				statusArr = append(statusArr, "4")
			default:
				statusArr = append(statusArr, gconv.String(status))
			}
		}
		orderSql = append(orderSql, fmt.Sprintf(" a.audit_status in (%s)", strings.Join(statusArr, ",")))
	}

	if len(param.OrderStatus) > 0 && len(param.OrderStatus) != 3 {
		orderSql = append(orderSql, fmt.Sprintf(" a.order_status in (%s)", strings.Join(param.OrderStatus, ",")))
	}

	if len(param.ReturnStatus) > 0 && len(param.ReturnStatus) != 3 {
		var isAllReturn bool
		for _, status := range param.ReturnStatus {
			if status == "1" {
				isAllReturn = true
				break
			}
		}
		if isAllReturn { //兼容线上已支付订单
			orderSql = append(orderSql, fmt.Sprintf("( a.return_status in (%s) or (a.order_status =1 and a.is_backstage_order =0) )", strings.Join(param.ReturnStatus, ",")))
		} else {
			orderSql = append(orderSql, fmt.Sprintf(" a.return_status in (%s)", strings.Join(param.ReturnStatus, ",")))

		}
	}

	if len(param.RefundStatus) > 0 && len(param.RefundStatus) != 3 {
		orderSql = append(orderSql, fmt.Sprintf(" a.refund_status in (%s)", strings.Join(param.RefundStatus, ",")))
	}

	/*switch len(param.InvoiceType) { //存在单选或2个选项是进行判断 全选或不选不进行where
	case 1: //单选发票方式
		for _, s := range param.InvoiceType {
			switch s {
			case 1: //未开票
				orderSql = append(orderSql, `b.order_code is null`)
			case 2: //部分开票
				orderSql = append(orderSql, `(b.order_code is not null and  IFNULL(b.invoiced_amount, 0) < a.pay_money - a.commission)`)
			case 3:
				orderSql = append(orderSql, `(b.order_code is not null and  IFNULL(b.invoiced_amount, 0) = a.pay_money - a.commission)`)
			}
		}
	case 2: //多选发票方式
		var isOne bool
		for _, s := range param.InvoiceType {
			if s == 1 {
				isOne = true
			}
		}
		if isOne { //存在未开发票选项
			for _, s := range param.InvoiceType {
				switch s {
				case 2: //部分开票
					orderSql = append(orderSql, `IFNULL(b.invoiced_amount, 0) < a.pay_money - a.commission - IFNULL(g.orderMoney, 0)`)
				case 3:
					orderSql = append(orderSql, `(b.order_code is null or  IFNULL(b.invoiced_amount, 0) = a.pay_money - a.commission - IFNULL(g.orderMoney, 0))`)
				}
			}
		} else { //不存在未开发票选项
			orderSql = append(orderSql, `(b.order_code is not null and  IFNULL(b.invoiced_amount, 0) <= a.pay_money - a.commission - IFNULL(g.orderMoney, 0))`)
		}
	}*/
	//发票状态；0 未申请  1 已申请  -2 已冲红（退款后发票冲红） 3 部分开票
	if len(param.InvoiceType) > 0 {
		var invoiceSql []string
		for _, iType := range param.InvoiceType {
			switch iType {
			case -2:
				invoiceSql = append(invoiceSql, " (a.refund_status = 1 and a.applybill_status = 1) ")
			default:
				invoiceSql = append(invoiceSql, fmt.Sprintf(" a.applybill_status =%d ", iType))
			}
		}
		orderSql = append(orderSql, fmt.Sprintf(` (%s) `, strings.Join(invoiceSql, " or ")))
	}

	if len(param.ContractType) == 1 {
		var statusArr []string
		for _, s := range param.ContractType {
			switch s {
			case 1: //未归档
				statusArr = append(statusArr, `e.contract_archive_status is null or e.contract_archive_status = 0`)
			case 2: //已归档
				statusArr = append(statusArr, `e.contract_archive_status = 1`)
			}
		}
		orderSql = append(orderSql, fmt.Sprintf("(%s)", strings.Join(statusArr, " or ")))
	}
	if param.OrderTimeStart != "" {
		orderSql = append(orderSql, fmt.Sprintf("a.create_time>='%s'", param.OrderTimeStart))
	}
	if param.OrderTimeEnd != "" {
		orderSql = append(orderSql, fmt.Sprintf("a.create_time<='%s'", param.OrderTimeEnd))
	}
	sqlWhere := `SELECT 
	%s
FROM 
    dataexport_order a 
	%s
	%s
LEFT JOIN (
    SELECT 
        order_code,
        SUM(invoice_money) AS invoiced_amount
    FROM 
        invoice
    WHERE 
        invoice_status = 1 and invoice_changed = 0
    GROUP BY 
        order_code
) b ON a.order_code = b.order_code 
LEFT JOIN (
    SELECT 
        order_code,
        SUM(return_money) AS return_money
    FROM 
        return_money_record
    WHERE 
        state = 1
    GROUP BY 
        order_code
) c ON a.order_code = c.order_code
LEFT JOIN (
    SELECT 
        order_code,
        SUM(refund_money) AS refund_money
    FROM 
        refund_record
    GROUP BY 
        order_code
) d ON a.order_code = d.order_code
LEFT JOIN contract e ON a.order_code = e.order_code 
LEFT JOIN (
    SELECT 
        orderCode,
        SUM(orderMoney) AS orderMoney
    FROM 
        moneyCorrection
    GROUP BY 
        orderCode
) g ON a.order_code = g.orderCode   
    LEFT JOIN entniche_user h on a.salesperson_entUserId = h.id
    %s`
	var whereSql string
	if len(orderSql) > 0 {
		whereSql = fmt.Sprintf(" where %s", strings.Join(orderSql, " and "))
	}
	//列表
	sql := fmt.Sprintf(sqlWhere, " a.id,\n  a.order_code,\n a.personPhone,\n a.personName,\n  a.create_time,\n a.order_money,\n  a.company_name,\n    a.user_phone,\n    a.user_nickname,\n    a.buy_subject,\n    a.audit_status,\n    a.order_status,\n    a.pay_money - IFNULL(g.orderMoney, 0) as pay_money,\n    a.commission,\n    a.return_status,\n    IFNULL(c.return_money, 0) as return_money,\n    IFNULL(b.invoiced_amount, 0) as invoiced_amount,\n    a.refund_status,\n    IFNULL(d.refund_money, 0) as refund_money,\n    e.contract_status,\n    e.contract_time,\n    e.contract_archive_status,\n    e.contract_archive_time,\n  h.name as ent_user_name", productSql, channelSql, whereSql)
	//数量
	sqlCount := fmt.Sprintf(sqlWhere, " count(1) ", productSql, channelSql, whereSql)
	//合同总金额
	sqlSum := fmt.Sprintf(sqlWhere, "  sum(pay_money) as pay_money_sum  ", productSql, channelSql, whereSql)

	log.Println("list count sql :", sqlCount)
	log.Println("list sql :", sql)
	count, err := g.DB().GetCount(ctx, sqlCount)
	if err != nil {
		log.Println("count err:=", err.Error())
		return nil, err
	}
	var sortClassify string
	switch param.SortClassify {
	case 1:
		sortClassify = " a.pay_money "
	case 2:
		sortClassify = " return_money "
	case 3:
		sortClassify = " invoiced_amount "
	case 4:
		sortClassify = " refund_money "
	case 5:
		sortClassify = " e.contract_time "
	case 6:
		sortClassify = " e.contract_archive_time "
	default:
		sortClassify = " a.create_time "
	}

	list, err := g.DB().Query(ctx, fmt.Sprintf(`%s order by %s %v LIMIT %d,%d`, sql, sortClassify, common.If(param.SortType == 1, " asc ", " desc "), (param.Page-1)*param.Size, param.Size))
	if err != nil {
		log.Println("list err:=", err.Error())
		return nil, err
	}
	var (
		listArr     []map[string]interface{}
		payMoneySum int
	)
	for _, m := range list.List() {
		//代用户下单为线上单子 付款金额order_money
		if param.ListSource == 2 && m["pay_money"] == nil {
			m["pay_money"] = m["order_money"]
		}
		//未退款金额
		remainingAmount := gconv.Int(m["return_money"])
		returnAmount := gconv.Int(m["return_money"])
		if gconv.Int(m["is_backstage_order"]) == 0 && gconv.Int(m["order_status"]) == 1 &&
			gconv.Int(m["pay_money"]) > 0 && gconv.Int(m["return_status"]) == 0 {
			remainingAmount = gconv.Int(m["pay_money"])
			returnAmount = gconv.Int(m["pay_money"])
			m["return_money"] = returnAmount
		}

		//未回款金额
		m["no_return_money"] = gconv.Int(m["pay_money"]) - returnAmount
		m["no_refund_money"] = remainingAmount - gconv.Int(m["refund_money"])
		//开票
		noInvoicedAmount := gconv.Int(m["pay_money"]) - gconv.Int(m["invoiced_amount"])
		m["no_invoiced_amount"] = common.If(noInvoicedAmount < 0, 0, noInvoicedAmount) //未开票金额
		if gconv.Int(m["invoiced_amount"]) != 0 {
			m["invoiced_status"] = common.If(noInvoicedAmount <= 0, 2, 3)
		} else {
			m["invoiced_status"] = 0 //未开票
		}

		jyOrderDetail, _ := g.DB().Query(ctx, fmt.Sprintf(`SELECT jod.*,jpi.name FROM jy_order_detail jod 
LEFT JOIN jy_product_item jpi on jod.product_code = jpi.code
WHERE jod.order_code = '%s' and jod.status =1 order by  jod.final_price desc, jod.id desc`, gconv.String(m["order_code"])))
		if !jyOrderDetail.IsEmpty() {
			switch param.ListSource {
			case 1: //我的订单获取所有关联订单名称
				var productType []string
				for _, m2 := range jyOrderDetail.List() {
					if gconv.String(m2["productType"]) == "大会员" {
						productType = append(productType, gconv.String(m2["name"]))
					} else {
						productType = append(productType, gconv.String(m2["product_type"]))
					}
				}
				m["product_type"] = strings.Join(productType, ",")
			default:
				m["service_type"] = jyOrderDetail.List()[0]["service_type"]
				if gconv.String(jyOrderDetail.List()[0]["productType"]) == "大会员" {
					m["product_type"] = gconv.String(jyOrderDetail.List()[0]["name"])
				} else {
					m["product_type"] = gconv.String(jyOrderDetail.List()[0]["product_type"])
				}
			}
		}
		listArr = append(listArr, m)
	}
	sumData, _ := g.DB().GetOne(ctx, sqlSum)
	if !sumData.IsEmpty() {
		payMoneySum = gconv.Int(sumData.Map()["pay_money_sum"])
	}
	return map[string]interface{}{
		"count":       count,
		"list":        listArr,
		"payMoneySum": payMoneySum,
	}, nil
}