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

<?php
/**
 * Created by PhpStorm.
 * User: leo
 * Date: 2018/8/2
 * Time: 上午10:38
 */

namespace common\helpers\bonus\Calc;

use common\components\ActiveRecord;
use common\models\CalcBonus;
use common\models\CalcBonusBS;
use common\models\CalcBonusQY;
use common\models\EmployLevel;
use common\models\forms\DeclarationForm;
use common\models\PerfMonth;
use common\models\PerfOrder;
use common\models\PerfPeriod;
use common\models\PerfStandard;
use common\models\Period;

class PushBaseDataToCalc extends BaseBusiness
{
    const BASE_INFO_METHODS = [
        //--- 用户表
        'user'             => ['separately' => true, 'table' => 'AR_USER', 'field' => [
            'ID',
            'USER_NAME',
            'REAL_NAME',
            'CREATED_AT',
            'STATUS',
            'DEC_LV',
            'EMP_LV',
            'CROWN_LV',
            'IS_DEC',
            'DEC_ID',
            'DEC_ROLE_ID',
            'LAST_DEC_LV',
            'IS_STUDIO',
            'LAST_CROWN_LV',
            'DELETED',
        ]],
        //--- 用户信息表
        'userInfo'         => ['table' => 'AR_USER_INFO', 'field' => [
            'ID',
            'USER_ID',
            'USER_NAME',
            'CON_UID',
            'REC_UID',
            'CREATED_AT',
        ]],

        //---配置表初始化数据见数据库是否开启管理奖，使用的是openbs进行控制
        'config'           => ['table' => 'AR_CONFIG', 'field' => [
            'CONFIG_NAME',
            'TITLE',
            'UNIT',
            'INPUT_TYPE',
            'OPTIONS',
            'VALUE',
            'TYPE',
            'SORT',
            'CREATED_AT',
            'UPDATED_AT',
        ]],
        //--- 报单级别配置表
        'declarationLevel' => ['table' => 'AR_DECLARATION_LEVEL', 'field' => [
            'ID',
            'LEVEL_NAME',
            'PERF',
            'QY_PERCENT',
            'QY_TOUCH_CAP',
            'QY_BIG_CAP',
            'SORT',
            'CREATED_AT',
            'INCOME_CAP',
        ]],
        //--- 级别配置表
        'employLevel'      => ['table' => 'AR_EMPLOY_LEVEL', 'field' => [
            'ID',
            'LEVEL_NAME',
            'CREATED_AT',
            'BS_PERCENT',
            'LEVEL_SORT',
            'TOURISM_PERCENT',
            'GARAGE_PERCENT',
            'ACHIEVE_PV',
            'ACHIEVE_MEMBER_NUM',
            'ACHIEVE_PERF_PV',
        ], 'alias'                     => [
            'LEVEL_SORT' => 'SORT',
        ]],
        //--- 报单中心级别配置表
        'decRole'          => ['table' => 'AR_DEC_ROLE', 'field' => [
            'ID',
            'ROLE_NAME',
            'FW_BONUS_PERCENT',
            'SORT',
            'CREATED_AT',
        ]],
        //--- 安置网络表
        'userNetwork'      => ['table' => 'AR_USER_NETWORK_NEW', 'field' => [
            'ID',
            'USER_ID',
            'PARENT_UID',
//            'LOCATION_TAG',
            'RELATIVE_LOCATION',
//            'TOP_UID',
//            'TOP_DEEP',
//            'PARENT_UIDS',
            'CREATED_AT',
//            'UPDATED_AT',
        ]],

        //--- 星级配置表
        'starCrownLevel'   => ['table' => 'AR_CROWN_LEVEL', 'field' => [
            'ID',
            'LEVEL_NAME',
            'MIN_LEVEL_ID',
            'LEVEL_SCORE',
            'UPGRADE_SCORE',
            'SORT',
            'TOURISM_PERCENT',
            'VILLA_PERCENT',
            'GARAGE_PERCENT',
            'CREATED_AT',
        ]],
        //--- 业绩单表
        'perfOrder'        => ['separately' => true, 'table' => 'AR_PERF_ORDER', 'field' => [
            'ID',
            'SN',
//            'PERF_TYPE',
            'DEC_TYPE',
            'USER_ID',
            'PV',
            'PERIOD_NUM',
            'CALC_MONTH',
            'DEC_USER_ID',
            'CREATED_AT',
            'ORDER_AMOUNT',
            'PAY_TYPE',
            'FROM_TABLES',
            'PAY_TYPE'
        ]],
        //--- 用户结余业绩表
        'userPerf'         => ['table' => 'AR_USER_PERF', 'field' => [
            'ID',
            'USER_ID',
            'PV_PCS_ZC',
            'PV_PCS_FX',
            'PV_1L',
            'PV_2L',
            'PV_3L',
            'PV_4L',
            'PV_5L',
            'CREATED_AT',
            'PV_PSS',
            'SURPLUS_1L',
            'SURPLUS_1L_ZC',
            'SURPLUS_1L_FX',
            'SURPLUS_2L',
            'SURPLUS_2L_ZC',
            'SURPLUS_2L_FX',
            'SURPLUS_3L',
            'SURPLUS_3L_ZC',
            'SURPLUS_3L_FX',
            'SURPLUS_4L',
            'SURPLUS_4L_ZC',
            'SURPLUS_4L_FX',
            'SURPLUS_5L',
            'SURPLUS_5L_ZC',
            'SURPLUS_5L_FX',
            'PV_PSS_TOTAL',
        ]],
        //--- 团队奖明细表, 月节点的时候，需要将这个结算月的所有数据都同步过来
        'calcBonusQy'      => ['separately' => true, 'table' => 'AR_CALC_BONUS_QY', 'field' => [
            'ID',
            'USER_ID',
            'LAST_DEC_LV',
            'LAST_EMP_LV',
            'LAST_CROWN_LV',
            'LAST_STATUS',
            'AMOUNT',
            'PERIOD_NUM',
            'CALC_YEAR',
            'CALC_MONTH',
            'LOGS',
            'CREATED_AT',
            'ORI_BONUS',
            'RECONSUME_POINTS',
            'MANAGE_TAX',
            'ORI_CAPPED_BONUS_QY',
            'IS_ACTIVE',
            'HOPE_CROWN_LV',
            'HOPE_BONUS',
        ]],
        //--- 用户结算月累计复消积分表，此表为新表，不传数据即代表没有扣除复消积分。 统计此结算月扣除的复消费积分总数，根据calc_bonus表 sum 一下复消积分字段即可。
        'deductReconsume'  => ['separately' => true, 'table' => 'AR_DEDUCT_RECONSUME', 'field' => [
            'ID',
            'USER_ID',
            'CALC_MONTH',
            'RECONSUME_POINTS_SUM',
            'CREATED_AT',
        ]],
        //--- 期业绩表数据. 月节点的时候，计算月业绩，需要业务系统将ar_perf_period表此结算月的所有数据同步过来。
        'perfPeriod'       => ['separately' => true, 'table' => 'AR_PERF_PERIOD', 'field' => [
            'ID',
            'USER_ID',
            'FX_AMOUNT_CASH',
            'PV_PCS',
            'PV_PCS_ZC',
            'PV_PCS_FX',
            'PV_PCS_FX_CASH',
            'PV_PCS_FX_POINT',
            'PV_1L',
            'PV_1L_TOUCH',
            'PV_1L_ZC',
            'PV_1L_FX',
            'PV_2L',
            'PV_2L_TOUCH',
            'PV_2L_ZC',
            'PV_2L_FX',
            'PV_3L',
            'PV_3L_TOUCH',
            'PV_3L_ZC',
            'PV_3L_FX',
            'PV_4L',
            'PV_4L_TOUCH',
            'PV_4L_ZC',
            'PV_4L_FX',
            'PV_5L',
            'PV_5L_TOUCH',
            'PV_5L_ZC',
            'PV_5L_FX',
            'SURPLUS_1L',
            'SURPLUS_1L_ZC',
            'SURPLUS_1L_FX',
            'SURPLUS_2L',
            'SURPLUS_2L_ZC',
            'SURPLUS_2L_FX',
            'SURPLUS_3L',
            'SURPLUS_3L_ZC',
            'SURPLUS_3L_FX',
            'SURPLUS_4L',
            'SURPLUS_4L_ZC',
            'SURPLUS_4L_FX',
            'SURPLUS_5L',
            'SURPLUS_5L_ZC',
            'SURPLUS_5L_FX',
            'PERIOD_NUM',
            'CALC_MONTH',
            'CREATED_AT',
            'PV_PSS',
        ]],
        //--- 期数配置表
        'period'           => ['separately' => true, 'table' => 'AR_PERIOD', 'field' => [
            'ID',
            'PERIOD_NUM',
            'CALC_YEAR',
            'CALC_MONTH',
            'IS_MONTH',
            'IS_PERFED',
            'IS_CALCULATED',
            'IS_PERFING',
            'IS_CALCING',
            'PERF_PERCENT',
            'CALC_PERCENT',
            'PERF_STARTED_AT',
            'PERFED_AT',
            'CALCULATE_STARTED_AT',
            'CALCULATED_AT',
            'CREATED_AT',
        ]],
        //--- 月业绩表 只有当前结算月为季度最终月时才推
        'perfMonth'        => ['separately' => true, 'table' => 'AR_PERF_MONTH', 'field' => [
            'ID',
            'USER_ID',
            'FX_AMOUNT_CASH',
            'PV_PCS',
            'PV_PCS_FX',
            'PV_1L',
            'PV_2L',
            'PV_3L',
            'PV_4L',
            'PV_5L',
            'PV_1L_TOTAL',
            'PV_2L_TOTAL',
            'PV_3L_TOTAL',
            'PV_4L_TOTAL',
            'PV_5L_TOTAL',
            'CALC_MONTH',
            'CREATED_AT',
            'PV_PSS',
            'PV_PSS_TOTAL',
        ]],
        //--- 推荐网络表
        'userRelation'     => ['table' => 'AR_USER_RELATION_NEW', 'field' => [
            'ID',
            'USER_ID',
            'PARENT_UID',
            'TOP_DEEP',
            'CREATED_AT',
        ]],
    ];

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

