order_consumption_system_new/app/Models/OrderCost.php

<?php

namespace App\Models;

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

class OrderCost extends Model
{
    protected $table = 'order_cost';
    public $timestamps = false;
    const ORDER_COST_MONTH_PRE = 'OrderConsumption.OrderCostMonthRds_';
    const ORDER_OFFLINE_MONTH_PRE = 'OrderConsumption.OrderOfflineMonthRds_';

    const COLUMNS = [
        'task_id',
        'order_part_id',
        'month',
        'cost',
        'traffic_boost_money',
        'traffic_boost_refund',
        'traffic_boost_sum',
        'top_fee',
        'actual_cost_sum',
        'estimate_cost',
        'estimate_traffic_boost_refund',
        'cost_invoice_date',
        'confirmed_cost_includes_tax',
        'confirmed_cost',
        'cost_owner',
        'offline_subsidy_company',
        'offline_subsidy_amount',
        'rebate',
        'offline_invoice_date',
        'offline_confirmed_cost_includes_tax',
        'offline_confirmed_cost',
        'offline_celeb_name',
        'offline_pay_date',
        'ratio_of_consumption_to_top_rebate',
        'ratio_of_consumption_to_top_income',
        'final_ratio_of_consumption_to_top_rebate',
        'final_ratio_of_consumption_to_top_income',
        'flow_boost_rebate_ratio',
        'flow_boost_rebate_income',
        'final_flow_boost_rebate_ratio',
        'final_flow_boost_rebate_income',
        'red_invoice_date',
        'refund_invoice_date',
        'estimate_rebate_total',
        'rebate_total',
        'document_number_second',
        'document_number_third',
        'confirmation_certificate_third',
        'document_number_four',
        'confirmation_certificate_four',
        'document_number_five',
        'document_number_six',
        'confirmation_certificate_five',
        'confirmation_certificate_six',
        'order_id',
        'if_public',
        'is_pdd_refund',
        'cost_verify_status',
        'note',
        'is_cash',
        'cost_sign_company'
    ];

