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 invoiceHistoryOld($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 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,
                    'invoice_id' => !empty($invoice['id']) ? $invoice['id'] : null,
                    'collection_remark' => !empty($invoice['collection_remark']) ? $invoice['collection_remark'] : 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,
                    'invoice_id' => !empty($invoice['id']) ? $invoice['id'] : null,
                    'collection_remark' => !empty($invoice['collection_remark']) ? $invoice['collection_remark'] : null,
                ];
            }
        }

        return $data;
    }

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

        $inv_ids = $details->pluck('inv_id');

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

        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 =2 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];

            if(!isset($data[$v['order_id']])) {
                //第一次开票
                $data[$v['order_id']] = [
                    'mcn_invoice_id' => !empty($invoice['id']) ? $invoice['id'] : null,
                    'mcn_collection_remark' => !empty($invoice['collection_remark']) ? $invoice['collection_remark'] : null,
                ];
            }
        }

        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;
    }

    public static function getSearchModel($search, $user) {
        $query = self::query();

        if (isset($search['id']) && !empty($search['id'])) {
            $query->where('id', $search['id']);
        }
        if (isset($search['statements_id']) && !empty($search['statements_id'])) {
            $query->where('statements_id', $search['statements_id']);
        }
        if (isset($search['status']) && is_numeric($search['status']) ){
            $query->where('status', $search['status'])->where('is_show', 1);
        }
        if( isset($search['customer_name']) && !empty($search['customer_name']) ){
            $query->where('customer_name', 'like', '%'.$search['customer_name'].'%');
        }
        if (isset($search['cdate']) && !empty($search['cdate'])) {
            $query->where('applicant_time', '>=', $search['cdate'].' 00:00:00');
            $query->where('applicant_time', '<=', $search['cdate'].' 23:59:59');
        }
        if (isset($search['handler_id']) && !empty($search['handler_id'])) {
            $query->where('handler_id', $search['handler_id']);
        }
        if (isset($search['company']) && !empty($search['company'])) {
            $query->where('company', $search['company']);
        }
        if (in_array($user->role_id, [10, 17, 18, 21])) {
            // 财务不看商务待审核的发票
            $query->where('status', '>=', 2);
        }
        if (21 == $user->role_id) {
            $query->where('company', 'like', '%海南%');
        }
        if (17 == $user->role_id) {
            $query->where('company', '上饶市合一科技有限公司');
        }
        if (isset($search['inv_list']) && is_array($search['inv_list']) && !empty($search['inv_list'])) {
            $query->whereIn('id', $search['inv_list']);
        }
        if (isset($search['approved_start_date']) && !empty($search['approved_start_date'])) {
            $query->where('confirm_verify_at', '>=', $search['approved_start_date'])->where('status', 4);
        }
        if (isset($search['approved_end_date']) && !empty($search['approved_end_date'])) {
            $query->where('confirm_verify_at', '<=', $search['approved_end_date'].'23:59:59')->where('status', 4);
        }
        if (isset($search['pre_audit_status']) && is_numeric($search['pre_audit_status'])) {
            $query->where('pre_audit_status', $search['pre_audit_status']);
        }
        if (isset($serach['reject_inv_list']) && !empty($search['reject_inv_list'])) {
            $query->whereIn('reject_inv_id', $search['reject_inv_list']);
        }
        if (isset($search['enable']) && is_numeric($search['enable'])) {
            $query->where('enable', 1);
        }

        return $query;
    }

    public static function getCustomerInvoiceList($search, $user, $offset, $pageSize) {
        $listQuery = self::getSearchModel($search, $user);
        $count = $listQuery->count();

        $list = $listQuery->select('id', 'customer_name', 'handler', 'created_at', 'status', 'applicant_time',
            'is_invoiced', 'invoice_amount', 'invoiced_time', 'company', 'statements_id', 'voided_inv_id', 'date_of_collection',
            'is_voided', 'is_show', 'verify_at', 'confirm_verify_at', 'pre_audit_status', 'pre_audit_content', 'collection_remark');

        if(in_array($user->role_id, [10, 17, 18, 21])) {
            $list->orderByRaw('is_show desc,if(`status`=2,0,1) asc,id desc');
        } else if (15 == $user->role_id){
            $list->where('is_show', 1)
                ->orderByRaw('if(`status`=1,0,1) asc,id desc');
        } else {
            $list->where('is_show', 1)
                ->orderBy('id', 'desc');
        }

        $list = $list->offset($offset)
            ->limit($pageSize)
            ->get();

        return [$list, $count];
    }

    public static function getCustomerInvoiceInfo($invoiceId) {
        return self::query()->where('id', $invoiceId)->where('enable', 1)->first();
    }

    public static function updateData($customerInvoiceId, $collectionData) {
        $res = self::query()->where('id', $customerInvoiceId)->update($collectionData);
        if($res) return 0;
        return 500;
    }

    public static function getNewRejectInvListBySearch($search, $user) {
        $listQuery = self::getSearchModel($search, $user);
        return $listQuery->get();
    }
}