    public function start(): array
    {
        try {
            //清除业务系统本地数据（业绩相关）
            $this->clearBusinessData();
            //清理计算服务系统的基本数据表
            $this->clearSyncCalcTableData();
            //从业务系统推送数据到计算服务系统
            $this->initializeBaseInfo();
//            $db = $this->_calc_db_name;
            //todo 更新进度
//            \Yii::$app->$db->createCommand()->update('AR_PERIOD', ['IS_PREPARE' => 1], 'PERIOD_NUM=:PERIOD_NUM', ['PERIOD_NUM' => $this->_periodNum])->execute();
        } catch (\Exception $e) {
            return $this->fail('msg:' . $e->getMessage() . 'line:' . $e->getLine());
        }
        return $this->success();
    }

    public function clearBusinessData()
    {
        // 周业绩
        PerfPeriod::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        // 业绩单
//        PerfOrder::pageDeleteAll('PERIOD_NUM=' . $this->_periodNum);
        // 删除活跃用户
        // PerfActiveUser::pageDeleteAll('PERIOD_NUM='.$this->_periodNum.'  AND IS_SENT=0 ');
        // 月结时要清空的数据
        if ($this->_isCalcMonth) {
            // 月业绩表
            PerfMonth::pageDeleteAll("CALC_MONTH='{$this->_calcYearMonth}'");
            //达标业绩表
            PerfStandard::pageDeleteAll("CALC_MONTH='{$this->_calcYearMonth}'");
        }
    }

