mall/modules/admin/models/ShareFinancialListForm.php

<?php
/**
 * 重庆赤晓店信息科技有限公司
 * https://www.chixiaodian.com
 * Copyright (c) 2023 赤店商城 All rights reserved.
 */
namespace app\modules\admin\models;

use app\models\BonusPoolDetail;
use app\models\Level;
use app\models\LevelOrder;
use app\models\MdProfit;
use app\models\Order;
use app\models\OrderDetail;
use app\models\OrderTransit;
use app\models\SaasUser;
use app\models\ShareDetail;
use app\models\SharingReceiver;
use app\models\User;
use app\models\UserShareMoney;
use Spatie\SimpleExcel\SimpleExcelWriter;
use yii\base\Model;

class ShareFinancialListForm extends Model
{
    public $status; //状态 1全部 2待收货 3已收货 4已完成
    public $order_no;//订单号
    public $user_name;//用户名称
    public $order_start_time;//订单开始时间
    public $order_end_time;//订单结束时间
    public $confirm_start_time;//收货开始时间
    public $confirm_end_time;//收货结束时间
    public $isExport;//是否为导出
    public $has_refund;

    public function rules()
    {
        return [
            [['order_no'], 'trim'],
            [['status', 'isExport'], 'integer'],
            [['order_no', 'user_name', 'order_start_time', 'order_end_time', 'confirm_start_time', 'confirm_end_time'], 'string'],
            [['has_refund'], 'safe'],
        ];
    }

