order_consumption_system_new/app/Models/OrderMcn.php

<?php
/**
 * Created by PhpStorm.
 * User: shensong
 * Date: 2021/4/9
 * Time: 15:53
 */

namespace App\Models;


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

class OrderMcn extends Model
{
    protected $table = 'order_mcn';
    public $timestamps = false;
    protected static $unguarded = true;

    const ORDER_MCN_MONTH_PRE = 'OrderConsumption.OrderMcnMonthRds_';

    const COLUMNS = [
    	'task_id',
		'original_month',
		'order_id',
		'month',
		'mcn_handler',
		'mcn_name',
		'mcn_rebate',
		'mcn_handler_id',
		'mcn_user_id',
		'mcn_user',
		'agreed_rebate_percentage',
		'organization_amount',
		'original_mcn_rebate',
		'mcn_final_amount',
		'is_ca',
        'mcn_invoice_amount',
        'mcn_invoice_date',
        'mcn_document_number',
        'mcn_confirmation_certificate'
    ];

    public static function setMcnRds($month)
    {
        $sql = "select om.*,
                oi.company,
                oi.advertiser_account,
                oi.advertiser_id,
                oi.salesman,
                oi.saler_id,
                oi.handler,
                oi.handlers_id,
                oi.customer_name,
                oi.final_customer_name,
                oi.origin_customer_name,
                oi.project_name,
                oi.launch_start_date,
                oi.launch_end_date,
                oi.launch_platform,
                oi.media_name,
                oi.cost_company_name,
                oi.cost_owner,
                oi.amount,
                oi.internet_celebrity_name,
                oi.internet_celebrity_id,
                oi.status
            from order_income as oi 
            left join order_mcn as om on oi.order_id = om.order_id 
            where om.enable=1 and om.month='{$month}' 
            order by om.id desc ";
        $data = DB::select($sql);

        $data = json_decode(json_encode($data), true);

        #数据处理
        foreach($data as &$v){
            $v['task_id'] .= '';
            //处理超长小数百分比显示问题
            $v['agreed_rebate_percentage'] = Order::longDecimalFormat($v['agreed_rebate_percentage']);

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

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

        return $data;
    }

    /**
     * 拼接条件语句
     */
    public static function getSearchQuery($search, $user)
    {
        return DB::table('order_mcn as om')
            ->leftJoin('order_income as oi', 'oi.order_id', '=', 'om.order_id')
            ->where('om.enable', 1)
            ->where(function($query) use($search, $user) {
                //if(isset($search['saler_id']) && $search['saler_id']>0) $query->whereRaw('(saler_id='.$user->id.' or handlers_id='.$user->id.')');
                if (isset($search['is_statements']) && 1 == $search['is_statements']) {
                    if(in_array($user->role_id, [9, 13])) {
                        // 2023-06-14 白凤要求新来的媒介郭姣蓉 113 可以看到和她 13 一样的数据
                        if(113 == $user->id) {
                            $query->whereRaw('(mcn_handler_id = 113 or mcn_handler_id = 19)');
                        } else {
                            $query->whereRaw('(mcn_handler_id='.$user->id.')');
                        }
                    }
                } else {
                    if(in_array($user->role_id, [9, 13]) && $user->id !=53) {
                        if(113 == $user->id) {
                            $query->whereRaw('(mcn_user_id=13 or mcn_handler_id=13 or mcn_user_id = 113 or mcn_handler_id = 113)');
                        } else {
                            $query->whereRaw('(mcn_user_id='.$user->id.' or mcn_handler_id='.$user->id.')');
                        }
                    }
                }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                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('om.verify_status', $status);
                }

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

                if(isset($search['month']) && $search['month']) {
                    if(count($search['month']) == 1) {
                        $query->where('om.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('om.month', $monthList);
                    }
                }

                if(isset($search['task_id']) && !empty($search['task_id'])) $query->where('om.task_id', 'like', '%'.$search['task_id'].'%');
//                if(isset($search['month'])) $query->where('om.month', $search['month']);
                if(isset($search['order_part_id'])) $query->where('oi.order_part_id', 'like', '%'.$search['order_part_id']);

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