xyzshop/test/TestTransferOrder.php

<?php


use PHPUnit\Framework\TestCase;
use const mtopcard\ProvinceList;

define('APP_ID', 'test');
define('BASE_ROOT_PATH', str_replace('/test', '', dirname(__FILE__)));
define('BASE_HELPER_PATH', BASE_ROOT_PATH . '/helper');

require_once(BASE_ROOT_PATH . '/global.php');
require_once(BASE_CORE_PATH . '/lrlz.php');
require_once(BASE_HELPER_PATH . '/task/manager.php');
require_once(BASE_HELPER_PATH . '/task/handler.php');
require_once(BASE_HELPER_PATH . '/task/task_wrapper.php');

const CurrentTest = NetTest;

class TestTransferOrder extends TestCase
{

    public static function setUpBeforeClass(): void
    {
        Base::run_util();
    }

    public function testSql()
    {
//        SELECT refill_order.*, vr_order.order_state, (1741162635 - refill_order.order_time - merchant.time_out) as mtime_out
//        FROM `lrlz_refill_order` AS `refill_order`
//                 INNER JOIN `lrlz_vr_order` AS `vr_order` ON refill_order.order_id = vr_order.order_id
//                 INNER JOIN `lrlz_merchant` AS `merchant` ON refill_order.mchid = merchant.mchid
//        WHERE (refill_order.inner_status = 0)
//        AND (refill_order.mchid IN (10296, 10292, 10297, 10549, 10550))
//          AND (refill_order.order_time >= 1741017600)
//            and (vr_order.order_state in (30,0))
//          and refill_order.is_retrying = 0
//        # and (refill_order.order_sn='2216470794433302240785')
//            AND NOT EXISTS (
//                SELECT 1
//                FROM lrlz_refill_order AS ro
//                INNER JOIN lrlz_vr_order vo
//                    ON ro.order_id=vo.order_id
//                INNER JOIN lrlz_refill_buyback AS rb
//                    ON ro.order_id = rb.order_id
//                WHERE  ro.order_time >= 1741017600 and ro.mch_order = refill_order.mch_order  and vo.order_state=40
//            )
//        ORDER BY refill_order.order_time desc
//        LIMIT 1000;

//        SELECT
//             refill_order.order_id,
//             refill_order.order_sn,
//             refill_order.mchid,
//             refill_order.mch_order,
//             refill_order.card_no,
//            vr_order.order_state,
//            (UNIX_TIMESTAMP() - refill_order.order_time - merchant.time_out) AS mtime_out,
//            MAX(refill_order.order_time) AS max_order_time -- 选取最大 order_time
//        FROM `lrlz_refill_order` AS `refill_order`
//        INNER JOIN `lrlz_vr_order` AS `vr_order`
//            ON refill_order.order_id = vr_order.order_id
//        INNER JOIN `lrlz_merchant` AS `merchant`
//            ON refill_order.mchid = merchant.mchid
//        LEFT JOIN `lrlz_refill_order` AS ro
//            ON refill_order.mch_order = ro.mch_order
//            AND ro.order_time >= 1741017600
//        LEFT JOIN `lrlz_vr_order` AS vo
//            ON ro.order_id = vo.order_id
//            AND vo.order_state = 40
//        LEFT JOIN `lrlz_refill_buyback` AS rb
//            ON ro.order_id = rb.order_id
//        WHERE
//            refill_order.inner_status = 0
//            AND refill_order.mchid IN (10296, 10292, 10297, 10549, 10550)
//            AND refill_order.order_time >= 1741017600
//            AND vr_order.order_state IN (30, 0)
//            AND refill_order.is_retrying = 0
//        GROUP BY refill_order.order_id, refill_order.mch_order, refill_order.mchid, refill_order.card_no, vr_order.order_state,refill_order.order_sn, refill_order.order_time, merchant.time_out
//        HAVING COUNT(DISTINCT CASE WHEN rb.order_id IS NOT NULL THEN ro.order_id END) = 0
//        ORDER BY max_order_time DESC
//        LIMIT 1000;

        $con = [
            'refill_buyback.order_time' => ['egt', 1740758400],
            'refill_order.order_time' => ['egt', 1740758400],
            'vr_order.order_state' => ORDER_STATE_SUCCESS
        ];
        $res = $this->getAllBuybackOrder($con);
    }

    public function getAllBuybackOrder($condition)
    {
        $model = Model('');

        $len = 1000;

        $i = 0;
        $orders = [];
        while (true)
        {
            $start = $i * $len;
            $items = $model->table('refill_buyback,refill_order,vr_order')
                ->on('refill_buyback.order_id=refill_order.order_id,refill_order.order_id=vr_order.order_id')
                ->field("distinct refill_order.mch_order")
                ->join('inner,inner')
                ->where($condition)
                ->limit("{$start},{$len}")
                ->select();
            $orders = array_merge($orders,$items);
            if (empty($items) || count($items) < $len) {
                break;
            }
            $i++;
        }
        return $orders;
    }
}