ngds/common/helpers/bonus/Calc/PullCalcBonusData.php

<?php

namespace common\helpers\bonus\Calc;

use common\components\ActiveRecord;
use common\helpers\Date;
use common\models\CalcBonus;
use common\models\CalcBonusBD;
use common\models\CalcBonusBS;
use common\models\CalcBonusGarage;
use common\models\CalcBonusGL;
use common\models\CalcBonusGX;
use common\models\CalcBonusQuarter;
use common\models\CalcBonusQY;
use common\models\CalcBonusStandard;
use common\models\CalcBonusTG;
use common\models\CalcBonusTourism;
use common\models\CalcBonusVilla;
use common\models\CalcBonusVIP;
use common\models\CalcBonusYC;
use common\models\CalcBonusYJ;
use common\models\CalcRecord;
use common\models\ScoreMonth;
use common\models\User;
use common\models\UserBonus;
use common\models\UserInfo;

/**
 * 拉取计算完成的奖金数据
 */
class PullCalcBonusData extends BaseBusiness
{
    const BASE_INFO_METHODS = [
        'calcBonus' => ['type' => 'separately', 'table' => 'AR_CALC_BONUS'],

        'calcBonusBs'      => ['type' => 'same', 'table' => 'AR_CALC_BONUS_BS'],
        'calcBonusQuarter' => ['type' => 'same', 'table' => 'AR_CALC_BONUS_QUARTER'],
        'calcBonusTourism' => ['type' => 'same', 'table' => 'AR_CALC_BONUS_TOURISM'],

        'AR_BS_BONUS_103_CALC_1'        => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_BS_BONUS_103_CALC_1', 'condition_type' => 'id', 'condition_field' => 'CALC_PERIOD_ID', 'param_name' => '_periodNum'],
        'AR_BS_BONUS_103_CALC_EVENT'    => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_BS_BONUS_103_CALC_EVENT', 'condition_type' => 'id', 'condition_field' => 'CALC_PERIOD_ID', 'param_name' => '_periodNum'],
        'AR_BS_BONUS_103_CALC_NET'      => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_BS_BONUS_103_CALC_NET', 'condition_type' => 'id', 'condition_field' => 'CALC_PERIOD_ID', 'param_name' => '_periodNum'],
        'AR_BS_BONUS_103_CALC_NET_1'    => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_BS_BONUS_103_CALC_NET_1', 'condition_type' => 'id', 'condition_field' => 'CALC_PERIOD_ID', 'param_name' => '_periodNum'],
        'AR_BS_BONUS_103_TEST'          => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_BS_BONUS_103_TEST', 'condition_type' => 'id', 'condition_field' => 'CALC_PERIOD_ID', 'param_name' => '_periodNum'],
        'AR_BS_BONUS_103_TEST_NET'      => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_BS_BONUS_103_TEST_NET', 'condition_type' => 'id', 'condition_field' => 'CALC_PERIOD_ID', 'param_name' => '_periodNum'],
        'AR_CALC_BONUS_BS_CALC'         => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_CALC_BONUS_BS_CALC'],
        'AR_CALC_BONUS_BS_CHECKING'     => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_CALC_BONUS_BS_CHECKING'],
        'AR_CALC_BONUS_BS_DETAIL'       => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_CALC_BONUS_BS_DETAIL'],
        'AR_CALC_BONUS_BS_DETAIL_BONUS' => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_CALC_BONUS_BS_DETAIL_BONUS'],
        'AR_CALC_BONUS_BS_DETAIL_CALC'  => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_CALC_BONUS_BS_DETAIL_CALC'],
        'AR_CALC_BONUS_BS_DETAIL_GPV'   => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_CALC_BONUS_BS_DETAIL_GPV'],
        'AR_CALC_BONUS_BS_STAT'         => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_CALC_BONUS_BS_STAT'],

        'AR_QTR_CALC_RECORD' => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_QTR_CALC_RECORD', 'condition_type' => 'id', 'condition_field' => 'CALC_PERIOD_ID', 'param_name' => '_periodNum'],
        'AR_QTR_CALC_SCORE'  => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_QTR_CALC_SCORE', 'condition_type' => 'custom', 'condition' => 'group'],//2个 where
        'AR_QTR_CALC_USER'   => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_QTR_CALC_USER'],
        'AR_QTR_CALC_WEIGHT' => ['type' => 'same', 'general_clear' => true, 'table' => 'AR_QTR_CALC_WEIGHT'],

        'calcBonusBd'     => ['type' => 'calc_month', 'table' => 'AR_CALC_BONUS_BD'],
        'calcBonusQy'     => ['type' => 'calc_month', 'table' => 'AR_CALC_BONUS_QY'],
        'calcBonusTg'     => ['type' => 'calc_month', 'table' => 'AR_CALC_BONUS_TG'],
        'calcBonusVilla'  => ['type' => 'calc_month', 'table' => 'AR_CALC_BONUS_VILLA'],
        'calcBonusGarage' => ['type' => 'calc_month', 'table' => 'AR_CALC_BONUS_GARAGE'],
    ];