    /**
     * 生成制定月份缓存
     */
    public static function setCostRds($month)
    {
        $sql = "select oc.*,
                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
            from order_cost as oc 
            left join order_income oi on oi.order_id = oc.order_id 
            where oc.enable=1 and oc.month='{$month}' 
            order by oc.id desc ";
        $data = DB::select($sql);

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

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

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

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

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

    /**
     * 生成制定月份缓存线下报表
     */
    public static function setOfflineRds($month)
    {
        $sql = "select oc.id,
                oc.task_id,
                oc.month,
                oc.order_part_id,
                oc.order_id,
                oc.offline_subsidy_company,
                oc.offline_subsidy_amount,
                oc.offline_celeb_name,
                oc.offline_pay_date,
                oc.status,
                oc.cost_verify_status,
                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
            from order_cost as oc 
            left join order_income oi on oi.order_id = oc.order_id 
            where oc.enable=1 and oc.month='{$month}' and offline_subsidy_amount>0
            order by oc.id desc ";
        $data = DB::select($sql);

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

        #数据处理
        foreach($data as &$v){
            $v['task_id'] .= '';
            //处理超长小数百分比显示问题
            $v['month'] = substr($v['month'], 0, 7);
        }

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

    /**
     * 添加订单
     */
    public static function addData($params)
    {
    	$id = DB::table('order_cost')->insertGetId($params);
        self::orderCalculateSave($id);
        return $id;
    }

    /**
     * 订单补充/汇总计算数据
     */
    public static function orderCalculateSave($id)
    {
        $order = self::where('id', $id)->first()->toArray();
        $data = [];
        self::orderCalculate($order, $data);

        if( empty($data) ){
            return false;
        }
        $res = self::where('id', $id)->update($data);

        return $res;
    }

    /**
     * 订单计算公式-统一
     * 
     */
    public static function orderCalculate($order = [], &$params = [], $status = 0)
    {
        if(empty($order)){
            $order = [
                'cost' => null,
                'top_fee' => null,
                'traffic_boost_refund' => null,
                'offline_subsidy_amount' => null,
                'traffic_boost_money' => null,
                'sr_rate' => null,
                'ratio_of_consumption_to_top_rebate' => null,
                'flow_boost_rebate_ratio' => null,
                'offline_confirmed_cost_includes_tax' => null,
                'final_ratio_of_consumption_to_top_rebate' => null,
                'final_flow_boost_rebate_ratio' => null,
            ];
        }

        $cost = isset($params['cost']) ? $params['cost'] : $order['cost'];
        $top_fee = isset($params['top_fee']) ? $params['top_fee'] : $order['top_fee'];
        $traffic_boost_refund = isset($params['traffic_boost_refund']) ? $params['traffic_boost_refund'] : $order['traffic_boost_refund'];
        $offline_subsidy_amount = isset($params['offline_subsidy_amount']) ? $params['offline_subsidy_amount'] : $order['offline_subsidy_amount'];
        $traffic_boost_money = isset($params['traffic_boost_money']) ? $params['traffic_boost_money'] : $order['traffic_boost_money'];
        
        $sr_rate = isset($params['sr_rate']) ? $params['sr_rate'] : $order['sr_rate'];
        #新加 媒体名称影响返点
        $ratio_of_consumption_to_top_rebate = isset($params['ratio_of_consumption_to_top_rebate']) ? $params['ratio_of_consumption_to_top_rebate'] : $order['ratio_of_consumption_to_top_rebate'];
        $flow_boost_rebate_ratio = isset($params['flow_boost_rebate_ratio']) ? $params['flow_boost_rebate_ratio'] : $order['flow_boost_rebate_ratio'];

        $offline_confirmed_cost_includes_tax = isset($params['offline_confirmed_cost_includes_tax']) ? $params['offline_confirmed_cost_includes_tax'] : $order['offline_confirmed_cost_includes_tax'];
        
        #上传成本，返货公式
        $params['actual_cost_sum'] = $cost + $top_fee; //后台实付成本合计金额 = 订单成本金额消耗+置顶费
        $params['rebate'] =  $offline_subsidy_amount * $sr_rate; //上饶一笑金额 = 线下补款金额 * 比例

        if($status<4){
            //财务锁单后暂估，当月数据不再变，只更新最终数据
            $params['estimate_cost'] = $cost;
            $params['estimate_traffic_boost_refund'] = $traffic_boost_refund;
            $params['ratio_of_consumption_to_top_income'] = round($params['actual_cost_sum'] * $ratio_of_consumption_to_top_rebate, 2); //消耗与置顶返点收入
            $params['flow_boost_rebate_income'] = round($traffic_boost_money * $flow_boost_rebate_ratio, 2); //流量助推返点收入
            $params['estimate_rebate_total'] = $params['flow_boost_rebate_income'] + $params['ratio_of_consumption_to_top_income'];//暂估返点收入=消耗与置顶返点收入+流量助推返点收入
        }

        #点确认返点，更新逻辑
        //TODO 不确定
//        if( isset($params['final_ratio_of_consumption_to_top_rebate']) || isset($params['final_flow_boost_rebate_ratio']) ){
//            $final_ratio_of_consumption_to_top_rebate = isset($params['final_ratio_of_consumption_to_top_rebate']) ? $params['final_ratio_of_consumption_to_top_rebate'] : $order['final_ratio_of_consumption_to_top_rebate'];
//            $final_flow_boost_rebate_ratio = isset($params['final_flow_boost_rebate_ratio']) ? $params['final_flow_boost_rebate_ratio'] : $order['final_flow_boost_rebate_ratio'];
//
//            $params['final_ratio_of_consumption_to_top_income'] = round($params['actual_cost_sum'] * $final_ratio_of_consumption_to_top_rebate, 2); //消耗与置顶返点-确认收入
//            $params['final_flow_boost_rebate_income'] = round($traffic_boost_money * $final_flow_boost_rebate_ratio, 2); //流量助推返点-确认收入
//            $params['rebate_total'] = $params['final_ratio_of_consumption_to_top_income'] + $params['final_flow_boost_rebate_income'];//确认返点收入=消耗与置顶返点收入+流量助推返点收入
//        }
        
        $params['offline_confirmed_cost'] = round($offline_confirmed_cost_includes_tax/1.06, 2); //线下不含税

        return $params;
        
    }

    /**
     * 拼接条件语句  
     */
    public static function getSearchQuery($search)
    {
        return DB::table('order_cost as oc')
            ->leftJoin('order_income as oi', 'oi.order_id', '=', 'oc.order_id')
            ->where('oc.enable', 1)
            ->where(function($query) use($search) {
                //if(isset($search['saler_id']) && $search['saler_id']>0) $query->whereRaw('(saler_id='.$user->id.' or handlers_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('oc.cost_owner', current($search['cost_owner']) );
                    } else {
                        $query->whereIn('oc.cost_owner', $search['cost_owner']);
                    }
                }

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

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

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

                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['ratio_of_consumption_to_top_rebate']) && !empty($search['ratio_of_consumption_to_top_rebate']) ){
                    if( count($search['ratio_of_consumption_to_top_rebate']) == 1){
                        $query->where('oc.ratio_of_consumption_to_top_rebate', current($search['ratio_of_consumption_to_top_rebate']) );
                    } else {
                        $query->whereIn('oc.ratio_of_consumption_to_top_rebate', $search['ratio_of_consumption_to_top_rebate']);
                    }
                }

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

                if(isset($search['task_id']) && !empty($search['task_id'])) $query->where('oc.task_id', 'like', $search['task_id'].'%');
                if(isset($search['is_refund'])) $query->where('oc.is_pdd_refund', $search['is_refund']);
                if(isset($search['if_public'])) $query->where('oc.if_public', $search['if_public']);
                if(isset($search['month'])) $query->where('oc.month', $search['month']);
                if(isset($search['order_part_id'])) $query->where('oi.order_part_id', $search['order_part_id']);
               
            });
    }

    
}