    /**
     * 清理配置中的数据表数据
     * @return array
     */
    public function clearSyncCalcTableData(): array
    {
        $db  = $this->_calc_db_name;
        $res = [];
        foreach (self::BASE_INFO_METHODS as $method => $val) {
            if (\Yii::$app->$db->createCommand("TRUNCATE TABLE {$val['table']}")->execute() !== false) {
                $res[] = $val['table'];
            }
        }
        return $res;
    }

    public function initializeBaseInfo()
    {
        $db = $this->_calc_db_name;

        foreach (self::BASE_INFO_METHODS as $method => $val) {
            $table      = self::BASE_INFO_METHODS[$method]['table'] ?? '';
            $fieldArray = self::BASE_INFO_METHODS[$method]['field'] ?? [];

            if (!empty($table) && !empty($fieldArray)) {
                if (!isset($val['separately'])) {
                    $_offset = 0;
                    A:
                    if (isset($val['alias'])) {
                        //如果业务表字段和计算服务表不同，则配置alias
                        foreach ($val['alias'] as $org => $v) {
                            $index              = array_search($org, $fieldArray);
                            $fieldArray[$index] = $v;
                        }
                    }
                    $field = implode(',', $fieldArray);
                    $model = 'common\models\\' . ucfirst($method);
                    $data  = $model::find()->select($field)->limit($this->_limit)->offset($_offset * $this->_limit)->asArray()->all();
                    if (!empty($data)) {
                        $_offset += 1;
                        \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
                        $data = null;
                        goto A;
                    }
                    $data = null;
                } elseif (method_exists($this, $method) && $val['separately']) {
                    $this->$method($table, $fieldArray, $db);
                }
            }
        }

        return true;
    }