    public function __construct($periodNum)
    {
        parent::__construct($periodNum);
    }

    public function start(): array
    {
        $db = $this->_calc_db_name;
        try {
            //清除本期原有数据，可多次拉取
//            \Yii::$app->$db->createCommand()->delete($table, "PERIOD_NUM = $this->_periodNum")->execute();
            $this->clearCalcTableData();

            foreach (self::BASE_INFO_METHODS as $method => $info) {
                $table = $info['table'];

                if ('same' == $info['type']) {
                    $_offset = 0;
                    $countSameBonus = 0;
                    sameBonus:
                    $offset = $_offset * $this->_limit;
                    if (isset($info['condition_type']) && $info['condition_type'] == 'id' && isset($info['param_name'])) {
                        $param = $info['param_name'];
                        $data = \Yii::$app->$db->createCommand("SELECT * from $table  where " . $info['condition_field'] . '=' . $this->$param . " limit $this->_limit offset $offset;")->queryAll();
                    } elseif (isset($info['condition_type']) && $info['condition_type'] == 'custom' && $info['condition'] == 'group') {
                        $data = \Yii::$app->$db->createCommand("SELECT * from $table where CALC_MONTH = $this->_calcMonth and CALC_YEAR=$this->_calcYear limit $this->_limit offset $offset;")->queryAll();
                    } else {
                        $data = \Yii::$app->$db->createCommand("SELECT * from $table where PERIOD_NUM = $this->_periodNum limit $this->_limit offset $offset;")->queryAll();
                    }
                    if (!empty($data)) {
                        $fieldArray = array_keys($data[0]);
                        $_offset    += 1;
                        $countSameBonus += count($data);
                        \Yii::$app->db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
                        $data = null;
                        goto sameBonus;
                    }
                    CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，".$table."表，插入".$countSameBonus."条记录");
                    $data = null;
                    continue;
                }

                if ('calc_month' == $info['type']) {
                    $_offset = 0;
                    $countCalcMonth = 0;
                    calcMonth:
                    $offset = $_offset * $this->_limit;
                    $data   = \Yii::$app->$db->createCommand("SELECT *,FROM_UNIXTIME(CREATED_AT,'%Y-%m-%d') as P_CALC_MONTH from $table where PERIOD_NUM = $this->_periodNum limit $this->_limit offset $offset;")->queryAll();
                    if (!empty($data)) {
                        $fieldArray = array_keys($data[0]);
                        $_offset    += 1;
                        $countCalcMonth += count($data);
                        \Yii::$app->db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
                        $data = null;
                        goto calcMonth;
                    }
                    CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，".$table."表，插入".$countCalcMonth."条记录");
                    $data = null;
                    continue;
                }

                if ('separately' == $info['type'] && method_exists($this, $method)) {
                    $this->$method();
                    continue;//必要，避免增加逻辑代码后忘记此处
                }
            }
            //拉取期业绩
            $res = (new PullPerfDataFromCalc($this->_periodNum))->start();
            if (200 != $res['code']) {
                CalcRecord::record($this->_periodNum, '第' . $this->_periodNum . '期的奖金计算后的期业绩数据获取失败，原因：' . $res['msg']);
            }
        } catch (\Exception $e) {
            return $this->fail('msg:' . $e->getMessage() . 'line:' . $e->getLine());
        }
        //同步周期表的值到业务系统
        self::pullPeriodForUpdate($this->_periodNum);
        return $this->success();
    }

