ngds/common/models/CalcBonus.php

<?php

namespace common\models;

use Yii;

/**
 * This is the model class for table "{{%CALC_BONUS}}".
 *
 * @property string $ID
 * @property string $USER_ID 会员ID
 * @property string $LAST_USER_NAME 结算时会员编号
 * @property string $LAST_REAL_NAME 结算时会员姓名
 * @property string $LAST_DEC_LV 结算时会员级别
 * @property string $LAST_EMP_LV 结算时会员聘级
 * @property int $LAST_STATUS 结算时状态
 * @property string $LAST_MOBILE 结算时手机号码
 * @property int $LAST_PERIOD_AT 结算时加入期数
 * @property int $LAST_CREATED_AT 结算时加入时间
 * @property string $LAST_SUB_COM_ID 结算时所属分公司
 * @property int $LAST_PROVINCE 结算时所属省份
 * @property int $LAST_CITY 结算时所属城市
 * @property int $LAST_COUNTY 结算时所属地区
 * @property string $LAST_SYSTEM_ID 结算时体系
 * @property int $LAST_IS_DIRECT_SELLER 结算时是否直销员
 * @property string $LAST_REC_USER_NAME 结算时开拓者编号
 * @property string $LAST_REC_REAL_NAME 结算时开拓者姓名
 * @property string $LAST_CON_USER_NAME 结算时上级编号
 * @property string $LAST_CON_REAL_NAME 结算时上级姓名
 * @property int $LAST_LOCATION 结算时市场
 * @property string $BONUS_QY 区域津贴
 * @property string $BONUS_YC 育成津贴
 * @property string $BONUS_VIP VIP奖
 * @property string $BONUS_FX 复销奖
 * @property string $BONUS_LS 零售奖
 * @property string $BONUS_FW 服务奖
 * @property string $BONUS_BT 店补
 * @property string $BONUS_BT_PROD 产品补贴
 * @property string $BONUS_BT_TOOL 工具流补贴
 * @property string $DEDUCT_ZR 责任业绩扣除
 * @property string $BONUS_FL 形象店货补
 * @property string $BONUS_CF 车房养老奖
 * @property string $BONUS_LX 领袖分红
 * @property string $SHOULD_QY 应发区域津贴
 * @property string $SHOULD_DEDUCT_ZR 应扣责任业绩
 * @property string $PV_1L 一市场新增业绩
 * @property string $QY_1L 一市场区域业绩
 * @property string $SURPLUS_1L 一市场结余业绩
 * @property string $PV_2L 二市场新增业绩
 * @property string $QY_2L 二市场区域业绩
 * @property string $SURPLUS_2L 二市场结余业绩
 * @property string $PV_3L 三市场新增业绩
 * @property string $QY_3L 三市场区域业绩
 * @property string $SURPLUS_3L 三市场结余业绩
 * @property string $PV_4L 四市场新增业绩
 * @property string $QY_4L 四市场区域业绩
 * @property string $SURPLUS_4L 四市场结余业绩
 * @property string $PV_5L 五市场新增业绩
 * @property string $QY_5L 五市场区域业绩
 * @property string $SURPLUS_5L 五市场结余业绩
 * @property string $PV_PCS 当期零售
 * @property string $PV_LS_TOUCH 虚拟零售区新增业绩
 * @property string $SURPLUS_LS 虚拟零售区结余
 * @property string $QY_LS 虚拟零售区区域业绩
 * @property string $PV_TOUCH 新增业绩累计
 * @property string $BONUS_REAL 实际奖金
 * @property string $BONUS_TOTAL 总奖金
 * @property string $BONUS_INCOME 总收入
 * @property int $PERIOD_NUM 结算期数
 * @property int $CALC_YEAR 所在结算年
 * @property int $CALC_MONTH 所在结算月
 * @property int $CALCULATED_AT 结算时间
 * @property int $IS_SENT 是否挂网
 * @property int $SENT_AT 挂网时间
 * @property string $P_CALC_MONTH 表分区的日期索引
 * @property int $CREATED_AT 创建时间
 *
 * @property string $ORI_BONUS_QY 团队奖原奖金
 * @property string $ORI_BONUS_QY_BD 报单团队奖原奖金
 * @property string $ORI_BONUS_QY_FX 复消团队奖原奖金
 * @property string $ORI_BONUS_YC 荣衔奖奖原奖金
 * @property string $ORI_BONUS_VIP VIP奖奖原奖金
 * @property string $ORI_BONUS_STANDARD 达标奖原奖金
 * @property string $BONUS_BD 服务奖
 * @property string $ORI_BONUS_BD 服务奖原奖金
 * @property string $BONUS_TG 推广奖
 * @property string $ORI_BONUS_TG 推广奖原奖金
 * @property string $BONUS_XF 消费奖
 * @property string $ORI_BONUS_XF 消费奖原奖金
 * @property string $BONUS_YJ 业绩奖
 * @property string $ORI_BONUS_YJ 业绩奖原奖金
 * @property string $ORI_BONUS_YJ_BD 报单业绩奖原奖金
 * @property string $ORI_BONUS_YJ_FX 复消业绩奖原奖金
 * @property string $BONUS_GX 共享奖
 * @property string $ORI_BONUS_GX 共享奖原奖金
 * @property string $BONUS_BONUS_GX 实发共享奖奖金
 * @property string $BONUS_GL 管理奖
 * @property string $ORI_BONUS_GL 管理奖原奖金
 * @property string $BONUS_BONUS_GL 实发管理奖奖金
 * @property string $ORI_BONUS_GL_BD 报单管理奖原奖金
 * @property string $ORI_BONUS_GL_FX 复消管理奖原奖金
 * @property string $RECONSUME_POINTS 复消积分
 * @property string $MANAGE_TAX 管理费 
 * @property string $BONUS_BS 蓝星奖金(即新的管理奖)
 * @property string $ORI_BONUS_BS 蓝星原奖金(即新的管理奖,原奖金)
 * @property string $BONUS_TOURISM 旅游奖奖金
 * @property string $BONUS_VILLA 房奖奖金
 * @property string $BONUS_GARAGE 车奖奖金
 */