    public function period($table, $fieldArray, $db)
    {
        $field = implode(',', $fieldArray);

        $where = ['PERIOD_NUM' => $this->_periodNum];

        if ($this->_isCalcMonth) {
            $where = ['CALC_YEAR' => $this->_calcYear, 'CALC_MONTH' => $this->_calcMonth];
            if (in_array($this->_calcMonth, [3, 6, 9, 12])) {
                $where['CALC_MONTH'] = [$this->_calcMonth, $this->_calcMonth - 1, $this->_calcMonth - 2];
            }
        }

        $data = Period::find()->select($field)
            ->where($where)
            ->asArray()->all();
        if (!empty($data)) {
            \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
        }
        return true;
    }

    public function user($table, $fieldArray, $db)
    {
        $field        = '`' . implode('`, `', $fieldArray) . '`,null as LAST_EMP_LV';
        $fieldArray[] = 'LAST_EMP_LV';
        $_offset      = 0;

        $forwardMonthPeriod = Period::find()
            ->where('IS_MONTH=1 AND PERIOD_NUM<:PERIOD_NUM', [':PERIOD_NUM' => $this->_periodNum])
            ->orderBy('PERIOD_NUM DESC')->asArray()->one();

        $forwardPeriodNum = $forwardMonthPeriod['PERIOD_NUM'];

//        $employeeLevelIds = CalcBonusBS::findAllAsArray(
//            'PERIOD_NUM=:PERIOD_NUM ', [':PERIOD_NUM' => $forwardPeriodNum]
//        );

        $employeeLevelIds = CalcBonusBS::find()->where(
            'PERIOD_NUM=:PERIOD_NUM ', [':PERIOD_NUM' => $forwardPeriodNum]
        )->select('USER_ID,LEVEL_ID')->asArray()->all();

        if (!empty($employeeLevelIds)) {
            $employeeLevelIds = array_column($employeeLevelIds, null, 'USER_ID'); // 当前蓝星奖计算(即管理奖) 的等级
        }

        $noLevelId = EmployLevel::NO_LEVEL_ID;

        user:
        $offset = $_offset * $this->_limit;
        $data   = ActiveRecord::findBySql("SELECT $field from AR_USER limit $this->_limit offset $offset;")->asArray()->all();

        if (!empty($data)) {
            foreach ($data as $k => $v) {
                $data[$k]['LAST_EMP_LV'] = $noLevelId;
                if (isset($employeeLevelIds[$v['ID']])) {
                    $data[$k]['LAST_EMP_LV'] = $employeeLevelIds[$v['ID']]['LEVEL_ID'] ?? 0;
                }
            }
            $_offset += 1;
            \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();

            $data = null;
            goto user;
        }
        $data             = null;
        $employeeLevelIds = null;

        return true;
    }