    public function calcBonus()
    {
        $repeatField = [
//            'ORI_CAPPED_BONUS_QY' => ['CAPPED_BONUS_QY'],
            'ORI_BONUS_QY' => ['BONUS_QY', 'SHOULD_QY'],

            'ORI_BONUS_BS' => ['BONUS_BS', 'REAL_BONUS_BS', 'BONUS_BS_ABBR', 'ORI_BONUS_BS_ABBR', 'REAL_BONUS_BS_ABBR'],//

            'ORI_BONUS_QUARTER' => ['BONUS_QUARTER'],
            'ORI_BONUS_TOURISM' => ['BONUS_TOURISM'],
            'ORI_BONUS_GARAGE'  => ['BONUS_GARAGE'],
            'ORI_BONUS_VILLA'   => ['BONUS_VILLA'],
            'ORI_BONUS_BD'      => ['BONUS_BD'],
            'ORI_BONUS_TG'      => ['BONUS_TG'],
        ];

        $db         = $this->_calc_db_name;
        $table      = self::BASE_INFO_METHODS['calcBonus']['table'];
        $pCalcMonth = Date::ociToDate($this->_calcYearMonth, Date::OCI_TIME_FORMAT_SHORT_MONTH);

        $calcPeriod   = $this->getCalcPeriod();
        $calculatedAt = $calcPeriod['CALCULATED_AT'];

        $_offset      = 0;
        $countCalcBonus = 0;
        $this->_limit = 2000;
        calcBonus:

        $offset = $_offset * $this->_limit;

        $data = \Yii::$app->$db->createCommand("SELECT
            b.*,
            1 as LAST_LOCATION,
            $calculatedAt as CALCULATED_AT,
            '$pCalcMonth' as P_CALC_MONTH
            from $table as b 
        where b.PERIOD_NUM = $this->_periodNum limit $this->_limit offset $offset;")->queryAll();

        if (!empty($data)) {
            $_offset += 1;
            $countCalcBonus += count($data);
            $data    = array_column($data, null, 'USER_ID');
            $userIds = array_keys($data);
            //获取用户信息相关的数据 在下方循环内以名为$user的变量整合
            $userInfo = array_column(self::getUserInfoByUserIds($userIds), null, 'ID');
            //获取用户积分相关的数据
            $userPointsInfo = array_column(self::getUserPointsByUserIds($userIds), null, 'ID');

            foreach ($data as $userId => $value) {
                if (isset($userInfo[$userId])) {
                    $user                       = $userInfo[$userId];
                    $user['LAST_REC_USER_NAME'] = $user['recUserName']['USER_NAME'] ?? '';
                    $user['LAST_REC_REAL_NAME'] = $user['recRealName']['REAL_NAME'] ?? '';
                    $user['LAST_CON_USER_NAME'] = $user['conUserName']['USER_NAME'] ?? '';
                    $user['LAST_CON_REAL_NAME'] = $user['conRealName']['REAL_NAME'] ?? '';
                    $user['LAST_SYSTEM_ID']     = $user['LAST_SYSTEM_ID'] ?? '';
                    $user['EXCHANGE_POINTS']    = $userPointsInfo[$userId] ?? 0;

                    //将ori开头的奖金项目赋值到实发奖金字段
                    $value = self::formatDataByParams($value, $repeatField);
                    unset($user['ID'], $user['REC_UID'], $user['CON_UID'], $user['recUserName'], $user['recRealName'],
                        $user['conUserName'], $user['conRealName'],
                        $value['ORI_BONUS_TOURISM'], $value['ORI_BONUS_GARAGE'], $value['ORI_BONUS_VILLA']);

                    //整合数据
                    $data[$userId] = array_merge($value, $user);
                }
            }

            $fieldArray = array_keys(current($data));
            \Yii::$app->db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
            $userInfo = null;
            $data     = null;
            goto calcBonus;
        }
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，".$table."表，插入".$countCalcBonus."条记录");
        $data = null;
        return true;

    }

    /**
     * @param array $userIds
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getUserInfoByUserIds(array $userIds): array
    {
        if (empty($userIds)) {
            return [];
        }
        return UserInfo::find()->alias('UI')
            ->join('INNER JOIN', User::tableName() . ' AS U', 'U.ID = UI.USER_ID')
            ->where(['in', 'U.ID', $userIds])
            ->select('
                U.ID,U.STATUS AS LAST_STATUS,U.MOBILE AS LAST_MOBILE,U.PERIOD_AT AS LAST_PERIOD_AT,U.CREATED_AT AS LAST_CREATED_AT,
            U.SUB_COM_ID AS LAST_SUB_COM_ID,U.PROVINCE AS LAST_PROVINCE,U.CITY AS LAST_CITY,U.COUNTY AS LAST_COUNTY,
            U.IS_DIRECT_SELLER AS LAST_IS_DIRECT_SELLER,UI.SYSTEM_ID AS LAST_SYSTEM_ID,UI.REC_UID,UI.CON_UID')
            ->with([
                'recUserName' => function ($query) {
                    $query->select('USER_ID,USER_NAME');
                },
                'recRealName' => function ($query) {
                    $query->select('ID,REAL_NAME');
                },
                'conUserName' => function ($query) {
                    $query->select('USER_ID,USER_NAME');
                },
                'conRealName' => function ($query) {
                    $query->select('ID,REAL_NAME');
                }
            ])
            ->asArray()
            ->all();
    }

    /**
     * @param array $userIds
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getUserPointsByUserIds(array $userIds): array
    {
        if (empty($userIds)) {
            return [];
        }
        return UserBonus::find()->where(['in', 'USER_ID', $userIds])
            ->select('EXCHANGE_POINTS,USER_ID')
            ->asArray()
            ->all();
    }

    /**
     * @param array $data
     * @param array $format
     * @return array
     */
    public static function formatDataByParams(array $data, array $format): array
    {
        if (empty($data) || empty($format)) {
            return [];
        }
        foreach ($data as $field => $v) {
            if (isset($format[$field])) {
                foreach ($format[$field] as $formatField) {
                    $data[$formatField] = $v;
                }
            }
        }
        return $data;
    }

    /**
     * 清空相关表数据
     */
    public function clearCalcTableData()
    {
        // 奖金表
        CalcBonus::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_CALC_BONUS表，当期数据已清空");
        CalcBonusBS::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_CALC_BONUS_BS表，当期数据已清空");
        CalcBonusQuarter::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_CALC_BONUS_QUARTER表，当期数据已清空");

        CalcBonusBD::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_CALC_BONUS_BD表，当期数据已清空");
        CalcBonusQY::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_CALC_BONUS_QY表，当期数据已清空");
        CalcBonusTG::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_CALC_BONUS_TG表，当期数据已清空");
//        CalcBonusYJ::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
//        CalcBonusGX::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
//        CalcBonusGL::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);

        //AR_BS_BONUS_103_CALC_EVENT 因每期的主键id相同，故需每次拉取前清理
        \Yii::$app->db->createCommand("TRUNCATE TABLE AR_BS_BONUS_103_CALC_EVENT")->execute();
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_BS_BONUS_103_CALC_EVENT表，已清空");
        //AR_QTR_CALC_RECORD 因每期的主键id相同，故需每次拉取前清理
        \Yii::$app->db->createCommand("TRUNCATE TABLE AR_QTR_CALC_RECORD")->execute();
        CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，AR_QTR_CALC_RECORD表，已清空");

        foreach (self::BASE_INFO_METHODS as $info) {
            if ('same' == $info['type'] && isset($info['general_clear']) && $info['general_clear']) {
                if (isset($info['condition_type']) && $info['condition_type'] == 'id' && isset($info['param_name'])) {
                    $param = $info['param_name'];
                    $res   = self::pageDeleteAll($info['table'], $info['condition_field'] . '=' . $this->$param);
                    CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，".$info['table']."表，已清除当期数据");
                } else if (isset($info['condition_type']) && $info['condition_type'] == 'custom' && $info['condition'] == 'group') {
                    $res = self::pageDeleteAll($info['table'], "CALC_MONTH = $this->_calcMonth and CALC_YEAR=$this->_calcYear");
                    CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，".$info['table']."表，已清除当月数据");
                } else {
                    $res = self::pageDeleteAll($info['table'], 'PERIOD_NUM=' . $this->_periodNum);
                    CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，".$info['table']."表，已清除当期数据");
                }

                if (!$res) {
                    CalcRecord::record($this->_periodNum, '【奖金计算】业务系统，第' . $this->_periodNum . '期的' . $info['table'] . '表清理失败');
                }
            }
        }

        // 月结时要清空的数据
        if ($this->_isCalcMonth) {
//            CalcBonusYC::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
//            CalcBonusVIP::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
//            CalcBonusStandard::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
//            ScoreMonth::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
            CalcBonusTourism::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
            CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，是月结点，AR_CALC_BONUS_TOURISM表，已清除当月数据");
            CalcBonusGarage::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
            CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，是月结点，AR_CALC_BONUS_GARAGE表，已清除当月数据");
            CalcBonusVilla::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
            CalcRecord::record($this->_periodNum, "【奖金计算】业务系统，是月结点，AR_CALC_BONUS_VILLA表，已清除当月数据");
        }
    }


    /**
     * @param $table
     * @param $where
     * @return bool
     * @see ActiveRecord::pageDeleteAll()
     */
    public static function pageDeleteAll($table = null, $where = '')
    {
        if (empty($table)) {
            return false;
        }
        $limit = 10000;
        $sql   = sprintf('DELETE FROM %s WHERE %s LIMIT %d', $table, $where, $limit);
        try {
            $affectRow = \Yii::$app->db->createCommand($sql)->execute();
        } catch (\Exception $e) {
            return false;
        }
        if ($affectRow == $limit) {
            unset($limit, $sql, $affectRow);
            return self::pageDeleteAll($table, $where);
        }

        unset($limit, $sql, $affectRow);
        return true;
    }

}