<?php

namespace App\Models;

use Dcat\Admin\Traits\HasDateTimeFormatter;

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

class CovenantReceivePayment extends Model
{
    use HasDateTimeFormatter;
    protected $table = 'covenant_receive_payment';

    //账户类型
    public const RECEIVEDTYPE = [
        1 => '基本户',
        2 => '其它',
    ];


    //已收款未开票
    public static function getReceiptNoinvoice($lawyer_id = 0)
    {
        $where = [];
        if ($lawyer_id) {
            $where['lawyer_id'] = $lawyer_id;
        }
        //开票金额
        $invoiced_money = self::where(['rtype' => 2])->where($where)
            ->sum('invoiced_money');
        //已收款
        $receipt_money = self::where(['rtype' => 1])->where($where)
            ->sum('received_amount');
        //已收款未开票
        $receipt_noinvoice = ($receipt_money - $invoiced_money) > 0 ? $receipt_money - $invoiced_money : 0;
        return $receipt_noinvoice;
    }

    //已开票未收款
    public static function getInvoiceNoreceipt($lawyer_id = 0)
    {
        $where = [];
        if ($lawyer_id) {
            $where['lawyer_id'] = $lawyer_id;
        }
        //开票金额
        $invoiced_money = self::where(['lawyer_id' =>  $lawyer_id, 'rtype' => 2])->where($where)
            ->sum('invoiced_money');
        //已收款
        $receipt_money = self::where(['lawyer_id' => $lawyer_id, 'rtype' => 1])->where($where)
            ->sum('received_amount');

        $invoice_noreceipt = ($invoiced_money - $receipt_money) > 0 ? $invoiced_money - $receipt_money  : 0;
        return $invoice_noreceipt;
    }

