order_consumption_system_new/app/Models/OrderIncome....

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2021/4/6
 * Time: 17:21
 */

namespace App\Models;


use App\RedisModel;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class OrderIncome extends Model
{
    protected $table = 'order_income';

    public $timestamps = false;
    protected static $unguarded = true;

    const ORDER_INCOME_MONTH_PRE = 'OrderConsumption.OrderIncomeMonthRds_';

    const COLUMNS = [
        'task_id',
        'order_id',
        'order_part_id',
        'month',
        'advertiser_account',
        'advertiser_id',
        'salesman',
        'saler_id',
        'handler',
        'handlers_id',
        'customer_name',
        'final_customer_name',
        'origin_customer_name',
        'project_name',
        'launch_start_date',
        'launch_end_date',
        'launch_platform',
        'media_name',
        'cost_company_name',
        'cost_owner',
        'amount',
        'origin_amount',
        'internet_celebrity_name',
        'internet_celebrity_id',
        'estimated_amount',
        'final_amount',
        'document_number_first',
        'billing_date_first',
        'invoice_amount_first',
        'billing_date_second',
        'invoice_amount_second',
        'confirmation_certificate_first',
        'customer_rebate',
        'actual_consumption_rebate_ratio',
        'link_fee_rebate_ratio',
        'flow_boost_back_point_ratio',
        'top_fee_rebate_proportion',
        'actual_consumption_before',
        'link_fee_before',
        'flow_boost_back_point_before',
        'top_fee_before',
        'before_folding_combined',
        'actual_consumption_after',
        'link_fee_after',
        'flow_boost_back_point_after',
        'top_fee_after',
        'discounted_combined',
        'rebate_spending_amount',
        'final_rebate_spending_amount',
        'document_number_seven',
        'confirmation_certificate_seven',
        'rebate_expense_receiving_unit',
        'income_red_invoice_date',
        'income_red_invoice_amount',
        'statements_status',
        'invoice_num',
        'saler_note',
        'is_pdd_refund',
        //'traffic_boost_sum',
        //'cost',
        'if_public',
        'payment_date',
        'uninvoiced_amount',
        'cpa_unit_income_price',
        'cpa_single_quantity',
        'verify_status',
        'company',
        'customer_rebate_amount',
        'cpa_actual_consumption',
        'rebate_spending_ratio'
    ];

    /**
     * 为私单自动生成任务id
     * @param $prefix
     * @return string
     */
    public static function createTaskId($prefix)
    {
        return $prefix.'-'.time().rand(1000, 9999);
    }

    /*
     * 废弃
     */
    public static function setIncomeRds($month)
    {
        $sql = "select
                id,
                month,
                task_id,
                order_part_id,
                order_id,
                company,
                advertiser_account,
                advertiser_id,
                salesman,
                saler_id,
                handler,
                handlers_id,
                customer_name,
                final_customer_name,
                origin_customer_name,
                project_name,
                launch_start_date,
                launch_end_date,
                launch_platform,
                media_name,
                cost_company_name,
                cost_owner,
                amount,
                final_amount,
                internet_celebrity_name,
                internet_celebrity_id,
                customer_rebate,
                actual_consumption_rebate_ratio,
                link_fee_rebate_ratio,
                flow_boost_back_point_ratio,
                top_fee_rebate_proportion,
                actual_consumption_before,
                link_fee_before,
                flow_boost_back_point_before,
                top_fee_before,
                before_folding_combined,
                actual_consumption_after,
                link_fee_after,
                flow_boost_back_point_after,
                top_fee_after,
                discounted_combined,
                rebate_spending_amount,
                rebate_spending_ratio,
                rebate_expense_receiving_unit,
                income_red_invoice_date,
                income_red_invoice_amount,
                statements_status,
                invoice_status,
                invoice_num,
                saler_note,
                is_pdd_refund,
                if_public,
                payment_date,
                cpa_unit_income_price,
                cpa_single_quantity,
                cpa_actual_consumption,
                verify_status,
                customer_rebate_amount,
                status
            from order_income
            where enable=1 and month='{$month}' 
            order by id desc ";
        $data = DB::select($sql);

        $data = json_decode(json_encode($data), true);
        $orderIds = array_column($data, 'order_id');
        $invoiceData = CustomerInvoices::invoiceHistory($orderIds);

        #数据处理
        foreach($data as &$v){
            $v['task_id'] .= '';

            $item = [
                'first_invoice_amount' => 0,
                'first_invoice_date' => null,
                'invoice_num' => 0,
                'second_invoice_amount' => 0,
                'second_invoice_date' => null,
                'uninvoiced_amount' => $v['final_amount'],
            ];

            //处理超长小数百分比显示问题
            $v['actual_consumption_rebate_ratio'] = Order::longDecimalFormat($v['actual_consumption_rebate_ratio']);
            $v['link_fee_rebate_ratio'] = Order::longDecimalFormat($v['link_fee_rebate_ratio']);
            $v['flow_boost_back_point_ratio'] = Order::longDecimalFormat($v['flow_boost_back_point_ratio']);
            $v['top_fee_rebate_proportion'] = Order::longDecimalFormat($v['top_fee_rebate_proportion']);
            $v['rebate_spending_ratio'] = Order::longDecimalFormat($v['rebate_spending_ratio']);

            $v['month'] = substr($v['month'], 0, 7);

            #拼接开票信息
            if (isset($invoiceData[$v['order_id']])) {
                $invoiceInfo = $invoiceData[$v['order_id']];
            } else {
                $invoiceInfo = $item;
            }

            $v = array_merge($v, $invoiceInfo);

            $v['if_public'] = $v['if_public'] == 1 ? '是' : '';
            $v['is_pdd_refund'] = $v['is_pdd_refund'] == 1 ? '是' : '';
        }

        $rk = static::ORDER_INCOME_MONTH_PRE . $month;
        RedisModel::set($rk, json_encode($data));
        RedisModel::expire($rk, 86400);

        return $data;
    }

    /*
    * 根据检索条件获取收入表数据
    */
    public static function getSearchIncomeQuery($search, $user)
    {
        return OrderIncome::where('enable', 1)
            ->where(function($query) use($search, $user) {
                if (isset($search['is_statements']) && 1 == $search['is_statements']) {
                    if($user->role_id == 11) {
                        $query->whereRaw('(handlers_id='.$user->id.')');
                    }
                } else {
                    if($user->role_id == 11) {
                        $query->whereRaw('(saler_id='.$user->id.' or handlers_id='.$user->id.')');
                    }
                }

                if(isset($search['company']) && !empty($search['company'])) {
                    if( count($search['company']) == 1){
                        $query->where('company', current($search['company']) );
                    } else {
                        $query->whereIn('company', $search['company']);
                    }
                }

                if(isset($search['advertiser_account']) && !empty($search['advertiser_account'])) {
                    if ( count($search['advertiser_account']) == 1) {
                        $query->where('advertiser_account', current($search['advertiser_account']));
                    } else {
                        $query->whereIn('advertiser_account', $search['advertiser_account']);
                    }
                }

                if(isset($search['media_name']) && !empty($search['media_name'])) {
                    if ( count($search['media_name']) == 1) {
                        $query->where('media_name', current($search['media_name']));
                    } else {
                        $query->whereIn('media_name', $search['media_name']);
                    }
                }

                if(isset($search['internet_celebrity_name']) && !empty($search['internet_celebrity_name'])) {
                    if ( count($search['internet_celebrity_name']) == 1) {
                        $query->where('internet_celebrity_name', current($search['internet_celebrity_name']));
                    } else {
                        $query->whereIn('internet_celebrity_name', $search['internet_celebrity_name']);
                    }
                }

                if(isset($search['handler']) && !empty($search['handler'])) {
                    if ( count($search['handler']) == 1) {
                        $query->where('handler', current($search['handler']));
                    } else {
                        $query->whereIn('handler', $search['handler']);
                    }
                }

                if(isset($search['customer_name']) && !empty($search['customer_name'])) {
                    if ( count($search['customer_name']) == 1) {
                        $query->where('customer_name', current($search['customer_name']));
                    } else {
                        $query->whereIn('customer_name', $search['customer_name']);
                    }
                }

                if(isset($search['project_name']) && !empty($search['project_name'])) {
                    if( count($search['project_name']) == 1) {
                        $query->where('project_name', current($search['project_name']));
                    } else {
                        $query->whereIn('project_name', $search['project_name']);
                    }
                }

                if(isset($search['cost_company_name']) && !empty($search['cost_company_name'])) {
                    if(count($search['cost_company_name']) == 1) {
                        $query->where('cost_company_name', current($search['cost_company_name']));
                    } else {
                        $query->whereIn('cost_company_name', $search['cost_company_name']);
                    }
                }

                if(isset($search['cost_owner']) && !empty($search['cost_owner'])) {
                    if (count($search['cost_owner']) == 1) {
                        $query->where('cost_owner', current($search['cost_owner']));
                    } else {
                        $query->whereIn('cost_owner', $search['cost_owner']);
                    }
                }
                if(isset($search['rebate_expense_receiving_unit']) && !empty($search['rebate_expense_receiving_unit'])) {
                    if ( count($search['rebate_expense_receiving_unit']) == 1) {
                        $query->where('rebate_expense_receiving_unit', current($search['rebate_expense_receiving_unit']));
                    } else {
                        $query->whereIn('rebate_expense_receiving_unit', $search['rebate_expense_receiving_unit']);
                    }
                }

                if(isset($search['advertiser_id']) && !empty($search['advertiser_id'])) {
                    if (count($search['advertiser_id']) == 1) {
                        $query->where('advertiser_id', current($search['advertiser_id']));
                    } else {
                        $query->whereIn('advertiser_id', $search['advertiser_id']);
                    }
                }
                if(isset($search['if_public'])) $query->where('if_public', (int)$search['if_public']);
                if(isset($search['launch_platform']) && !empty($search['launch_platform'])) {
                    if(count($search['launch_platform']) == 1) {
                        $query->where('launch_platform', current($search['launch_platform']));
                    } else {
                        $query->whereIn('launch_platform', $search['launch_platform']);
                    }
                }

                if(isset($search['month']) && $search['month']) {
                    if(count($search['month']) == 1) {
                        $query->where('month', '=', date('Y-m-01', strtotime(current($search['month'])) ) );
                    } else {
                        $monthList = array_map(function($value){
                            return date('Y-m-01', strtotime($value));
                        }, $search['month']);

                        $query->whereIn('month', $monthList);
                    }
                }

                if(isset($search['launch_end_date_start']) && $search['launch_end_date_start']) $query->where('launch_end_date', '>=', $search['launch_end_date_start']);
                if(isset($search['launch_end_date_end']) && $search['launch_end_date_end']) $query->where('launch_end_date', '<=', $search['launch_end_date_end']);

                if(isset($search['amount_min']) && $search['amount_min']) $query->where('amount', '>=', $search['amount_min']);
                if(isset($search['amount_max']) && $search['amount_max']) $query->where('amount', '<=', $search['amount_max']);

                if(isset($search['final_amount_min']) && $search['final_amount_min']) $query->where('final_amount', '>=', $search['final_amount_min']);
                if(isset($search['final_amount_max']) && $search['final_amount_max']) $query->where('final_amount', '<=', $search['final_amount_max']);

//                if(isset($search['cost_min']) && $search['cost_min']) $query->where('cost', '>=', $search['cost_min']);
//                if(isset($search['cost_max']) && $search['cost_max']) $query->where('cost', '<=', $search['cost_max']);

                if(isset($search['income_red_invoice_date_start']) && $search['income_red_invoice_date_start']) $query->where('income_red_invoice_date', '>=', $search['income_red_invoice_date_start']);
                if(isset($search['income_red_invoice_date_end']) && $search['income_red_invoice_date_end']) $query->where('income_red_invoice_date', '<=', $search['income_red_invoice_date_end']);

                if(isset($search['income_red_invoice_amount_min']) && $search['income_red_invoice_amount_min']) $query->where('income_red_invoice_amount', '>=', $search['income_red_invoice_amount_min']);
                if(isset($search['income_red_invoice_amount_max']) && $search['income_red_invoice_amount_max']) $query->where('income_red_invoice_amount', '<=', $search['income_red_invoice_amount_max']);

                if(isset($search['verify_status']) && $search['verify_status']>0){
                    $status = $search['verify_status'];

                    switch ($status) {
                        case 1:
                            $status = [1, 2];
                            break;
                        case 2:
                            $status = [4];
                            break;
                        case 3:
                            $status = [3, 5];
                            break;
                        default:
                            $status = [];
                            break;
                    }

                    if(!empty($status)) $query->whereIn('verify_status', $status);
                }

                if(!empty($search['statements_status'])) {
                    if(-1 == $search['statements_status']) {
                        $search['statements_status'] = 0;
                    }
                    $query->where('statements_status', $search['statements_status']);
                }

                if(!empty($search['task_id'])) $query->where('task_id', 'like', '%'.$search['task_id'].'%');

                if(!empty($search['order_part_id'])) $query->where('order_part_id', 'like', '%'.$search['order_part_id'].'%');

//                $query->where('order_id', '>', '');
            });
    }
}