class CalcBonus extends \common\components\ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return '{{%CALC_BONUS}}';
    }

    /**
     * {@inheritdoc}
     */
    public function rules()
    {
        return [
            [['USER_ID', 'LAST_USER_NAME', 'LAST_REAL_NAME', 'LAST_MOBILE', 'LAST_SYSTEM_ID','LAST_LOCATION', 'PERIOD_NUM', 'CALC_YEAR', 'CALC_MONTH', 'CALCULATED_AT', 'P_CALC_MONTH', 'CREATED_AT'], 'required'],
            [['LAST_STATUS', 'LAST_PERIOD_AT', 'LAST_CREATED_AT', 'LAST_PROVINCE', 'LAST_CITY', 'LAST_COUNTY', 'LAST_IS_DIRECT_SELLER', 'LAST_LOCATION', 'PERIOD_NUM', 'CALC_YEAR', 'CALC_MONTH', 'CALCULATED_AT', 'IS_SENT', 'SENT_AT', 'CREATED_AT'], 'integer'],
            [['ORI_BONUS_BS','EXCHANGE_POINTS','BONUS_BS','BONUS_QY', 'BONUS_YC', 'BONUS_VIP', 'BONUS_FX', 'BONUS_LS', 'BONUS_FW', 'BONUS_BT', 'BONUS_BT_PROD', 'BONUS_BT_TOOL', 'DEDUCT_ZR', 'BONUS_FL', 'BONUS_CF', 'BONUS_LX', 'SHOULD_QY', 'SHOULD_DEDUCT_ZR', 'PV_1L', 'QY_1L', 'SURPLUS_1L', 'PV_2L', 'QY_2L', 'SURPLUS_2L', 'PV_3L', 'QY_3L', 'SURPLUS_3L', 'PV_4L', 'QY_4L', 'SURPLUS_4L', 'PV_5L', 'QY_5L', 'SURPLUS_5L', 'PV_PCS', 'PV_LS_TOUCH', 'SURPLUS_LS', 'QY_LS', 'PV_TOUCH', 'BONUS_REAL', 'BONUS_TOTAL', 'BONUS_INCOME', 'ORI_BONUS_QY','ORI_BONUS_QY_BD','ORI_BONUS_QY_FX','ORI_BONUS_YC', 'ORI_BONUS_VIP','ORI_BONUS_STANDARD','BONUS_BD','ORI_BONUS_BD','BONUS_TG','ORI_BONUS_TG','BONUS_XF','ORI_BONUS_XF','BONUS_YJ','ORI_BONUS_YJ','ORI_BONUS_YJ_BD','ORI_BONUS_YJ_FX','BONUS_GX','ORI_BONUS_GX','REAL_BONUS_GX','BONUS_GL','ORI_BONUS_GL','REAL_BONUS_GL','ORI_BONUS_GL_BD','ORI_BONUS_GL_FX','RECONSUME_POINTS','MANAGE_TAX', 'BONUS_TOURISM', 'BONUS_GARAGE', 'BONUS_VILLA'], 'number'],
            [['ID', 'USER_ID', 'LAST_DEC_LV', 'LAST_EMP_LV', 'LAST_SUB_COM_ID', 'LAST_SYSTEM_ID'], 'string', 'max' => 32],
            [['LAST_USER_NAME', 'LAST_REC_USER_NAME', 'LAST_CON_USER_NAME'], 'string', 'max' => 16],
            [['LAST_REAL_NAME', 'LAST_REC_REAL_NAME', 'LAST_CON_REAL_NAME'], 'string', 'max' => 128],
            [['LAST_MOBILE'], 'string', 'max' => 11],
            [['P_CALC_MONTH'], 'safe'],
            [['ID'], 'unique'],
        ];
    }

    /**
     * {@inheritdoc}
     */
    public function attributeLabels()
    {
        return [
            'ID' => 'ID',
            'USER_ID' => '会员ID',
            'LAST_USER_NAME' => '结算时会员编号',
            'LAST_REAL_NAME' => '结算时会员姓名',
            'LAST_DEC_LV' => '结算时会员级别',
            'LAST_EMP_LV' => '结算时会员聘级',
            'LAST_STATUS' => '结算时状态',
            'LAST_MOBILE' => '结算时手机号码',
            'LAST_PERIOD_AT' => '结算时加入期数',
            'LAST_CREATED_AT' => '结算时加入时间',
            'LAST_SUB_COM_ID' => '结算时所属分公司',
            'LAST_PROVINCE' => '结算时所属省份',
            'LAST_CITY' => '结算时所属城市',
            'LAST_COUNTY' => '结算时所属地区',
            'LAST_SYSTEM_ID' => '结算时体系',
            'LAST_IS_DIRECT_SELLER' => '结算时是否直销员',
            'LAST_REC_USER_NAME' => '结算时开拓者编号',
            'LAST_REC_REAL_NAME' => '结算时开拓者姓名',
            'LAST_CON_USER_NAME' => '结算时上级编号',
            'LAST_CON_REAL_NAME' => '结算时上级姓名',
            'LAST_LOCATION' => '结算时市场',
            'BONUS_QY' => '区域津贴',
            'BONUS_YC' => '荣衔奖',
            'BONUS_VIP' => 'VIP奖',
            'BONUS_FX' => '复销奖',
            'BONUS_LS' => '零售奖',
            'BONUS_FW' => '服务奖',
            'BONUS_BT' => '店补',
            'BONUS_BT_PROD' => '产品补贴',
            'BONUS_BT_TOOL' => '工具流补贴',
            'DEDUCT_ZR' => '责任业绩扣除',
            'BONUS_FL' => '形象店货补',
            'BONUS_CF' => '车房养老奖',
            'BONUS_LX' => '领袖分红',
            'SHOULD_QY' => '应发区域津贴',
            'SHOULD_DEDUCT_ZR' => '应扣责任业绩',
            'PV_1L' => '一市场新增业绩',
            'QY_1L' => '一市场区域业绩',
            'SURPLUS_1L' => '一市场结余业绩',
            'PV_2L' => '二市场新增业绩',
            'QY_2L' => '二市场区域业绩',
            'SURPLUS_2L' => '二市场结余业绩',
            'PV_3L' => '三市场新增业绩',
            'QY_3L' => '三市场区域业绩',
            'SURPLUS_3L' => '三市场结余业绩',
            'PV_4L' => '四市场新增业绩',
            'QY_4L' => '四市场区域业绩',
            'SURPLUS_4L' => '四市场结余业绩',
            'PV_5L' => '五市场新增业绩',
            'QY_5L' => '五市场区域业绩',
            'SURPLUS_5L' => '五市场结余业绩',
            'PV_PCS' => '当期零售',
            'PV_LS_TOUCH' => '虚拟零售区新增业绩',
            'SURPLUS_LS' => '虚拟零售区结余',
            'QY_LS' => '虚拟零售区区域业绩',
            'PV_TOUCH' => '新增业绩累计',
            'BONUS_REAL' => '实际奖金',
            'BONUS_TOTAL' => '总奖金',
            'BONUS_INCOME' => '总收入',
            'PERIOD_NUM' => '结算期数',
            'CALC_YEAR' => '所在结算年',
            'CALC_MONTH' => '所在结算月',
            'CALCULATED_AT' => '结算时间',
            'IS_SENT' => '是否挂网',
            'SENT_AT' => '挂网时间',
            'P_CALC_MONTH' => '表分区的日期索引',
            'CREATED_AT' => '创建时间',
            'ORI_BONUS_QY'=> '团队奖原奖金',
            'ORI_BONUS_QY_BD'=> '报单团队奖原奖金',
            'ORI_BONUS_QY_FX'=> '复消团队奖原奖金',
            'ORI_BONUS_YC'=> '荣衔奖原奖金',
            'ORI_BONUS_VIP'=> 'VIP奖原奖金',
            'ORI_BONUS_STANDARD'=> '达标奖原奖金',
            'BONUS_BD'=> '服务奖',
            'ORI_BONUS_BD'=> '服务奖原奖金',
            'BONUS_TG'=> '推广奖',
            'ORI_BONUS_TG'=> '推广奖原奖金',
            'BONUS_XF'=> '消费奖',
            'ORI_BONUS_XF'=> '消费奖原奖金',
            'BONUS_YJ'=> '业绩奖',
            'ORI_BONUS_YJ'=> '业绩奖原奖金',
            'ORI_BONUS_YJ_BD'=> '报单业绩奖原奖金',
            'ORI_BONUS_YJ_FX'=> '复消业绩奖原奖金',
            'BONUS_GX'=> '共享奖',
            'ORI_BONUS_GX'=> '共享奖原奖金',
            'REAL_BONUS_GX'=> '实发共享奖奖金',
            'BONUS_GL'=> '管理奖',
            'ORI_BONUS_GL'=> '管理奖原奖金',
            'REAL_BONUS_GL'=> '实发管理奖奖金',
            'ORI_BONUS_GL_BD'=> '报单管理奖原奖金',
            'ORI_BONUS_GL_FX'=> '复消管理奖原奖金',
            'RECONSUME_POINTS'=> '复消积分',
            'MANAGE_TAX'=> '管理费',
            'EXCHANGE_POINTS' => '兑换积分',
            'BONUS_BS' => '蓝星奖',
            'ORI_BONUS_BS' => '蓝星原奖金',
            'BONUS_BS_MNT' => '蓝星管理奖',
            'ORI_BONUS_BS_MNT' => '蓝星管理奖原奖金',
            'BONUS_BS_ABBR' => '蓝星业绩奖',
            'ORI_BONUS_BS_ABBR' => '蓝星业绩奖原奖金',
            'BONUS_TOURISM' => '旅游奖奖金',
            'BONUS_GARAGE' => '车奖奖金',
            'BONUS_VILLA' => '房奖奖金',
        ];
    }

    /**
     * 近几期的奖金
     * @param $userId
     * @param int $num
     * @return array
     * @throws \yii\db\Exception
     */
    public static function periodBonus($userId, $num = 7){
        // 获取近几期的年月
        $periodYearMonths = Period::getNearlyPeriodYearMonth($num);
        $allBonus = [];
        foreach($periodYearMonths as $data){
            $allBonus[] = static::findUseSlaves()->yearMonth($data['yearMonth'])->select('USER_ID,BONUS_QY, BONUS_YC, BONUS_FX, BONUS_LS, BONUS_FW, BONUS_BT, BONUS_BT_PROD, BONUS_BT_TOOL, DEDUCT_ZR, BONUS_FL, BONUS_CF, BONUS_LX, SHOULD_QY,BONUS_TOURISM,BONUS_GARAGE,BONUS_VILLA')->where('USER_ID=:USER_ID AND PERIOD_NUM=:PERIOD_NUM AND IS_SENT=1', [':USER_ID'=>$userId, ':PERIOD_NUM'=>$data['periodNum']])->asArray()->one();
        }
        return $allBonus;
    }

    /**
     * 获取期数对应的奖金情况
     * @param $userId
     * @param $periodNum
     * @return array|null|\yii\db\ActiveRecord
     * @throws \yii\db\Exception
     */
    public static function periodNumBonus($userId, $periodNum){
        $period = Period::instance();
        $yearMonth = $period->getYearMonth($periodNum);
        return static::findUseSlaves()->yearMonth($yearMonth)->where('USER_ID=:USER_ID AND PERIOD_NUM=:PERIOD_NUM', [':USER_ID'=>$userId, ':PERIOD_NUM'=>$periodNum])->select('USER_ID,BONUS_QY, BONUS_YC, BONUS_TG, BONUS_YJ, BONUS_GX, BONUS_GL, ORI_BONUS_QY, ORI_BONUS_YC, ORI_BONUS_TG, ORI_BONUS_YJ, ORI_BONUS_GX, ORI_BONUS_GL, RECONSUME_POINTS, MANAGE_TAX, BONUS_REAL, BONUS_TOTAL, BONUS_INCOME')->asArray()->one();
    }

    /**
     * 根据期数获取月累计奖金
     * @param $userId
     * @param $periodNum
     * @param string $type
     * @return int|mixed
     * @throws \yii\db\Exception
     */
    public static function monthBonus($userId, $periodNum=null, $type = 'BONUS_QY') {
        if($periodNum) {
            $period = Period::instance();
            $yearMonth = $period->getYearMonth($periodNum);
            if ($yearMonth) {
                $bonus = CalcBonus::findUseSlaves()->yearMonth($yearMonth)->select('SUM(' . $type . ') AS ' . $type . '_SUM')->where('USER_ID=:USER_ID AND CALC_MONTH=:CALC_MONTH AND IS_SENT=1', [':USER_ID' => $userId, ':CALC_MONTH' => $yearMonth])->asArray()->one();
            }
        }else{
            $bonus = CalcBonus::findUseSlaves()->select('SUM(' . $type . ') AS ' . $type . '_SUM')->where('USER_ID=:USER_ID AND IS_SENT=1', [':USER_ID' => $userId])->asArray()->one();
        }
        return $bonus[$type . '_SUM'] ?? 0;
    }
}