    /*
     * 财务列表
     */
    public function financialList() {
        try {
            $field = [
                'total_price', 'pay_price', 'share_money', 'md_profit', 'share_detail_sub', 'share_detail', 'sharing_receiver'
                //订单总额， 实际付款总额， 分销支出总额， 门店支出总额， 极差支出总额， 股东分红总额， 平台抽成总额
            ];
            $data = [];
            $all_data = [];
            if (!$this->isExport) {
                //获取统计数据
                foreach ($field as $index => $value) {
                    $result = $this->getSql($value);
                    if ($result['code'] !== 0) {
                        return $result;
                    }

                    $sql = $result['data'];
                    if (in_array($value, ['md_profit', 'share_detail_sub', 'share_detail'])) {
                        $data_sub = \Yii::$app->db->createCommand($sql)->queryAll();
                        $data_id = array_column($data_sub, 'md_profit');
                        $data_id = array_diff($data_id, ['0']);
                        if ($value === 'md_profit') {
                            $data[$index] = MdProfit::find()->where(['order_id' => $data_id])->select('total_profit')->sum('total_profit') ?: '0.00';
                        }
                        if ($value === 'share_detail_sub') {
                            $data[$index] = ShareDetail::find()->where(['store_id' => get_store_id(), 'type' =>
                                [
                                    ShareDetail::TYPE_RANGE_PROFIT,
                                    ShareDetail::TYPE_SHARE_PROFIT,
                                    ShareDetail::TYPE_TWO_PLUS_ONE_SHARE_PROFIT,
                                    ShareDetail::TYPE_TWO_PLUS_ONE_SHARE_TEAM_PROFIT,
                                    ShareDetail::TYPE_RANGE_PROFIT_OLD,
                                    ShareDetail::TYPE_TWO_PLUS_ONE_SHARE_TEAM_PROFIT_OLD,
                                    ShareDetail::TYPE_FROST_PROFIT,
                                ], 'type_id' => $data_id
                            ])->select('money')->sum('money') ?: '0.00';
                        }
                        if ($value === 'share_detail') {
                            $data[$index] = BonusPoolDetail::find()->where(['order_id' => $data_id])->select('money')->sum('money') ?: '0.00';
                            $query = Order::find()->where(['id' => $data_id])->andWhere(['not in', 'id', BonusPoolDetail::find()->where(['order_id' => $data_id])->select('order_id')]);
                            $query->select('sum(old_holder_first_price), sum(old_holder_second_price), sum(old_holder_third_price), sum(holder_first_price), sum(holder_second_price), sum(holder_third_price)');
                            $data[$index] += array_sum($query->asArray()->one());
//                            $data[$index] = ShareDetail::find()->where(['store_id' => get_store_id(), 'type' =>ShareDetail::TYPE_HOLDER_PROFIT,
//                                'type_id' => $data_id
//                            ])->select('money')->sum('money') ?: '0.00';
                        }
                    } elseif ($value === 'sharing_receiver') {
                        $data_sub = \Yii::$app->db->createCommand($sql)->queryAll();
                        $data_order_no = array_column($data_sub, 'order_no');
                        //让利比例
                        $data[$index] = SharingReceiver::find()->where(['order_no' => $data_order_no, 'is_delete' => 0])->select('amount')->sum('amount') ?: '0.00';
                    } else {
                        $data_sub = \Yii::$app->db->createCommand($sql)->queryColumn();
                        $data[$index] = array_sum($data_sub) ?: '0.00';
                    }
                    $data[$index] = sprintf('%.2f', $data[$index]);
                }
                $all_data = array_combine($field, $data);
            }


//            $query->select('o.id, o.pay_price, o.coupon_sub_price, o.level_order_id, su.mobile,
//             o.integral_difference_price, o.order_no, o.created_at, o.confirm_time, o.trade_status, su.name, su.avatar,
//             o.first_price, o.second_price, o.third_price');
            $result = $this->getSql();
            if ($result['code'] !== 0) {
                return $result;
            }
            $sql = $result['data'];
            $pageNo = get_params('pageNo', get_params('page', 1));
            $pageSize = get_params('pageSize', \Yii::$app->params['pageSize']);
            $pageInitNum = ($pageNo - 1) * $pageSize;
            $count = \Yii::$app->db->createCommand($sql)->query()->count();
            $sql .= "  ORDER BY o.created_at desc ";
            if (!$this->isExport) {
                $sql .= " LIMIT {$pageSize} OFFSET {$pageInitNum}";
            }
            $list = \Yii::$app->db->createCommand($sql)->queryAll();


            $total_data = [
                'totalCount' => $count
            ];
            $all_data = array_merge($total_data, $all_data);
            foreach ($list as &$value) {
                $order = Order::findOne($value['id']);
                $value['order'] = $order;
                $value['send_type_name'] = $order['is_delivery'] ? '同城' : ($order['is_offline'] ? '自提' :'快递');
                $value['mch'] = $order['mch_id'] ? \app\models\Mch::findOne($order['mch_id']) : null;
                $value['mch_name'] = $value['mch'] ? $value['mch']['name'] : '';
                if ($value['created_at']) {
                    $value['created_at'] = date('Y-m-d H:i:s', $value['created_at']);
                }

                if ($value['confirm_time']) {
                    $value['confirm_time'] = date('Y-m-d H:i:s', $value['confirm_time']);
                } else {
                    $value['confirm_time'] = '-';
                }


                $value['total_price'] = sprintf('%.2f', ($value['pay_price'] + $value['integral_difference_price'] + $value['coupon_sub_price']));
                //让利比例
                $SharingReceiver = SharingReceiver::find()->where(['order_no' => $value['order_no'], 'is_delete' => 0])->select('amount')->column();
                $value['sharing_receiver'] = sprintf('%.2f', array_sum($SharingReceiver));



                //分销金额
//                $value['share_money'] = sprintf('%.2f', ($value['first_price'] + $value['second_price'] + $value['third_price']));

                $share_detail_list = [];
                $share_detail_holder_list = [];
                //转单金额
                $order_transit = [];
                $md_profit = 0;
                if ((int)$value['order_type'] === 1) {
                    //转单
                    $order_transit = OrderTransit::find()->where(['order_id' => $value['id'], 'is_delete' => 0])->select('order_price')->column();
                    //极差分红
                    $share_detail_list = ShareDetail::find()->where(['store_id' => get_store_id(), 'type' =>
                        [
                            ShareDetail::TYPE_RANGE_PROFIT,
                            ShareDetail::TYPE_SHARE_PROFIT,
                            ShareDetail::TYPE_TWO_PLUS_ONE_SHARE_PROFIT,
                            ShareDetail::TYPE_TWO_PLUS_ONE_SHARE_TEAM_PROFIT,
                            ShareDetail::TYPE_RANGE_PROFIT_OLD,
                            ShareDetail::TYPE_TWO_PLUS_ONE_SHARE_TEAM_PROFIT_OLD,
                            ShareDetail::TYPE_FROST_PROFIT,
                        ], 'type_id' => $value['id']
                    ])->select('money')->column();
                    //股东分红
                    $share_detail_holder_list = BonusPoolDetail::find()->where(['order_id' => $value['id']])->select('money')->column();
//                    $share_detail_holder_list = ShareDetail::find()->where(['store_id' => get_store_id(), 'type' =>ShareDetail::TYPE_HOLDER_PROFIT,
//                        'type_id' => $value['id']
//                    ])->select('money')->column();
                    $order = Order::findOne($value['id']);
                    $share_holder_price = bcadd($order['old_holder_first_price'],
                        bcadd($order['old_holder_second_price'],
                            bcadd($order['old_holder_third_price'],
                                bcadd($order['holder_first_price'],
                                    bcadd($order['holder_second_price'], $order['holder_third_price'], 2)
                                    , 2)
                                , 2)
                            , 2)
                        , 2);
                    //门店收益
                    $md_profit = MdProfit::findOne(['order_id' => $value['id']])->total_profit ?: 0.00;
                }
                if ((int)$value['trade_status'] === 3 && (int)$value['is_sale'] === 1) {
                    $value['trade_status'] = '4';
                }
                $value['status'] = '';
                switch ($value['trade_status']) {
                    case 2:
                        $value['status'] = '待收货';
                        break;
                    case 3:
                        $value['status'] = '已收货';
                        break;
                    case 4:
                        $value['status'] = '已完成';
                        break;
                }
                unset($value['is_sale']);

                //成本价
                $value['cost_price'] = OrderDetail::find()->where(['order_id' => $value['id'], 'is_delete' => 0])->sum('cost_price') ?: 0.00;

                $value['OrderRefund'] = \app\models\OrderRefund::find()->where(['order_id' => $value['id'], 'is_delete' => 0])->orderBy('id DESC')->one();
                $value['md_profit'] = sprintf('%.2f', $md_profit);
                $value['transit_price'] = sprintf('%.2f', array_sum($order_transit));
                $value['share_detail_sub'] = sprintf('%.2f', array_sum($share_detail_list));
                $value['share_detail'] = sprintf('%.2f', array_sum($share_detail_holder_list));
                $value['share_detail'] = $value['share_detail'] > 0 ? $value['share_detail'] : $share_holder_price;
                $value['finally_price'] = sprintf('%.2f', round($value['pay_price'] - $value['transit_price'] - $value['md_profit'] - $value['share_detail_sub'] - $value['share_detail'] - $value['sharing_receiver'] - $value['share_money'], 2));//最终金额
            }
            if ($this->isExport) {
                $export_field = [

                        'id' => 'ID',
                        'pay_price' => '实际支付金额',
                        'cost_price' => '成本价',
                        'order_no' => '订单号',
                        'created_at' => '下单时间',
                        'user_id' => '用户id',
                        'confirm_time' => '收货时间',
                        'trade_status' => '状态',
                        'level_order_id' => '等级订单id',
                        'share_money' => '分销支出',
                        'coupon_sub_price' => '优惠券优惠金额',
                        'integral_difference_price' => '积分抵扣金额',
                        'order_type' => '订单类型序号',
                        'name' => '用户名称',
                        'avatar' => '用户头像',
                        'mobile' => '用户手机号',
                        'total_price' => '订单金额',
                        'sharing_receiver' => '平台抽成',
                        'status' => '状态',
                        'md_profit' => '门店支出',
                        'transit_price' => '转单支出',
                        'share_detail_sub' => '极差支出',
                        'share_detail' => '股东支出',
                        'finally_price' => '结余金额',


                ];

                $file_name = '财务列表';

                $data = [];
                foreach ($list as $item) {

                    $row = [];
                    foreach ($export_field as $key => $label) {
                        $row[$key] = $item[$key] ?? '';
                    }
                    $data[] = $row;
                }

                
                return SimpleExcelWriter::streamDownload($file_name . time() . '.csv')
                    ->noHeaderRow()
                    ->addRow(array_values($export_field))
                    ->addRows($data)
                    ->toBrowser();
            }

            return [
                'code' => 0,
                'msg' => 'success',
                'data' => [
                    'data' => $list,
                    'all_data' => $all_data,
                    'pageNo' => (int)$pageNo,
                    'totalCount' => (int)$count,
                ]
            ];
        } catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }
    }

    public function getSql($field = '') {
        try {
            $other_field = ", 0 as level_order_id, 0 as integral_difference_price, 0 as share_money";
            if ($field !== '') {
                //计算总额的sql
                switch ($field) {
                    case 'total_price':
                        $field2 = $field3 = $field1 = ' total_price, user_id';//cyy_order 与 cyy_order_pospal表存在此字段 支付金额+积分抵扣金额
                        $field4 = "pay_price as total_price, user_id";
                        break;
                    case 'pay_price':
                        $field4 = $field5 = $field2 = $field3 = $field1 = ' pay_price, user_id';
                        break;
                    case 'share_money':
                        $field3 = $field1 = ' IF (share_send_type = 1, first_price + second_price + third_price + limit_price + rebate, limit_price + rebate) as share_money, user_id';
                        $field4 = $field5 = $field2 = ' 0 as share_money ,user_id';
                        break;
                    case 'sharing_receiver':
                        $field4 = $field5 = $field2 = $field3 = $field1 = ' order_no, user_id';
                        break;
                    default ://md_profit - share_detail_sub - share_detail
                        $field1 = ' id as md_profit, user_id';
                        $field3 = $field4 = $field5 = $field2 = ' 0 as md_profit, user_id';
                        break;
                }
            } else {
                $field = ' id, pay_price, order_no, created_at, user_id';
                $field4 = $field2 = $field3 = $field1 = $field;
                $field1 .= ", confirm_time, trade_status, level_order_id, IF (share_send_type = 1, first_price + second_price + third_price + limit_price + rebate, limit_price + rebate) as share_money, coupon_sub_price, integral_difference_price, is_sale ";
                $field2 .= ", confirm_time, trade_status, coupon_sub_price {$other_field}, is_sale";
                $field3 .= ", confirm_time, trade_status, 0 as level_order_id, IF (share_send_type = 1, first_price + second_price + third_price + limit_price + rebate, limit_price + rebate) as share_money, coupon_sub_price, integral_difference_price, is_sale ";
                $field5 = $field4 .= ", pay_time as confirm_time, (2 + is_pay) as trade_status {$other_field}, 0 as coupon_sub_price, 1 as is_sale ";
            }
            $where = ' store_id = ' . get_store_id() . ' AND is_delete = 0 AND is_recycle = 0 AND trade_status NOT IN (-1, 1)';
            $re_where = '  store_id = ' . get_store_id() . ' AND is_delete = 0 AND is_recycle = 0';
            if ((int)$this->status > 1) {

                if (in_array((int)$this->status, [1, 2, 3])) {
                    $where .= " AND trade_status = {$this->status}";
                }
                if (in_array((int)$this->status, [2, 3])) {
                    $re_where .= " AND is_pay = 1";
                }
                if ((int)$this->status === 4) {
                    $where .= " AND is_sale = 1";
                } else {
                    $where .= " AND is_sale = 0";
                }
            }

            //下单开始日期
            if ($this->order_start_time) {
                $order_start_time = strtotime($this->order_start_time);
                $where .= " AND created_at >= {$order_start_time}";
                $re_where .= " AND created_at >= {$order_start_time}";
            }

            //下单结束日期
            if ($this->order_end_time) {
                $order_end_time = strtotime($this->order_end_time);
                $where .= " AND created_at <= {$order_end_time}";
                $re_where .= " AND created_at <= {$order_end_time}";
            }

            //确认收货开始时间
            if ($this->confirm_start_time) {
                $confirm_start_time = strtotime($this->confirm_start_time);
                $where .= " AND confirm_time >= {$confirm_start_time}";
                $re_where .= " AND created_at >= {$confirm_start_time}";
            }

            //确认收货结束时间
            if ($this->confirm_end_time) {
                $confirm_end_time = strtotime($this->confirm_end_time);
                $where .= " AND confirm_time <= {$confirm_end_time}";
                $re_where .= " AND created_at <= {$confirm_end_time}";
            }

            //订单号
            if ($this->order_no) {
                $where .= " AND order_no LIKE '%{$this->order_no}%'";
                $re_where .= " AND order_no LIKE '%{$this->order_no}%'";
            }

            //订单号
            $whereRefund = '';
            if (isset($this->has_refund) && $this->has_refund > -1) {
                $ref = 'SELECT order_id FROM cyy_order_refund WHERE store_id = ' . get_store_id();
                if($this->has_refund){
                    $whereRefund .= ' AND id IN (' . $ref . ')';
                }else{
                    $whereRefund .= ' AND id NOT IN (' . $ref . ')';
                }
            }


            $sql = "SELECT o.*, su.name, su.avatar, su.mobile FROM (SELECT {$field1}, 1 as order_type FROM cyy_order where {$where}{$whereRefund}";
            if(!$whereRefund){
            $sql .= " UNION ALL SELECT {$field2}, 2 as order_type FROM cyy_adopt_cost_order where {$where}";
            $sql .= " UNION ALL SELECT {$field3}, 3 as order_type FROM cyy_order_pospal where {$where}";

            $sql .= " UNION ALL SELECT {$field4}, 4 as order_type FROM cyy_level_order where {$re_where} AND is_pay = 1";
            }
//            $sql .= " UNION ALL SELECT {$field5}, 5 as order_type FROM cyy_re_order where {$re_where} AND is_pay = 1";
            $sql .= " ) as o LEFT JOIN cyy_user u ON o.user_id = u.id LEFT JOIN cyy_saas_user su ON su.mobile = u.binding";

//            $query = Order::find()->alias('o')->leftJoin(['u' => User::tableName()], 'o.user_id = u.id')
//                ->leftJoin(['su' => SaasUser::tableName()], 'u.binding = su.mobile')
//                ->where(['o.store_id' => get_store_id(), 'o.trade_status' => [2, 3], 'o.is_delete' => 0]);

            //状态 1全部 2待收货 3收货
//            if ((int)$this->status > 1) {
//                $query->andWhere(['o.trade_status' => $this->status]);
//            }

            //用户名称
            if ($this->user_name) {
                $sql .= " where su.name LIKE '%{$this->user_name}%'";
            }
            return [
                'code' => 0,
                'msg' => 'success',
                'data' => $sql
            ];
        } catch (\Exception $e) {
            return [
                'code' => 1,
                'msg' => $e->getMessage()
            ];
        }

    }
}