    public function calcBonusQy($table, $fieldArray, $db): bool
    {
        if (!$this->_isCalcMonth) {
            return true;
        }
        $periodNum     = $this->_periodNum;
        $currentPeriod = Period::getInfoByPeriodNum($periodNum);
        //判断是否月节点
        if ($currentPeriod['IS_MONTH']) {
            $periodNum = Period::getCurrentMonthPeriodByPeriodNum($periodNum);
        }
        $field = implode(',', $fieldArray);
        $data  = CalcBonusQY::find()->select($field)->where(['PERIOD_NUM' => $periodNum])->asArray()->all();

        \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
        $data = null;
        return true;
    }

    public function deductReconsume($table, $fieldArray, $db): bool
    {
        $data = CalcBonus::find()
            ->where('CALC_MONTH=:CALC_MONTH', [':CALC_MONTH' => $this->_calcYearMonth])
            ->select(['ID', 'USER_ID', 'CALC_MONTH', 'SUM(RECONSUME_POINTS)', 'CREATED_AT'])
            ->groupBy(['USER_ID'])->having(['>', 'SUM(RECONSUME_POINTS)', 0])
            ->asArray()
            ->all();
        if (!empty($data)) {
            \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();

        }
        $data = null;
        return true;
    }

    public function perfPeriod($table, $fieldArray, $db): bool
    {
        if (!$this->_isCalcMonth) {
            return true;
        }
        $field = implode(',', $fieldArray);
        $data  = PerfPeriod::find()
            ->where('CALC_MONTH=:CALC_MONTH', [':CALC_MONTH' => $this->_calcYearMonth])
            ->select($field)
            ->asArray()
            ->all();
        if (!empty($data)) {
            \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
        }
        $data = null;
        return true;
    }

    public function perfOrder($table, $fieldArray, $db): bool
    {
        $_offset = 0;

        $field = "ID,SN,DEC_TYPE, USER_ID,PV,
         PERIOD_NUM, CALC_MONTH,DEC_USER_ID,
         CREATED_AT,DEC_AMOUNT as ORDER_AMOUNT,PAY_TYPE,FROM_TABLES,PAY_TYPE";

        $periodNum     = $this->_periodNum;
        $currentPeriod = Period::getInfoByPeriodNum($periodNum);
        //判断是否月节点
        if ($currentPeriod['IS_MONTH']) {
            $periodNum = Period::getCurrentMonthPeriodByPeriodNum($periodNum);
        }

        //结算月
        perfOrder:
        $offset = $_offset * $this->_limit;
        $data   = PerfOrder::find()->select($field)->where(['PERIOD_NUM' => $periodNum])
            ->offset($offset)->limit($this->_limit)->asArray()->all();

        if (!empty($data)) {
            $_offset    += 1;
            $fieldArray = array_keys(current($data));
            \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
            $data = null;
            goto perfOrder;
        }
        $data = null;
        return true;
    }

    protected function decOrder($calcMonth, $offset = 0): bool
    {
        $field = "ID,ORDER_SN as SN,'ZC' as PERF_TYPE, TO_USER_ID as USER_ID,DEC_PV as PV,
         PERIOD_NUM, '$calcMonth' as CALC_MONTH,USER_ID as DEC_USER_ID,
         CREATED_AT,DEC_AMOUNT as ORDER_AMOUNT, PAID_WALLET as PAY_TYPE,'AR_DEC_ORDER'";

        $data = ActiveRecord::findBySql("SELECT $field from AR_DEC_ORDER;")
            ->where("PERIOD_NUM=:PERIOD_NUM AND IS_DEL=0 AND TYPE='ZC'", [':PERIOD_NUM' => $this->_periodNum])->asArray()->all();

        $this->perfOrderInsert($data);
        $data = null;
        return true;
    }

