order_consumption_system_new/app/Models/CustomerInvo...

<?php

namespace App\Models;

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

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

    public static $typeList = [1,3,4,5,6];

    public static function invoiceHistory($orderIds)
    {
        $details = CustomerInvoiceOrders::select('inv_id', 'order_id', 'invoice_amount')
            ->whereIn('order_id', $orderIds)->where('enable', 1)
            ->orderBy('id', 'asc')->get();

        $orders = Order::select('id', 'task_id', 'customer_name', 'billing_date', 'final_amount')
            ->whereIn('id', $orderIds)->where('enable', 1)
            ->get()->keyBy('id')->toArray();
        $inv_ids = $details->pluck('inv_id');

        $inv_ids = json_decode(json_encode($inv_ids), 1);
        $inv_ids = array_unique($inv_ids);

        $typeList = self::$typeList;
        if (empty($inv_ids)) {
            $invoices = [];
        } else {
            $sql = 'select ci.* from customer_invoices as ci left join statements as s on s.id = ci.statements_id where 
            s.type in ('.implode(',', $typeList).') and ci.status = 4 and ci.is_invoiced=1 and ci.id in ('.implode(',', $inv_ids) . ') and ci.enable = 1
            and s.enable = 1';

            $invoices = \DB::select($sql);
            $invoices = json_decode(json_encode($invoices), 1);
            $invoices = array_column($invoices, null, 'id');
        }

        $data = array();
        foreach($details->toArray() as $k=>&$v){
            $inv_id = $v['inv_id'];

            if(!isset($invoices[$inv_id])){
                continue;
            }

            $invoice = $invoices[$inv_id];
            $order = $orders[$v['order_id']];

            if(!isset($data[$v['order_id']])) {
                //第一次开票
                $data[$v['order_id']] = [
                    'first_invoice_amount' => $v['invoice_amount'],
                    'first_invoice_date' => !empty($invoice['invoiced_time']) ? substr($invoice['invoiced_time'], 0, 10) : null,
                    'invoice_num' => 1,
                    'second_invoice_amount' => 0,
                    'second_invoice_date' => null,
                    'uninvoiced_amount' => $order['final_amount'] - $v['invoice_amount'],
                    'invoice_amount' => $v['invoice_amount'],
                    'billing_date' => !empty($invoice['invoiced_time']) ? substr($invoice['invoiced_time'], 0, 10) : null,
                ];
            } else {
                //第二次开票
                $data[$v['order_id']] = [
                    'invoice_num' => 2,
                    'first_invoice_amount' => $data[$v['order_id']]['first_invoice_amount'],
                    'first_invoice_date' => $data[$v['order_id']]['first_invoice_date'],
                    'second_invoice_amount' => $v['invoice_amount'],
                    'second_invoice_date' => !empty($invoice['invoiced_time']) ? substr($invoice['invoiced_time'], 0, 10) : null,
                    'uninvoiced_amount' => $data[$v['order_id']]['uninvoiced_amount'] - $v['invoice_amount'],
                    'invoice_amount' => ($data[$v['order_id']]['first_invoice_amount'] + $v['invoice_amount']),
                    'billing_date' => !empty($invoice['invoiced_time']) ? substr($invoice['invoiced_time'], 0, 10) : null,
                ];
            }
//            $data[] = [
//                'task_id' => $order['task_id'],
//                'invoice_amount' => $v['invoice_amount'],
//                'cdate' => $order['billing_date'],
//                'created_at' => $invoice['created_at']
//            ];
        }

        return $data;
    }

    public static function getOrderIdsByHandler($search) {
        # 根据月份以及经手人姓名获取初始订单id
        $orderIdList = Order::query()->where('handler', current($search['handler']))
            ->where(function($query) use ($search) {
                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);
                    }
                }
            })->where('enable', 1)->pluck('id')->toArray();

        # 根据订单id获取对应的发票id
        $customerInvoiceIdList = CustomerInvoiceOrders::where('enable', 1)
            ->whereIn('order_id', $orderIdList)->pluck('inv_id')->toArray();

        # 根据查询到的发票id获取已经成功开票的发票id
        $successCustomerInvoiceIdList = CustomerInvoices::join('statements', 'statements.id', 'customer_invoices.statements_id')->whereIn('customer_invoices.id', $customerInvoiceIdList)
            ->where('customer_invoices.enable', 1)->where('customer_invoices.is_invoiced', 1)->where('statements.type', '!=', 2)->pluck('customer_invoices.id')->toArray();

        # 根据成功开票的发票id再次查询对应的订单id
        $successOrderIdList = CustomerInvoiceOrders::where('enable', 1)
            ->whereIn('inv_id', $successCustomerInvoiceIdList)->pluck('order_id')->toArray();

        # 对订单id进行交集运算
        $actualOrderIdList = array_intersect($orderIdList, $successOrderIdList);

        return $actualOrderIdList;
    }

    public static function getOrderIdsByMcnHandler($search) {
        # 根据月份以及经手人姓名获取初始订单id
        $orderIdList = Order::where('month', $search['month'])->where('mcn_handler', current($search['mcn_handler']))
            ->where('enable', 1)->pluck('id')->toArray();

        # 根据订单id获取对应的发票id
        $customerInvoiceIdList = CustomerInvoiceOrders::where('enable', 1)
            ->whereIn('order_id', $orderIdList)->pluck('inv_id')->toArray();

        # 根据查询到的发票id获取已经成功开票的发票id
        $successCustomerInvoiceIdList = CustomerInvoices::join('statements', 'statements.id', 'customer_invoices.statements_id')->whereIn('customer_invoices.id', $customerInvoiceIdList)
            ->where('customer_invoices.enable', 1)->where('customer_invoices.is_invoiced', 1)->where('statements.type', 2)->pluck('customer_invoices.id')->toArray();

        # 根据成功开票的发票id再次查询对应的订单id
        $successOrderIdList = CustomerInvoiceOrders::where('enable', 1)
            ->whereIn('inv_id', $successCustomerInvoiceIdList)->pluck('order_id')->toArray();

        # 对订单id进行交集运算
        $actualOrderIdList = array_intersect($orderIdList, $successOrderIdList);

        return $actualOrderIdList;
    }
}