    //创收已收款
    public static function getReceivedMoney($lawyer_id = 0, $year = 0)
    {
        $where = ['rtype' => 1];
        if ($lawyer_id) {
            $where['lawyer_id'] = $lawyer_id;
        }
        if ($year) {
            $where['year'] = $year;
        }
        $money = self::where($where)
            ->sum('received_amount');
        return $money;
    }
    /**
     * 创收已收款
     * number 律师编号
     * received_at 收款日期
     * invoiced_at 开票日期
     */
    public static function getReceivedMoneyByNumber($number, $received_at = [], $invoiced_at = [])
    {
        $lawyer_id = $received_amount = 0;

        $in_start = $invoiced_at['start'] ? $invoiced_at['start'] . '-01' : '';
        $in_end = $invoiced_at['end'] ? $invoiced_at['end'] . '-31' : '';
        $re_start = $received_at['start'] ? $received_at['start'] . '-01' : '';
        $re_end = $received_at['end'] ? $received_at['end'] . '-31' : '';

        if ($number) {
            $lawyObj = Lawyer::where('number', $number)->first();
            if ($lawyObj) {
                $lawyer_id = $lawyObj->number;
            }
        }

        $note_monitor = DB::table("covenant_receive_payment as rp")
            ->select(DB::raw('rp.lawyer_id, sum(received_amount) as received_amount'));
        if ($lawyer_id) {
            $note_monitor = $note_monitor->where("lawyer_id", $lawyer_id);
        }

        if ($in_start && $re_start) {
            if ($in_end && $re_end) {
                $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end])
                    ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
            } else {
                if ($in_end) {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start)
                        ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
                } elseif ($re_end) {
                    $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                        ->whereBetween('rp.received_at', [$re_start, $re_end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                        ->whereDate('rp.received_at', '>', $re_start);
                }
            }
        } elseif ($in_start && !$re_start) {
            if ($in_start) {
                if ($in_end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$in_start, $in_end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $in_start);
                }
            }
        } elseif (!$in_start && $re_start) {
            if ($re_start) {
                if ($re_end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start);
                }
            }
        }
        $result = $note_monitor->where('rtype', 1)->groupBy('rp.lawyer_id')->get()->toArray();

        foreach ($result as $kk => $vv) {
            $received_amount += $vv->received_amount;
        }

        return $received_amount;
    }



    //开票金额
    public static function getInvoicedMoney($lawyer_id = 0, $year = 0)
    {
        $where = ['rtype' => 2];
        if ($lawyer_id) {
            $where['lawyer_id'] = $lawyer_id;
        }
        if ($year) {
            $where['year'] = $year;
        }
        $money = self::where($where)
            ->sum('invoiced_money');
        return $money;
    }

    /**
     * 创收已开票
     * number 律师编号
     * received_at 收款日期
     * invoiced_at 开票日期
     */
    public static function getInvoicedMoneyByNumber($number, $received_at = [], $invoiced_at = [])
    {
        $lawyer_id = $invoiced_money = 0;

        $in_start = $invoiced_at['start'] ? $invoiced_at['start'] . '-01' : '';
        $in_end = $invoiced_at['end'] ? $invoiced_at['end'] . '-31' : '';
        $re_start = $received_at['start'] ? $received_at['start'] . '-01' : '';
        $re_end = $received_at['end'] ? $received_at['end'] . '-31' : '';

        if ($number) {
            $lawyObj = Lawyer::where('number', $number)->first();
            if ($lawyObj) {
                $lawyer_id = $lawyObj->number;
            }
        }

        $note_monitor = DB::table("covenant_receive_payment as rp")
            ->select(DB::raw('rp.lawyer_id, sum(invoiced_money) as invoiced_money'));
        if ($lawyer_id) {
            $note_monitor = $note_monitor->where("lawyer_id", $lawyer_id);
        }

        if ($in_start && $re_start) {
            if ($in_end && $re_end) {
                $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end])
                    ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
            } else {
                if ($in_end) {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start)
                        ->whereBetween('rp.invoiced_at', [$in_start, $in_end]);
                } elseif ($re_end) {
                    $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                        ->whereBetween('rp.received_at', [$re_start, $re_end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.invoiced_at', '>', $in_start)
                        ->whereDate('rp.received_at', '>', $re_start);
                }
            }
        } elseif ($in_start && !$re_start) {
            if ($in_start) {
                if ($in_end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$in_start, $in_end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $in_start);
                }
            }
        } elseif (!$in_start && $re_start) {
            if ($re_start) {
                if ($re_end) {
                    $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end]);
                } else {
                    $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start);
                }
            }
        }
        $result = $note_monitor->where('rtype', 2)->groupBy('rp.lawyer_id')->get()->toArray();
        foreach ($result as $kk => $vv) {
            $invoiced_money += $vv->invoiced_money;
        }

        return $invoiced_money;
    }


    /**
     * 提成金额
     * number 律师编号
     * received_at 收款日期
     */
    public static function getCommissionAmount($number, $received_at = [])
    {
        $lawyer_id = $total_money = 0;

        $re_start = $received_at['start'] ? $received_at['start'] . '-01' : '';
        $re_end = $received_at['end'] ? $received_at['end'] . '-31' : '';

        if ($number) {
            $lawyObj = Lawyer::where('number', $number)->first();
            if ($lawyObj) {
                $lawyer_id = $lawyObj->number;
            }
        }

        $note_monitor = DB::table("covenant_receive_payment as rp")
            ->select(DB::raw('rp.lawyer_id,sum(received_amount) as received_amount'));
        if ($lawyer_id) {
            $note_monitor = $note_monitor->where("lawyer_id", $lawyer_id);
        }


        if ($re_start) {
            if ($re_end) {
                $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end]);
            } else {
                $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start);
            }
        }

        $result = $note_monitor->where('rtype', 1)->groupBy('rp.lawyer_id')->get()->toArray();

        foreach ($result as $kk => $vv) {
            $lawyerObj = Lawyer::find($vv->lawyer_id);
            $commission_rate = $lawyerObj->commission_rate;

            $total_money += $vv->received_amount * ($commission_rate / 100);
        }

        return $total_money;
    }

    /**
     * 基本工资 
     * number 律师编号
     * received_at 收款日期
     */
    public static function getTotalBasicSalary($number, $received_at = [])
    {
        $lawyer_id = $total_money = 0;

        $re_start = $received_at['start'] ? $received_at['start'] . '-01' : '';
        $re_end = $received_at['end'] ? $received_at['end'] . '-31' : '';

        if ($number) {
            $lawyObj = Lawyer::where('number', $number)->first();
            if ($lawyObj) {
                $lawyer_id = $lawyObj->number;
            }
        }

        $note_monitor = DB::table("covenant_receive_payment as rp")
            ->select(DB::raw('rp.lawyer_id,sum(received_amount) as received_amount'));
        if ($lawyer_id) {
            $note_monitor = $note_monitor->where("lawyer_id", $lawyer_id);
        }


        if ($re_start) {
            if ($re_end) {
                $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end]);
            } else {
                $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start);
            }
        }

        $result = $note_monitor->where('rtype', 1)->groupBy('rp.lawyer_id')->get()->toArray();

        foreach ($result as $kk => $vv) {
            $lawyerCostObj = LawyerCost::where('lawyer_id', $vv->lawyer_id)->first();

            $total_money += $lawyerCostObj->basic_salary;
        }

        return $total_money;
    }

    /**
     * 专项附加 
     * number 律师编号
     * received_at 收款日期
     */
    public static function getTotalSpecialAdditional($number, $received_at = [])
    {
        $lawyer_id = $total_money = 0;

        $re_start = $received_at['start'] ? $received_at['start'] . '-01' : '';
        $re_end = $received_at['end'] ? $received_at['end'] . '-31' : '';

        if ($number) {
            $lawyObj = Lawyer::where('number', $number)->first();
            if ($lawyObj) {
                $lawyer_id = $lawyObj->number;
            }
        }

        $note_monitor = DB::table("covenant_receive_payment as rp")
            ->select(DB::raw('rp.lawyer_id,sum(received_amount) as received_amount'));
        if ($lawyer_id) {
            $note_monitor = $note_monitor->where("lawyer_id", $lawyer_id);
        }


        if ($re_start) {
            if ($re_end) {
                $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end]);
            } else {
                $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start);
            }
        }

        $result = $note_monitor->where('rtype', 1)->groupBy('rp.lawyer_id')->get()->toArray();

        foreach ($result as $kk => $vv) {
            $lawyerCostObj = LawyerCost::where('lawyer_id', $vv->lawyer_id)->first();

            $total_money += $lawyerCostObj->special_additional;
        }

        return $total_money;
    }

    /**
     * 社保
     * number 律师编号
     * received_at 收款日期
     */
    public static function getTotalSocial($number, $received_at = [])
    {
        $lawyer_id = $total_money = 0;

        $re_start = $received_at['start'] ? $received_at['start'] . '-01' : '';
        $re_end = $received_at['end'] ? $received_at['end'] . '-31' : '';

        if ($number) {
            $lawyObj = Lawyer::where('number', $number)->first();
            if ($lawyObj) {
                $lawyer_id = $lawyObj->number;
            }
        }

        $note_monitor = DB::table("covenant_receive_payment as rp")
            ->select(DB::raw('rp.lawyer_id,sum(received_amount) as received_amount'));
        if ($lawyer_id) {
            $note_monitor = $note_monitor->where("lawyer_id", $lawyer_id);
        }


        if ($re_start) {
            if ($re_end) {
                $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end]);
            } else {
                $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start);
            }
        }

        $result = $note_monitor->where('rtype', 1)->groupBy('rp.lawyer_id')->get()->toArray();

        foreach ($result as $kk => $vv) {
            $lawyerCostObj = LawyerCost::where('lawyer_id', $vv->lawyer_id)->first();

            $total_money += $lawyerCostObj->social_person_fee;
            $total_money += $lawyerCostObj->social_company_fee;
        }

        return $total_money;
    }

    /**
     * 公积金
     * number 律师编号
     * received_at 收款日期
     */
    public static function getTotalAccumulationFund($number, $received_at = [])
    {
        $lawyer_id = $total_money = 0;

        $re_start = $received_at['start'] ? $received_at['start'] . '-01' : '';
        $re_end = $received_at['end'] ? $received_at['end'] . '-31' : '';

        if ($number) {
            $lawyObj = Lawyer::where('number', $number)->first();
            if ($lawyObj) {
                $lawyer_id = $lawyObj->number;
            }
        }

        $note_monitor = DB::table("covenant_receive_payment as rp")
            ->select(DB::raw('rp.lawyer_id,sum(received_amount) as received_amount'));
        if ($lawyer_id) {
            $note_monitor = $note_monitor->where("lawyer_id", $lawyer_id);
        }


        if ($re_start) {
            if ($re_end) {
                $note_monitor = $note_monitor->whereBetween('rp.received_at', [$re_start, $re_end]);
            } else {
                $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $re_start);
            }
        }

        $result = $note_monitor->where('rtype', 1)->groupBy('rp.lawyer_id')->get()->toArray();

        foreach ($result as $kk => $vv) {
            $lawyerCostObj = LawyerCost::where('lawyer_id', $vv->lawyer_id)->first();

            $total_money += $lawyerCostObj->accumulation_fund_person_fee;
            $total_money += $lawyerCostObj->accumulation_fund_company_fee;
        }

        return $total_money;
    }
}