    protected function orderDec($calcMonth, $offset = 0): bool
    {
        $field = "od.ID,od.SN,'ZC' as TYPE,od.USER_ID,od.PAY_PV,
         od.PERIOD_NUM, '$calcMonth' as CALC_MONTH,
         u.DEC_ID as DEC_USER_ID,
         od.CREATED_AT,od.PAY_AMOUNT as ORDER_AMOUNT, od.PAY_TYPE,'AR_ORDER_DEC'";

        $data = ActiveRecord::findBySql("SELECT $field from AR_ORDER_DEC as od left join AR_USER as u on od.USER_ID=u.ID;")
            ->where("PERIOD_NUM=:PERIOD_NUM AND IS_DEL=0 AND TYPE='ZC'", [':PERIOD_NUM' => $this->_periodNum])->asArray()->all();

        $this->perfOrderInsert($data);
        $data = null;
        return true;
    }

    protected function order($calcMonth, $offset = 0): bool
    {
        $field = "od.ID,od.SN,'FX' as TYPE,od.USER_ID,od.PAY_PV,
         od.PERIOD_NUM, '$calcMonth' as CALC_MONTH,
         '' as DEC_USER_ID,
         od.CREATED_AT,od.PAY_AMOUNT as ORDER_AMOUNT, od.PAY_TYPE,'AR_ORDER'";

        $data = ActiveRecord::findBySql("SELECT $field from AR_ORDER as od;")
            ->where("PERIOD_NUM=:PERIOD_NUM AND IS_DELETE=0 AND ORDER_TYPE=:ORDER_TYPE",
                [':PERIOD_NUM' => $this->_periodNum, ':ORDER_TYPE' => DeclarationForm::TYPE_FX])
            ->asArray()->all();

        $this->perfOrderInsert($data);
        $data = null;
        return true;
    }

    protected function orderShop($calcMonth, $offset = 0): bool
    {
        $field = "od.ID,od.SN,'FX' as TYPE,od.USER_ID,od.PAY_PV,
         od.PERIOD_NUM, '$calcMonth' as CALC_MONTH,
         '' as DEC_USER_ID,
         od.CREATED_AT,od.PAY_AMOUNT as ORDER_AMOUNT, od.PAY_TYPE,'AR_ORDER_SHOP'";

        $data = ActiveRecord::findBySql("SELECT $field from AR_ORDER_SHOP as od;")
            ->where("PERIOD_NUM=:PERIOD_NUM AND IS_DELETE=0 AND ORDER_TYPE=:ORDER_TYPE",
                [':PERIOD_NUM' => $this->_periodNum, ':ORDER_TYPE' => DeclarationForm::TYPE_FX])
            ->asArray()->all();

        $this->perfOrderInsert($data);
        $data = null;
        return true;
    }

    /**
     * @param $data
     * @return void
     */
    private function perfOrderInsert($data): void
    {
        $db         = $this->_calc_db_name;
        $table      = self::BASE_INFO_METHODS['perfOrder']['table'] ?? '';
        $fieldArray = self::BASE_INFO_METHODS['perfOrder']['field'] ?? [];
        \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
    }

    public function perfMonth($table, $fieldArray, $db)
    {
        if (!$this->_isCalcMonth && !in_array($this->_calcMonth, [3, 6, 9, 12])) {
            return true;
        }
        $_offset = 0;
        $field   = implode(',', $fieldArray);

        $months = $this->getMonthsForQuarterly();

        //结算月
        perfMonth:
        $offset = $_offset * $this->_limit;
        $data   = PerfMonth::find()->where(['CALC_MONTH' => $months])
            ->offset($offset)->limit($this->_limit)->select($field)->asArray()->all();

        if (!empty($data)) {
            $_offset    += 1;
            $fieldArray = array_keys(current($data));
            \Yii::$app->$db->createCommand()->batchInsert($table, $fieldArray, $data)->execute();
            $data = null;
            goto perfMonth;
        }
        $data = null;
        return true;
    }
}