ZZCX_yii/backend/modules/hotel/models/Report.php

<?php

namespace backend\modules\hotel\models;

use backend\modules\api\models\BaseSupplier;
use backend\modules\hotel\models\search\searchOrderMain;
use backend\modules\zzcs\models\BaseUser;
use common\models\BaseUserAuth;
use Yii;
use yii\data\ActiveDataProvider;
use yii\data\ArrayDataProvider;
use yii\data\SqlDataProvider;
use backend\modules\zzcs\models\OrderFinanceReparations;

class Report extends HotelModel
{
    private $dateArray = [
        1 => 'create_date',
        2 => 'checkin_date',
        3 => 'checkout_date',
    ];
    public $date_type = 1;  //预订时间1 ，入住时间2，离店时间3
    public $start_date;
    public $end_date;
    public $past_start_date;
    public $past_end_date;
    public $province = '';
    public $city = '';
    public $hotel_name = '';
    public $supplier_id = '';//供应商ID
    public $org_sale_id = '0';//渠道商ID
    public $hotel_id = '0';//酒店ID
    public $op = 'getInfo';
    public $user_id;
    public $room_type;
    public $send_user_id = 0;
    public $principal_id = 0;
    public $purchaser_id = 0;
    public $to_url = '';

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['date_type', 'start_date', 'end_date', 'op', 'org_sale_id', 'hotel_name', 'hotel_id'], 'safe'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'ID' => 'ID',
            'CANCEL_FLAG' => 'Cancel  Flag',
            'MAIN_CREATE_USER_ID' => 'Main  Create  User  ID',
            'CREATE_USER_ID' => 'Create  User  ID',
            'CREATE_TIME' => 'Create  Time',
            'UPDATE_USER_ID' => 'Update  User  ID',
            'UPDATE_TIME' => 'Update  Time',
            'SALE_ORG_ID' => 'Sale  Org  ID',
            'SALE_TOP_ORG_ID' => 'Sale  Top  Org  ID',
            'PROD_TOP_ORG_ID' => 'Prod  Top  Org  ID',
            'ORDER_ID' => 'Order  ID',
            'ORDER_DESCRIPTION' => 'Order  Description',
            'OUTSIDE_ORDER_NO' => 'Outside  Order  No',
            'ORDER_VALID_STATUS' => 'Order  Valid  Status',
            'ORDER_BOOK_STATUS' => 'Order  Book  Status',
            'ORDER_PAY_STATUS' => 'Order  Pay  Status',
            'ORDER_PAY_MAIN_ID' => 'Order  Pay  Main  ID',
            'ORDER_PAY_USER_ID' => 'Order  Pay  User  ID',
            'ORDER_PAY_TIME' => 'Order  Pay  Time',
            'ORDER_PRINT_TIMES' => 'Order  Print  Times',
            'PARENT_ORDER_ID' => 'Parent  Order  ID',
            'PROD_ID' => 'Prod  ID',
            'PARENT_PROD_ID' => 'Parent  Prod  ID',
            'PROD_NAME' => 'Prod  Name',
            'PARENT_PROD_NAME' => 'Parent  Prod  Name',
            'ORDER_PRICE' => 'Order  Price',
            'ORDER_PROD_TYPE' => 'Order  Prod  Type',
            'PROD_SUPPLY_ORG_NAME' => 'Prod  Supply  Org  Name',
            'BASE_PRICE' => 'Base  Price',
            'MID_PRICE' => 'Mid  Price',
            'RUN_ID' => 'Run  ID',
            'RUN_DATE' => 'Run  Date',
            'RUN_DATE_NUM' => 'Run  Date  Num',
            'RUN_TIME' => 'Run  Time',
            'RUN_TIME_MINUTES' => 'Run  Time  Minutes',
            'RUN_BUS_ORDER_ID' => 'Run  Bus  Order  ID',
            'RUN_BUS_SEAT_TYPE' => 'Run  Bus  Seat  Type',
            'RUN_BUS_SEAT_SEQ_ID' => 'Run  Bus  Seat  Seq  ID',
            'RUN_BUS_SEAT_ID' => 'Run  Bus  Seat  ID',
            'RUN_BUS_SEAT_NAME' => 'Run  Bus  Seat  Name',
            'PROD_START_STATION_RES_ID' => 'Prod  Start  Station  Res  ID',
            'PROD_START_STATION_RES_NAME' => 'Prod  Start  Station  Res  Name',
            'PROD_START_STATION_CHECKPORT_RES_ID' => 'Prod  Start  Station  Checkport  Res  ID',
            'PROD_START_STATION_CHECKPORT_RES_NAME' => 'Prod  Start  Station  Checkport  Res  Name',
            'PROD_START_STATION_SEQ_ID' => 'Prod  Start  Station  Seq  ID',
            'PROD_START_STATION_DATE' => 'Prod  Start  Station  Date',
            'PROD_START_STATION_DATE_NUM' => 'Prod  Start  Station  Date  Num',
            'PROD_START_STATION_TIME' => 'Prod  Start  Station  Time',
            'PROD_START_STATION_TIME_MINUTES' => 'Prod  Start  Station  Time  Minutes',
            'PROD_START_STATION_AREA_ID' => 'Prod  Start  Station  Area  ID',
            'PROD_START_STATION_AREA_NAME' => 'Prod  Start  Station  Area  Name',
            'PROD_END_STATION_RES_ID' => 'Prod  End  Station  Res  ID',
            'PROD_END_STATION_RES_NAME' => 'Prod  End  Station  Res  Name',
            'PROD_END_STATION_SEQ_ID' => 'Prod  End  Station  Seq  ID',
            'PROD_END_STATION_DATE' => 'Prod  End  Station  Date',
            'PROD_END_STATION_DATE_NUM' => 'Prod  End  Station  Date  Num',
            'PROD_END_STATION_TIME' => 'Prod  End  Station  Time',
            'PROD_END_STATION_TIME_MINUTES' => 'Prod  End  Station  Time  Minutes',
            'PROD_END_STATION_AREA_ID' => 'Prod  End  Station  Area  ID',
            'PROD_END_STATION_AREA_NAME' => 'Prod  End  Station  Area  Name',
            'CUSTOMER_NAME' => 'Customer  Name',
            'CUSTOMER_SEX' => 'Customer  Sex',
            'CUSTOMER_MOBILE' => 'Customer  Mobile',
            'CUSTOMER_ID_TYPE' => 'Customer  Id  Type',
            'CUSTOMER_ID_NO' => 'Customer  Id  No',
            'CUSTOMER_ADDRESS' => 'Customer  Address',
            'CUSTOMER_ADDRESS_AREA_ID' => 'Customer  Address  Area  ID',
            'CUSTOMER_POSTCODE' => 'Customer  Postcode',
            'CUSTOMER_MEMO' => 'Customer  Memo',
            'MEMBER_ID' => 'Member  ID',
            'MEMBER_SCORE_PAY' => 'Member  Score  Pay',
            'MEMBER_SCORE_GET' => 'Member  Score  Get',
            'ORDER_STATUS' => 'Order  Status',
            'ORDER_DISABLE_TYPE' => 'Order  Disable  Type',
            'ORDER_DISABLE_USER_ID' => 'Order  Disable  User  ID',
            'ORDER_DISABLE_TIME' => 'Order  Disable  Time',
            'ORDER_DISABLE_PERCENT' => 'Order  Disable  Percent',
            'ORDER_DISABLE_FEE' => 'Order  Disable  Fee',
            'ORDER_DISABLE_APPLY_USER_ID' => 'Order  Disable  Apply  User  ID',
            'ORDER_DISABLE_APPLY_TIME' => 'Order  Disable  Apply  Time',
            'KEEP_END_TIME' => 'Keep  End  Time',
            'KEEP_END_MINUTES' => 'Keep  End  Minutes',
            'if_gift' => 'If Gift',
            'reorder_times' => 'Reorder Times',
            'IF_LAST_PROD' => 'If  Last  Prod',
            'ORDER_LEVEL' => 'Order  Level',
            'ORDER_CONFIRM_CODE' => 'Order  Confirm  Code',
            'OUTSIDE_SALE_ORG_ID' => 'Outside  Sale  Org  ID',
            'IS_UP' => 'Is  Up',
            'APPLY_ID' => 'Apply  ID',
            'SALES_MAN' => 'Sales  Man',
            'RECEIVE_VALUE' => 'Receive  Value',
            'REAL_PASSENGER_NUM' => 'Real  Passenger  Num',
            'STOCK_TYPE' => 'Stock  Type',
            'REFUSE_FLAG' => 'Refuse  Flag',
            'PROFIT_VALUE' => 'Profit  Value',
            'TOTAL_COMMISSION' => 'Total  Commission',
        ];
    }

    /**
     * User: wangxj
     *
     * 函数作用
     *
     * @params
     */
    public function load($post, $formName = null)
    {
        $this->start_date = isset($_REQUEST['Report']['start_date']) ? $_REQUEST['Report']['start_date'] : date('Y-m-01', time());
        $this->end_date = isset($_REQUEST['Report']['end_date']) ? $_REQUEST['Report']['end_date'] : date('Y-m-d', time());
        $this->op = isset($_REQUEST['op']) ? $_REQUEST['op'] : $this->op;
        $this->hotel_name = isset($_REQUEST['hotel_name']) ? $_REQUEST['hotel_name'] : $this->hotel_name;
        $this->org_sale_id = isset($_REQUEST['org_sale_id']) ? $_REQUEST['org_sale_id'] : $this->org_sale_id;
        $this->hotel_id = isset($_REQUEST['hotel_id']) ? $_REQUEST['hotel_id'] : $this->hotel_id;
        $this->to_url = isset($_REQUEST['to_url']) ? $_REQUEST['to_url'] : $this->to_url;
        $this->send_user_id = isset($_REQUEST['send_user_id']) ? $_REQUEST['send_user_id'] : $this->send_user_id;
        $this->principal_id = isset($_REQUEST['principal_id']) ? $_REQUEST['principal_id'] : $this->principal_id;
        $this->purchaser_id = isset($_REQUEST['purchaser_id']) ? $_REQUEST['purchaser_id'] : $this->purchaser_id;

        return parent::load($post);
    }

    //获取同比日期函数
    public function getBeforeDate($start_date, $end_date)
    {
        //判断所选日期是否为月头和月尾，如果是则比较上一整月,如果不是则减去当月月数-1拼上原来的天数。
        //当月月头
        $day_first = date("Y-m-01", strtotime("$start_date"));
        //当月月尾
        $day_last = date("Y-m-t", strtotime("$start_date"));
        if ($start_date == $day_first && $end_date == $day_last) {
            $timestamp = strtotime($start_date);
            $arr = getdate($timestamp);
            if ($arr['mon'] == 1) {
                $year = $arr['year'] - 1;
                $month = $arr['mon'] + 11;
                $firstday = $year . '-' . $month . '-01';
                $lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
            } else {
                $firstday = date('Y-m-01', strtotime(date('Y', $timestamp) . '-' . (date('m', $timestamp) - 1) . '-01'));
                $lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
            }
        } else {
            $start_arr = getdate(strtotime($start_date));
            $end_arr = getdate(strtotime($end_date));
            if ($start_arr['mon'] == 1) {
                $start_year = $start_arr['year'] - 1;
                $start_month = $start_arr['mon'] + 11;
            } else {
                $start_year = $start_arr['year'];
                $start_month = $start_arr['mon'] - 1;
            }
            if ($end_arr['mon'] == 1) {
                $end_year = $end_arr['year'] - 1;
                $end_month = $end_arr['mon'] + 11;
            } else {
                $end_year = $end_arr['year'];
                $end_month = $end_arr['mon'] - 1;
            }
            if ($start_month < 10)
                $start_month = '0' . $start_month;
            if ($end_month < 10)
                $end_month = '0' . $end_month;
            $firstday = $start_year . '-' . $start_month . '-' . date('d', strtotime("$start_date"));
            $lastday = $end_year . '-' . $end_month . '-' . date('d', strtotime("$end_date"));
            //判断日期是否合法，不合法则不计算同比；
            if (date('Y-m-d', strtotime($firstday)) == $firstday) {
                $result = true;
                if (date('Y-m-d', strtotime($lastday)) == $lastday) {
                    $result = true;
                } else {
                    $result = false;
                }
            } else {
                $result = false;
            }
            //判断end
            if ($result == false) {
                $firstday = '';
                $lastday = '';
            }
        }

        return array($firstday, $lastday);
    }

    /**
     * User:Steven
     * 生成报表数据
     *
     * @return array
     */
    public function updateStatisticsData()
    {
        $sql = "truncate table rep_daily_ht_order;
                insert into rep_daily_ht_order(create_date,checkin_date,checkout_date,create_user_id,create_user_name,purchase_id,purchase_name,top_area_id,parent_area_id,hotel_id,hotel_name,room_name,supplier_id,supplier_name,channel_id,channel_name,order_count,room_count,sales_value,cost_value,commission_value,profit_value)
                select substr(m.create_time,1,10) as create_date,
                       m.prod_start_station_date as checkin_date,
                       m.prod_end_station_date as checkout_date,
                       m.create_user_id,
                       u.user_name,
                       p.id as purchase_id,
                       p.purchaser_name,     
                       a.top_area_id,
                       a.parent_area_id,
                       m.parent_prod_id as hotel_id,
                       m.parent_prod_name as hotel_name,
                       m.prod_id as room_type,
                       m.prod_name as room_name,      
                       m.prod_top_org_id as supplier_id,
                       m.prod_supply_org_name as supplier_name,
                       m.outside_sale_org_id as channel_id,
                       (select supplier_name from base_supplier where id = m.outside_sale_org_id) as channel_name,
                       count(m.id) as order_cnt,     
                       sum((select count(*) from order_main where parent_order_id = m.order_id and cancel_flag = 0 and order_valid_status = 1)) as room_cnt,
                       sum(order_price) as order_price,
                       sum(order_price-total_commission-profit_value) as order_cost,
                       sum(total_commission) as order_commission,
                       sum(profit_value) as order_profit
                from order_main m inner join 
                     opera_hotel h on m.parent_prod_id = h.hotel_id inner join 
                     base_area_view a on h.area_id = a.area_id inner join
                     base_user u on m.create_user_id = u.id inner join
                     base_supplier_purchase p on h.purchase_name = p.id
                where m.parent_order_id = 0 and m.cancel_flag = 0 and m.order_valid_status = 1 and m.order_prod_type = 25
                group by substr(m.create_time,1,10),m.prod_start_station_date,m.prod_end_station_date,
                         m.create_user_id,u.user_name,p.id,p.purchaser_name,m.parent_prod_id,m.prod_id,m.prod_top_org_id,m.outside_sale_org_id";
        $conn = Yii::$app->db;
        $conn->createCommand($sql)->execute();

        return true;
    }

    public function getOrderList()
    {
        if (isset($_REQUEST['Report']))
            $_REQUEST = array_merge($_REQUEST, $_REQUEST['Report']);
        $this->setAttributes($_REQUEST, false);
        $order = new OrderMain();
        $data['dataProvider'] = $order->search($_REQUEST);

        return ['model' => $this, 'data' => $data];
    }

    /**
     * User:Redstop
     *
     * 日期维度来统计酒店销售数据
     * @return array
     */
    public function getDailyReportInfo($start_date, $end_date)
    {
        //        $start_date = '2017-08-01';
        //        $end_date = '2017-08-31';
        $date_type = 1;//预定时间
        $before_date = $this->getBeforeDate($start_date, $end_date);
        //        前月日期
        $before_start_date = "";
        $before_end_date = "";
        if ($before_date) {
            $before_start_date = $before_date[0];
            $before_end_date = $before_date[1];
        }

        //增加赔入赔出
        $models = OrderFinanceReparations::find()->select(['a.*', 'substring(a.create_time,1,10) as create_time1', 'a.send_user_id', 'sum( if(reparation_type=1,`reparations`,0) ) as peichu', 'sum( if(reparation_type=2,`reparations`,0) ) as peiru'])
            ->innerJoin('(select order_main.*,order_ht_status_log.create_user_id as send_user_id from order_main INNER JOIN `order_ht_status_log` ON parent_order_id=order_ht_status_log.order_id and order_ht_status_log.order_status=198 where parent_order_id in (select order_id from order_finance_reparations where cancel_flag = 0) and order_prod_type = 26 and order_main.cancel_flag = 0 group by parent_order_id) a', 'a.parent_order_id = order_finance_reparations.order_id')
            ->where(['and',
                ['=', 'order_finance_reparations.cancel_flag', 0],
            ]);

        $sql = $models->createCommand()->getSql();
        $models->andFilterWhere(['between', 'a.create_time', $start_date, $end_date]);
        //        $models->groupBy("substring( `a.create_time` , 1 , 10 )");
        $models->groupBy("create_time1")->asArray();
        $sum_models = $models->all();

        $group_by = "group by substring(create_time,1,10)";
        $date_type_sql = "and substring(create_time,1,10) between'{$start_date}' and '{$end_date}'";

        $sql = "select substring(create_time,1,10) as date,
                count(distinct parent_order_id) as order_count,
                count(*) as jianye_count,
                sum(order_price) as order_price,
                sum(base_price) as base_price,
                sum(total_commission) as total_commission,
                sum(profit_value) as profit_value
                from order_main  n
                where order_prod_type = 26 
                and cancel_flag = 0  
                and order_status in (198,314,147,313,382) 
                and order_valid_status = 1 
                $date_type_sql
                $group_by";
        $connection = Yii::$app->db;
        $data['rowset'] = $connection->createCommand($sql)->queryAll();
        //处理环比等数据
        foreach ($data['rowset'] as $k => $v) {
            //判断分母不为0
            $v['jianye_count'] = empty($v['jianye_count']) ? 0 : $v['jianye_count'];
            $v['order_price'] = empty($v['order_price']) ? 0 : $v['order_price'];
            $v['profit_value'] = empty($v['profit_value']) ? 0 : $v['profit_value'];

            foreach ($sum_models as $m => $model) {
                if ($model['create_time1'] == $v['date']) {
                    $data['rowset'][$k]['profit_value'] += $model['peiru'] - $model['peichu'];
                    $v['profit_value'] += $model['peiru'] - $model['peichu'];
                    break;
                } else {
                    continue;
                }
            }

            //毛利率
            if ($v['order_price'] != 0)
                $data['rowset'][$k]['profit_rate'] = sprintf('%.2f', round($v['profit_value'] * 100 / $v['order_price'], 2));
            else
                $data['rowset'][$k]['profit_rate'] = '-';
            //处理百分号
            //            $data['rowset'][$k]['jianye_count_mom'] = '-';
            //            $data['rowset'][$k]['order_price_mom'] = '-';
            //            $data['rowset'][$k]['profit_value_mom'] = '-';
            //            $data['rowset'][$k]['profit_rate'] = '-';

            $data['rowset'][$k]['jianye_count_mom'] = $data['rowset'][$k]['jianye_count_mom'] == '-' ? '-' : $data['rowset'][$k]['jianye_count_mom'] . '%';
            $data['rowset'][$k]['order_price_mom'] = $data['rowset'][$k]['order_price_mom'] == '-' ? '-' : $data['rowset'][$k]['order_price_mom'] . '%';
            $data['rowset'][$k]['profit_value_mom'] = $data['rowset'][$k]['profit_value_mom'] == '-' ? '-' : $data['rowset'][$k]['profit_value_mom'] . '%';
            $data['rowset'][$k]['profit_rate'] = $data['rowset'][$k]['profit_rate'] == '-' ? '-' : $data['rowset'][$k]['profit_rate'] . '%';
        }


        return $data['rowset'];
    }

    /**
     * @Author wanglg
     * @Desc 统计报表数据查询及处理
     * @return mixed
     */
    public function getNewReport()
    {
        $op = isset($_GET['op']) ? $_GET['op'] : $this->op;
        $past_date = $this->getBeforeDate($this->start_date, $this->end_date);
        $past_array = array('date_type' => $this->date_type, 'date_from' => $past_date[0], 'date_to' => $past_date[1], 'channel_id' => $this->org_sale_id,
            'hotel_id' => $this->hotel_id, 'hotel_name' => $this->hotel_name);
        $searchParam = array('date_type' => $this->date_type, 'date_from' => $this->start_date, 'date_to' => $this->end_date, 'channel_id' => $this->org_sale_id,
            'hotel_id' => $this->hotel_id, 'hotel_name' => $this->hotel_name);
        $params = array('searchOrderMain' => $searchParam);
        $past_params = array('searchOrderMain' => $past_array);
        $data['order_info'] = $this->getOrderList1($params, $op); // 基础数据
        $data['old_order_info'] = $this->getOrderList1($past_params, $op); //环比基础数据

        // 查询本月赔款信息
        $compensate_query = $this->getCompensateInOrOut($params['searchOrderMain'], $op);

        // 查询上月赔款信息
        $past_compensate_query = $this->getCompensateInOrOut($past_params['searchOrderMain'], $op);

        /*$compensate_info = $compensate_query->asArray()->all();
        $past_compensate_info = $past_compensate_query->asArray()->all();*/
        // 权限设置查询赔款信息:如果既是运营又是采购
        if (Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::HOTEL_OPERATOR) {
            $compensate_query->andFilterWhere(['b.PRINCIPAL_ID' => Yii::$app->user->id]);
            $past_compensate_query->andFilterWhere(['b.PRINCIPAL_ID' => Yii::$app->user->id]);
            $compensate_info = $compensate_query->asArray()->all();
            $past_compensate_info = $past_compensate_query->asArray()->all();
        } elseif (Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::HOTEL_PURCHASE) {
            $compensate_query->andFilterWhere(['b.SALES_MAN' => Yii::$app->user->id]);
            $past_compensate_query->andFilterWhere(['b.SALES_MAN' => Yii::$app->user->id]);
            $compensate_info = $compensate_query->asArray()->all();
            $past_compensate_info = $past_compensate_query->asArray()->all();
        } elseif (Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::HOTEL_ADMIN || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::HOTEL_CUS_ADMIN
            || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::SYS_CS || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::SYS_ADMIN
            || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::FINANCIAL_OFFICER) {
            // 酒店管理员、客服管理人员、系统管理员、财务可以查看所有数据
            $compensate_info = $compensate_query->asArray()->all();
            $past_compensate_info = $past_compensate_query->asArray()->all();
        } else {
            $compensate_info = [];
            $past_compensate_info = [];
        }


        $order_colum = '';
        $group_colum = '';
        $old_order_colum = '';
        if ($op == 'getInfo') // 酒店纬度
        {
            $old_order_colum = array_column($data['old_order_info'], 'PARENT_PROD_ID');
            $order_colum = array_column($data['order_info'], 'PARENT_PROD_ID');
            $group_colum = array_column($data['old_order_info'], 'PARENT_PROD_ID');
        } elseif ($op == 'orgSale') // 渠道纬度
        {
            $old_order_colum = array_column($data['old_order_info'], 'OUTSIDE_SALE_ORG_ID');
            $order_colum = array_column($data['order_info'], 'OUTSIDE_SALE_ORG_ID');
            $group_colum = array_column($data['old_order_info'], 'OUTSIDE_SALE_ORG_ID');
        } elseif ($op == 'cusService') // 客服录单
        {
            $old_order_colum = array_column($data['old_order_info'], 'CREATE_USER_ID');
            $order_colum = array_column($data['order_info'], 'CREATE_USER_ID');
            $group_colum = array_column($data['old_order_info'], 'CREATE_USER_ID');
        } elseif ($op == 'cusServiceSend') // 客服发单
        {
            $old_order_colum = array_column($data['old_order_info'], 'SEND_USER_ID');
            $order_colum = array_column($data['order_info'], 'SEND_USER_ID');
            $group_colum = array_column($data['old_order_info'], 'SEND_USER_ID');
        } elseif ($op == 'principal') // 运营
        {
            $old_order_colum = array_column($data['old_order_info'], 'PRINCIPAL_ID');
            $order_colum = array_column($data['order_info'], 'PRINCIPAL_ID');
            $group_colum = array_column($data['old_order_info'], 'PRINCIPAL_ID');
        } elseif ($op == 'purchaser') // 采购
        {
            $old_order_colum = array_column($data['old_order_info'], 'SALES_MAN');
            $order_colum = array_column($data['order_info'], 'SALES_MAN');
            $group_colum = array_column($data['old_order_info'], 'SALES_MAN');
        } elseif ($op == 'roomInfo') {
            $old_order_colum = array_column($data['old_order_info'], 'PROD_ID');
            $order_colum = array_column($data['order_info'], 'PROD_ID');
            $group_colum = array_column($data['old_order_info'], 'PROD_ID');
        }

        // 计算本月的赔入赔出
        foreach ($compensate_info as $compensate => $val) {
            // 查找不同纬度将不在数组中的压入数组，组合新数组
            if (
                ($op == 'getInfo' && !in_array($val['PARENT_PROD_ID'], $order_colum)) ||
                ($op == 'orgSale' && !in_array($val['OUTSIDE_SALE_ORG_ID'], $order_colum)) ||
                ($op == 'cusService' && !in_array($val['CREATE_USER_ID'], $order_colum)) ||
                ($op == 'cusServiceSend' && !in_array($val['SEND_USER_ID'], $order_colum)) ||
                ($op == 'principal' && !in_array($val['PRINCIPAL_ID'], $order_colum)) ||
                ($op == 'purchaser' && !in_array($val['SALES_MAN'], $order_colum)) ||
                ($op == 'roomInfo' && !in_array($val['PROD_ID'], $order_colum))
            ) {
                // $flag = empty($data['order_info']) ? true : false;
                $val['PROFIT_VALUE'] = $val['compensate_in'] - $val['compensate_out'];
                array_push($data['order_info'], $val);
                continue;
            }

            foreach ($data['order_info'] as $key => $item) {
                if (
                    ($op == 'getInfo' && $val['PARENT_PROD_ID'] == $item['PARENT_PROD_ID']) ||
                    ($op == 'orgSale' && $val['OUTSIDE_SALE_ORG_ID'] == $item['OUTSIDE_SALE_ORG_ID']) ||
                    ($op == 'cusService' && $val['CREATE_USER_ID'] == $item['CREATE_USER_ID']) ||
                    ($op == 'cusServiceSend' && $val['SEND_USER_ID'] == $item['SEND_USER_ID']) ||
                    ($op == 'principal' && $val['PRINCIPAL_ID'] == $item['PRINCIPAL_ID']) ||
                    ($op == 'purchaser' && $val['SALES_MAN'] == $item['SALES_MAN']) ||
                    ($op == 'roomInfo' && $val['PROD_ID'] == $item['PROD_ID'])
                ) {
                    $data['order_info'][$key]['order_count'] = $data['order_info'][$key]['order_count'] + $val['order_count'];
                    $data['order_info'][$key]['compensate_out'] = $item['compensate_out'] + $val['compensate_out'];
                    $data['order_info'][$key]['compensate_in'] = $item['compensate_in'] + $val['compensate_in'];
                    $data['order_info'][$key]['PROFIT_VALUE'] = $data['order_info'][$key]['PROFIT_VALUE'] + $val['compensate_in'] - $val['compensate_out'];
                }
            }
        }

        // 处理上月的赔入赔出
        foreach ($past_compensate_info as $past_compensate => $val) {
            if (
                ($op == 'getInfo' && !in_array($val['PARENT_PROD_ID'], $old_order_colum)) ||
                ($op == 'orgSale' && !in_array($val['OUTSIDE_SALE_ORG_ID'], $old_order_colum)) ||
                ($op == 'cusService' && !in_array($val['CREATE_USER_ID'], $old_order_colum)) ||
                ($op == 'cusServiceSend' && !in_array($val['SEND_USER_ID'], $old_order_colum)) ||
                ($op == 'principal' && !in_array($val['PRINCIPAL_ID'], $old_order_colum)) ||
                ($op == 'purchaser' && !in_array($val['SALES_MAN'], $old_order_colum)) ||
                ($op == 'roomInfo' && !in_array($val['PROD_ID'], $old_order_colum))
            ) {
                $val['PROFIT_VALUE'] = $val['compensate_in'] - $val['compensate_out'];
                array_push($data['old_order_info'], $val);
                continue;
            }

            foreach ($data['old_order_info'] as $key => $item) {
                if (
                    ($op == 'getInfo' && $val['PARENT_PROD_ID'] == $item['PARENT_PROD_ID']) ||
                    ($op == 'orgSale' && $val['OUTSIDE_SALE_ORG_ID'] == $item['OUTSIDE_SALE_ORG_ID']) ||
                    ($op == 'cusService' && $val['CREATE_USER_ID'] == $item['CREATE_USER_ID']) ||
                    ($op == 'cusServiceSend' && $val['SEND_USER_ID'] == $item['SEND_USER_ID']) ||
                    ($op == 'principal' && $val['PRINCIPAL_ID'] == $item['PRINCIPAL_ID']) ||
                    ($op == 'purchaser' && $val['SALES_MAN'] == $item['SALES_MAN']) ||
                    ($op == 'roomInfo' && $val['PROD_ID'] == $item['PROD_ID'])
                ) {
                    $data['old_order_info'][$key]['order_count'] = $data['old_order_info'][$key]['order_count'] + $val['order_count'];
                    $data['old_order_info'][$key]['compensate_out'] = $item['compensate_out'] + $val['compensate_out'];
                    $data['old_order_info'][$key]['compensate_in'] = $item['compensate_in'] + $val['compensate_in'];
                    $data['old_order_info'][$key]['PROFIT_VALUE'] = $data['old_order_info'][$key]['PROFIT_VALUE'] + $val['compensate_in'] - $val['compensate_out'];
                }
            }
        }

        $total_array = [];
        $old_total_array['total_order_num'] = array_sum(array_column($data['old_order_info'], 'order_count'));// 总订单数
        $old_total_array['total_room_num'] = array_sum(array_column($data['old_order_info'], 'room_count'));// 总间夜数
        $old_total_array['total_order_price'] = array_sum(array_column($data['old_order_info'], 'ORDER_PRICE'));// 总销售额
        $old_total_array['total_base_price'] = array_sum(array_column($data['old_order_info'], 'BASE_PRICE'));// 总销售额
        $old_total_array['total_commission_total'] = array_sum(array_column($data['old_order_info'], 'TOTAL_COMMISSION'));// 总渠道佣金
        $old_total_array['total_compensate_in'] = array_sum(array_column($data['old_order_info'], 'compensate_in'));// 总赔入
        $old_total_array['total_compensate_out'] = array_sum(array_column($data['old_order_info'], 'compensate_out'));// 总赔出
        $old_total_array['total_order_profit'] = array_sum(array_column($data['old_order_info'], 'PROFIT_VALUE'));// 订单总利润
        foreach ($data['order_info'] as $key => $order) {
            // 总利润
            $total_array['total_order_num'] += $order['order_count'];// 总订单数
            $total_array['total_room_num'] += $order['room_count'];// 总间夜数
            $total_array['total_order_price'] += round($order['ORDER_PRICE'], 2);// 总销售额
            $total_array['total_base_price'] += round($order['BASE_PRICE'], 2);// 总销售额
            $total_array['total_commission_total'] += round($order['TOTAL_COMMISSION'], 2);// 总渠道佣金
            $total_array['total_compensate_in'] += round($order['compensate_in'], 2);// 总赔入
            $total_array['total_compensate_out'] += round($order['compensate_out'], 2);// 总赔出
            $total_array['total_order_profit'] += round($order['PROFIT_VALUE'], 2);// 订单总利润

            //酒店纬度
            if (!in_array($order['PARENT_PROD_ID'], $group_colum) && $op == 'getInfo') {
                $data['old_order_info'][] = ['PARENT_PROD_ID' => $order['PARENT_PROD_ID'], 'room_count' => 0, 'ORDER_PRICE' => 0, 'BASE_PRICE' => 0, 'TOTAL_COMMISSION' => 0,
                    'PROFIT_VALUE' => 0, 'compensate_in' => 0, 'compensate_out' => 0];
            }

            // 渠道维度
            if (!in_array($order['OUTSIDE_SALE_ORG_ID'], $group_colum) && $op == 'orgSale') {
                $data['old_order_info'][] = ['OUTSIDE_SALE_ORG_ID' => $order['OUTSIDE_SALE_ORG_ID'], 'room_count' => 0, 'ORDER_PRICE' => 0, 'BASE_PRICE' => 0, 'TOTAL_COMMISSION' => 0,
                    'PROFIT_VALUE' => 0, 'compensate_in' => 0, 'compensate_out' => 0];
            }

            // 录单客服
            if (!in_array($order['CREATE_USER_ID'], $group_colum) && $op == 'cusService') {
                $data['old_order_info'][] = ['CREATE_USER_ID' => $order['CREATE_USER_ID'], 'room_count' => 0, 'ORDER_PRICE' => 0, 'BASE_PRICE' => 0, 'TOTAL_COMMISSION' => 0,
                    'PROFIT_VALUE' => 0, 'compensate_in' => 0, 'compensate_out' => 0];
            }

            // 发单客服
            if (!in_array($order['SEND_CREATE_USER'], $group_colum) && $op == 'cusServiceSend') {
                $data['old_order_info'][] = ['SEND_CREATE_USER' => $order['SEND_CREATE_USER'], 'room_count' => 0, 'ORDER_PRICE' => 0, 'BASE_PRICE' => 0, 'TOTAL_COMMISSION' => 0,
                    'PROFIT_VALUE' => 0, 'compensate_in' => 0, 'compensate_out' => 0];
            }

            // 运营维度
            if (!in_array($order['PRINCIPAL_ID'], $group_colum) && $op == 'principal') {
                $data['old_order_info'][] = ['PRINCIPAL_ID' => $order['PRINCIPAL_ID'], 'room_count' => 0, 'ORDER_PRICE' => 0, 'BASE_PRICE' => 0, 'TOTAL_COMMISSION' => 0,
                    'PROFIT_VALUE' => 0, 'compensate_in' => 0, 'compensate_out' => 0];
            }

            // 采购维度
            if (!in_array($order['PURCHASE_TRUE_NAME'], $group_colum) && $op == 'purchaser') {
                $data['old_order_info'][] = ['PURCHASE_TRUE_NAME' => $order['PURCHASE_TRUE_NAME'], 'room_count' => 0, 'ORDER_PRICE' => 0, 'BASE_PRICE' => 0, 'TOTAL_COMMISSION' => 0,
                    'PROFIT_VALUE' => 0, 'compensate_in' => 0, 'compensate_out' => 0];
            }

            // 房型维度
            if (!in_array($order['PROD_ID'], $group_colum) && $op == 'roomInfo') {
                $data['old_order_info'][] = ['PROD_ID' => $order['PROD_ID'], 'room_count' => 0, 'ORDER_PRICE' => 0, 'BASE_PRICE' => 0, 'TOTAL_COMMISSION' => 0,
                    'PROFIT_VALUE' => 0, 'compensate_in' => 0, 'compensate_out' => 0];
            }

            foreach ($data['old_order_info'] as $item => $old_order) {
                // 酒店纬度
                if (
                    ($op == 'getInfo' && ($order['PARENT_PROD_ID'] == $old_order['PARENT_PROD_ID'] || $compensate_info['PARENT_PROD_ID'])) // 酒店纬度
                    || ($op == 'orgSale' && $order['OUTSIDE_SALE_ORG_ID'] == $old_order['OUTSIDE_SALE_ORG_ID'])
                    || ($op == 'cusService' && $order['CREATE_USER_ID'] == $old_order['CREATE_USER_ID'])
                    || ($op == 'cusServiceSend' && $order['SEND_CREATE_USER'] == $old_order['SEND_CREATE_USER'])
                    || ($op == 'principal' && $order['PRINCIPAL_ID'] == $old_order['PRINCIPAL_ID'])
                    || ($op == 'purchaser' && $order['SALES_MAN'] == $old_order['SALES_MAN'])
                    || ($op == 'roomInfo' && $order['PROD_ID'] == $old_order['PROD_ID'])
                ) {
                    // 间夜月环比
                    $data['order_info'][$key]['room_count_mom'] = (empty($old_order['room_count']) || $old_order['room_count'] == 0) ? '-' : round(($order['room_count'] - $old_order['room_count']) / $old_order['room_count'] * 100, 2) . '%';
                    // 销售额月环比
                    $data['order_info'][$key]['order_price_mom'] = (empty($old_order['ORDER_PRICE']) || $old_order['ORDER_PRICE'] == 0) ? '-' : round(($order['ORDER_PRICE'] - $old_order['ORDER_PRICE']) / $old_order['ORDER_PRICE'] * 100, 2) . '%';
                    // 毛利月环比
                    $data['order_info'][$key]['profit_value_mom'] = (empty($old_order['PROFIT_VALUE']) || $old_order['PROFIT_VALUE'] == 0) ? '-' : round(($order['PROFIT_VALUE'] - $old_order['PROFIT_VALUE']) / abs($old_order['PROFIT_VALUE']) * 100, 2) . '%';
                    // 毛利率
                    break;
                }
            }
            $data['order_info'][$key]['price_rate'] = (empty($order['ORDER_PRICE']) || $order['ORDER_PRICE'] == 0) ? '-' : round($order['PROFIT_VALUE'] / $order['ORDER_PRICE'], 4) * 100 . '%';
        }

        // 本期总利润
        $A = $total_array['total_order_profit'] - $total_array['compensate_out'] + $total_array['compensate_in'];
        // 上期总利润
        $B = $old_total_array['total_order_profit'] - $old_total_array['compensate_out'] + $old_total_array['compensate_in'];
        // 总环比计算
        $total_array['total_mom_room_count'] = empty($old_total_array['total_room_num']) ? '-' : round(($total_array['total_room_num'] - $old_total_array['total_room_num']) / $old_total_array['total_room_num'] * 100, 2) . '%';
        $total_array['total_mom_order_price'] = empty($old_total_array['total_order_price']) ? '-' : number_format(($total_array['total_order_price'] - $old_total_array['total_order_price']) / $old_total_array['total_order_price'] * 100, 2) . '%';
        $total_array['total_mom_profit_value'] = empty($B) ? '-' : number_format(($A - $B) / $B * 100, 2) . '%'; // 毛利月环比
        $total_array['total_price_rate'] = (empty($total_array['total_order_price']) || $total_array['total_order_price'] == 0) ? '-' : number_format($A / $total_array['total_order_price'] * 100, 2) . '%'; // 毛利率
        $data['total'] = $total_array;

        // 处理数据
        return $data;
    }

    /**
     * @Author wanglg
     * @Desc查询日期段内所有订单信息，根据查询维度查询
     * @param $params
     * @param $op
     * @return
     */
    public function getOrderList1($params, $op)
    {
        $model = new searchOrderMain();
        // 如果是运营，查询所运营的酒店信息，如果是非酒店运营，采购，管理人员，客服管理人员，返回空数组
        if (BaseUserAuth::HOTEL_OPERATOR == Yii::$app->user->identity['USER_ROLE']) {
            $where = ['order_main.PRINCIPAL_ID' => Yii::$app->user->id];
            $childWhere = ['PRINCIPAL_ID' => Yii::$app->user->id];
        } elseif (BaseUserAuth::HOTEL_PURCHASE == Yii::$app->user->identity['USER_ROLE']) {
            $where = ['order_main.SALES_MAN' => Yii::$app->user->id];
            $childWhere = ['SALES_MAN' => Yii::$app->user->id];
        } elseif (Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::HOTEL_ADMIN || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::HOTEL_CUS_ADMIN
            || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::SYS_CS || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::SYS_ADMIN
            || Yii::$app->user->identity['USER_ROLE'] == BaseUserAuth::FINANCIAL_OFFICER) {
            // 酒店管理员、客服管理人员、后台管理人员和系统管理员可以查看所有数据
            $where = '';
            $childWhere = '';
        } else {
            return [];
        }

        // 查询本月数据
        $query = OrderMain::find()
            ->joinWith('user')
            ->joinWith('baseChannel')
            ->leftJoin('base_user as p', 'p.ID=order_main.PRINCIPAL_ID')
            ->leftJoin('base_user as pur', 'pur.ID=order_main.SALES_MAN')
            ->from('order_main');

        $model->load($params);
        $query->andFilterWhere([
            'order_main.CANCEL_FLAG' => 0,
            'order_main.ORDER_PROD_TYPE' => OrderMain::ORDER_PROD_TYPE_MAIN,
            'order_main.ORDER_VALID_STATUS' => 1,
        ]);

        $query->addSelect(['order_main.OUTSIDE_SALE_ORG_ID', 'base_supplier.SUPPLIER_NAME as CHANNEL_NAME', 'order_main.PARENT_PROD_ID', 'order_main.CREATE_USER_ID', 'order_main.PROD_ID',
            'order_main.PRINCIPAL_ID', 'order_main.SALES_MAN', 'p.TRUE_NAME as PRINCIPAL_TRUE_NAME', 'base_user.TRUE_NAME AS INPUT_CREATE_USER', 'order_main.PROD_NAME',
            'pur.TRUE_NAME as PURCHASE_TRUE_NAME',
            'COUNT(order_main.ORDER_ID) as order_count', 'order_main.PARENT_PROD_NAME', 'IF(0=0,0.00,0.00) as compensate_out', 'IF(0=0,0.00,0.00) as compensate_in']);

        if ($model->channel_id) {
            $query->andFilterWhere(['order_main.OUTSIDE_SALE_ORG_ID' => trim($model->channel_id)]);
        }
        if ($model->hotel_name) {
            $query->andFilterWhere(['like', 'order_main.PARENT_PROD_NAME', trim($model->hotel_name)]);

        }
        if ($model->hotel_id) {
            $query->andFilterWhere(['order_main.PARENT_PROD_ID' => trim($model->hotel_id)]);
        }


        if ($op != 'cusServiceSend') {
            $query->andFilterWhere(['order_main.ORDER_STATUS' =>// array(198, 314, 147, 313, 382),
                [OrderMain::ORDER_STATUS_WAITING_CONFIRM,
                    OrderMain::ORDER_STATUS_CONFIRMED,
                    OrderMain::ORDER_STATUS_FINISH,
                    OrderMain::ORDER_STATUS_WAITING_SEND,
                    OrderMain::ORDER_STATUS_CHANGING]]);
        }

        if ($op == 'getInfo') {
            $group_by = 'PARENT_PROD_ID';
            $order_by = ['room_count' => SORT_DESC, 'order_main.PARENT_PROD_ID' => SORT_ASC];
        } elseif ($op == 'orgSale') {
            $group_by = 'OUTSIDE_SALE_ORG_ID';
            $order_by = ['room_count' => SORT_DESC, 'order_main.OUTSIDE_SALE_ORG_ID' => SORT_ASC];
        } elseif ($op == 'cusService') // 客服录单
        {
            $group_by = 'CREATE_USER_ID';
            $order_by = ['room_count' => SORT_DESC, 'order_main.CREATE_USER_ID' => SORT_ASC];
        } elseif ($op == 'cusServiceSend') // 客服发单
        {
            $query->addSelect(['order_ht_status_log.CREATE_USER_ID AS SEND_USER_ID', 'lu.TRUE_NAME AS SEND_CREATE_USER']);
            $query->joinWith('orderHtStatusLog.user lu');
            $query->andFilterWhere(['order_main.DOCKING_TYPE' => OrderMain::ORDER_TYPE_CHANNEL, 'order_ht_status_log.ORDER_STATUS' => OrderMain::ORDER_STATUS_WAITING_CONFIRM,
                'order_ht_status_log.BEFORE_STATUS' => OrderMain::ORDER_STATUS_WAITING_SEND]);
            $group_by = 'SEND_USER_ID';
            $order_by = ['room_count' => SORT_DESC, 'SEND_USER_ID' => SORT_ASC];
        } elseif ($op == 'principal') // 运营
        {
            $group_by = 'PRINCIPAL_ID';
            $order_by = ['room_count' => SORT_DESC, 'order_main.PRINCIPAL_ID' => SORT_ASC];
        } elseif ($op == 'purchaser') // 采购
        {
            $group_by = 'SALES_MAN';
            $order_by = ['room_count' => SORT_DESC, 'SALES_MAN' => SORT_ASC];
        } elseif ($op == 'roomInfo') // 采购
        {
            $group_by = 'PROD_ID';
            $order_by = ['room_count' => SORT_DESC, 'order_main.PROD_ID' => SORT_ASC];
        } else {
            $group_by = 'PARENT_PROD_ID';
            $order_by = ['room_count' => SORT_DESC, 'order_main.PARENT_PROD_ID' => SORT_ASC];
        }

        //预订时间
        $date_start = ' 00:00:00';
        $date_end = ' 23:59:59';
        // 入住日期
        if ($model->date_type == 2) {
            // 子查询需要查询的字段
            $query->addSelect(['vice_order.ORDER_PRICE ORDER_PRICE', 'vice_order.BASE_PRICE BASE_PRICE', 'vice_order.TOTAL_COMMISSION TOTAL_COMMISSION',
                'vice_order.PROFIT_VALUE PROFIT_VALUE', 'vice_order.room_count room_count']);

            $check_in = OrderMain::find()
                ->select(['SUM(ORDER_PRICE) ORDER_PRICE', 'SUM(BASE_PRICE) BASE_PRICE', 'SUM(TOTAL_COMMISSION) TOTAL_COMMISSION', 'SUM(PROFIT_VALUE) PROFIT_VALUE',
                    'count(order_main.ORDER_ID) AS room_count'])
                ->from('order_main');

            $check_in->andFilterWhere(['ORDER_PROD_TYPE' => OrderMain::ORDER_PROD_TYPE_SUM, 'order_main.CANCEL_FLAG' => 0, 'ORDER_VALID_STATUS' => 1,
                'order_main.ORDER_STATUS' =>
                    [OrderMain::ORDER_STATUS_WAITING_CONFIRM,
                        OrderMain::ORDER_STATUS_CONFIRMED,
                        OrderMain::ORDER_STATUS_FINISH,
                        OrderMain::ORDER_STATUS_WAITING_SEND,
                        OrderMain::ORDER_STATUS_CHANGING],
            ]);
            $check_in->andFilterWhere(['between', 'PROD_START_STATION_DATE', $model->date_from, $model->date_to]);
            if ($childWhere) {
                $check_in->andFilterWhere($childWhere);
            }
            // 查询筛选条件
            if ($model->hotel_id) {
                $check_in->andFilterWhere(['PARENT_PROD_ID' => $model->hotel_id]);
            }

            if ($model->channel_id) {
                $check_in->andFilterWhere(['OUTSIDE_SALE_ORG_ID' => $model->channel_id]);
            }

            $check_in->groupBy($group_by);
            // 按照入住日期查询，因为一个订单可能有多个入住日期，但只统计筛选范围的子订单，需要通过子订单来查询，
            // 如果是发单维度，需要关联订单状态表查询发单客服
            if ($op == 'cusServiceSend') {
                $check_in->addSelect(["order_ht_status_log.CREATE_USER_ID AS {$group_by}"]);
                $check_in->leftJoin('order_ht_status_log', 'order_main.PARENT_ORDER_ID=order_ht_status_log.ORDER_ID AND order_ht_status_log.ORDER_STATUS=198 
                                      AND order_ht_status_log.BEFORE_STATUS=313')
                    ->leftJoin('base_user', 'order_ht_status_log.CREATE_USER_ID=base_user.ID');
                $check_in->andFilterWhere(['DOCKING_TYPE' => OrderMain::ORDER_TYPE_CHANNEL]);
                $check_in_sql = $check_in->createCommand()->getRawSql();
                $query->leftJoin("({$check_in_sql}) as vice_order", "vice_order.{$group_by}=order_ht_status_log.CREATE_USER_ID");
            } else {
                $check_in->addSelect([$group_by]);
                $check_in_sql = $check_in->createCommand()->getRawSql();
                $query->leftJoin("({$check_in_sql}) as vice_order", "vice_order.{$group_by}=order_main.{$group_by}");
            }

            // 主订单的日期筛选
            $query->andFilterWhere(['or', ['and', ['<=', 'order_main.PROD_START_STATION_DATE', $model->date_from], ['>', 'order_main.PROD_END_STATION_DATE', $model->date_from]],
                ['and', ['<=', 'order_main.PROD_START_STATION_DATE', $model->date_to], ['>', 'order_main.PROD_END_STATION_DATE', $model->date_to]],
                ['and', ['>=', 'order_main.PROD_START_STATION_DATE', $model->date_from], ['<=', 'order_main.PROD_END_STATION_DATE', $model->date_to]]]);
        } elseif ($model->date_type == 3) { // 离店
            $date_column = 'PROD_END_STATION_DATE';
            $query->addSelect(['SUM(order_main.ORDER_PRICE) as ORDER_PRICE', 'SUM(order_main.BASE_PRICE) as BASE_PRICE', 'SUM(order_main.TOTAL_COMMISSION) as TOTAL_COMMISSION', 'SUM(order_main.PROFIT_VALUE) as PROFIT_VALUE',
                'SUM((SELECT COUNT(ORDER_ID) FROM order_main a WHERE PARENT_ORDER_ID=order_main.ORDER_ID AND a.CANCEL_FLAG=0 AND a.ORDER_PROD_TYPE=26)) as room_count',
            ]);
            $query->andFilterWhere(['between', $date_column, $model->date_from, $model->date_to]);
        } else { // 录单时间
            $query->addSelect(['SUM(order_main.ORDER_PRICE) as ORDER_PRICE', 'SUM(order_main.BASE_PRICE) as BASE_PRICE', 'SUM(order_main.TOTAL_COMMISSION) as TOTAL_COMMISSION', 'SUM(order_main.PROFIT_VALUE) as PROFIT_VALUE',
                'SUM((SELECT COUNT(ORDER_ID) FROM order_main a WHERE PARENT_ORDER_ID=order_main.ORDER_ID AND a.CANCEL_FLAG=0 AND a.ORDER_PROD_TYPE=26)) as room_count',
            ]);
            $query->andFilterWhere(['between', 'order_main.CREATE_TIME', $model->date_from . $date_start, $model->date_to . $date_end]);
        }


        if ($where) {
            $query->andFilterWhere($where);
        }

        $query->orderBy($order_by);
        $query->groupBy($group_by);
        // $sql = $query->createCommand()->getRawSql();

        return $query->asArray()->all();
    }

    /**
     * @Author wanglg
     * @Desc 获取赔入赔出sql对象
     * @param $params查询维度详细参数
     * @param $op查询维度
     * @return object
     */
    public function getCompensateInOrOut($params, $op)
    {
        $from = $params['date_from'] . ' 00:00:00';
        $to = $params['date_to'] . ' 23:59:59';
        $info = OrderFinanceReparations::find()
            ->select(['b.CREATE_TIME', 'b.CREATE_USER_ID', 'b.SALES_MAN AS SALES_MAN', 'b.PRINCIPAL_ID', 'a.ORDER_ID', 'b.PARENT_PROD_ID', 'b.PROD_ID', 'b.CUSTOMER_NAME',
                'b.CUSTOMER_MOBILE', 'b.PARENT_PROD_NAME', 'b.PROD_NAME', 'b.OUTSIDE_SALE_ORG_ID', 'b.PROD_START_STATION_DATE', 'b.PROD_END_STATION_DATE',
                'IF(0=0, "0", "0") as ORDER_PRICE', 'IF(0=0, "0", "0") as BASE_PRICE', 'IF(0=0, "0", "0") as TOTAL_COMMISSION', 'IF(0=0, "0", "0") as PROFIT_VALUE', 'bs.SUPPLIER_NAME as CHANNEL_NAME',
                'user2.TRUE_NAME as INPUT_CREATE_USER', 'user1.TRUE_NAME as PURCHASE_TRUE_NAME',
                'IF(0=0, "-", "-") AS ORDER_STATUS_NAME'])
            ->leftJoin('order_main b', 'a.order_id=b.ORDER_ID')
            ->leftJoin('base_supplier bs', 'bs.ID=b.OUTSIDE_SALE_ORG_ID')
            ->leftJoin('base_user user2', 'user2.ID=b.CREATE_USER_ID')
            ->leftJoin('base_user user1', 'user1.ID=b.SALES_MAN')
            ->where(['and', 'a.cancel_flag=0', 'b.ORDER_PROD_TYPE=25', 'b.CANCEL_FLAG=0', "a.create_time between '{$from}' and '{$to}'"])
            ->from('order_finance_reparations a');

        // 如果是发单或录单则不统计赔入赔出
        if (in_array($op, ['cusServiceSend', 'cusService'])) {
            $info->addSelect(['IF(0=0, 0, 0) AS order_count', 'IF(0=0, 0, 0) as compensate_out', 'IF(0=0, 0, 0)  as compensate_in',]);
        } else {
            $info->addSelect(['COUNT(a.order_id) as order_count', 'SUM(case reparation_type when 1 then reparations else 0.00 end) as compensate_out',
                'SUM(case reparation_type when 2 then reparations else 0.00 end) as compensate_in',]);
        }

        // 查询发单相关信息
        if ($op == 'cusServiceSend') {
            $info->addSelect(['sl.CREATE_USER_ID as SEND_USER_ID', 'user.TRUE_NAME SEND_CREATE_USER']);
            $info->leftJoin('order_ht_status_log sl', 'sl.ORDER_ID=b.ORDER_ID and sl.ORDER_STATUS=198 and sl.BEFORE_STATUS=313')
                ->leftJoin('base_user user', 'sl.CREATE_USER_ID=user.ID');
            $info->andFilterWhere(['b.DOCKING_TYPE' => OrderMain::ORDER_TYPE_CHANNEL]);
            $info->groupBy('SEND_USER_ID');
        } else {
            $group_by = $this->selectGroupBy($op);
            $info->groupBy($group_by);
        }

        if (!empty($params['hotel_id'])) {
            $info->andFilterWhere(['b.PARENT_PROD_ID' => $params['hotel_id']]);
        }

        if (!empty($params['channel_id'])) {
            $info->andFilterWhere(['b.OUTSIDE_SALE_ORG_ID' => $params['channel_id']]);
        }

        if (!empty($params['principal_id'])) {
            $info->andFilterWhere(['b.PRINCIPAL_ID' => $params['principal_id']]);
        }

        if (!empty($params['purchaser_id'])) {
            $info->andFilterWhere(['b.SALES_MAN' => $params['purchaser_id']]);
        }

        if (!empty($params['user_id'])) {
            $info->andFilterWhere(['b.CREATE_USER_ID' => $params['user_id']]);
        }

        if (!empty($params['send_user_id'])) {
            $info->andFilterWhere(['sl.CREATE_USER_ID' => $params['send_user_id']]);
        }

        $info->addSelect(['IF(0=0, "-", "-") as room_count']);

        // 查询如果订单日期也在该范围内则room_count - 该数量
        return $info;
    }

    /**
     * @Author wanglg
     * @Desc 不同纬度分组
     * @param $op分组维度
     * @return array
     */
    public function selectGroupBy($op)
    {
        if ($op == 'getInfo') {
            $group_by = ['PARENT_PROD_ID'];// 酒店维度
        } elseif ($op == 'orgSale') {
            $group_by = ['OUTSIDE_SALE_ORG_ID'];
        } elseif ($op == 'cusService') // 客服录单
        {
            $group_by = ['CREATE_USER_ID'];
        } elseif ($op == 'principal') // 运营
        {
            $group_by = ['PRINCIPAL_ID'];
        } elseif ($op == 'purchaser') // 采购
        {
            $group_by = ['SALES_MAN'];
        } elseif ($op == 'roomInfo') // 房型
        {
            $group_by = ['PROD_ID'];
        } else {
            $group_by = ['PARENT_PROD_ID'];
        }

        return $group_by;
    }

}