<?php namespace App\Admin\Repositories; use App\Models\Covenant as ModelsCovenant; use App\Models\LawyerCost as ModelsLawyerCost; use App\Models\Lawyer as ModelsLawyer; use App\Models\CovenantReceivePayment as ModelCovenantReceivePayment; use App\Models\Principal; use Dcat\Admin\Repositories\EloquentRepository; use Dcat\Admin\Grid; use Illuminate\Support\Facades\DB; class LawyerCollect extends EloquentRepository { /** * Model. * * @var string */ protected $eloquentClass = ModelsCovenant::class; protected $lawyers; public function __construct($options = []) { parent::__construct($options); $this->lawyers = $options['lawyers'] ?? []; } public function get(Grid\Model $model) { // 获取筛选条件 $year = $model->filter()->input('year') ?? date("Y"); $received_at = $invoiced_at = []; $received_at['start'] = isset($_GET['received_at']['start']) && $_GET['received_at']['start'] ? $_GET['received_at']['start'] . "-01" : ''; $received_at['end'] = isset($_GET['received_at']['end']) && $_GET['received_at']['end'] ? $_GET['received_at']['end'] . "-31" : ''; $invoiced_at['start'] = isset($_GET['invoiced_at']['start']) && $_GET['invoiced_at']['start'] ? $_GET['invoiced_at']['start'] . '-01' : ''; $invoiced_at['end'] = isset($_GET['invoiced_at']['end']) && $_GET['invoiced_at']['end'] ? $_GET['invoiced_at']['end'] . '-31' : ''; $data = []; foreach ($this->lawyers as $lawyer) { $params = [ 'number' => $lawyer->number, 'name' => $lawyer->name, 'year' => $year ]; $totalInfo = ModelsLawyer::getTotalInfo($params, $received_at, $invoiced_at); $tmp = []; $tmp['lname'] = $lawyer->name; $tmp['commission_ratio'] = $lawyer->commission_rate; $tmp['receipt_money'] = number_format($totalInfo['receipt_money'], 2); $tmp['commission_amount'] = number_format($totalInfo['commission_amount'], 2); $tmp['receipt_noinvoice'] = number_format(($totalInfo['receipt_money'] - $totalInfo['invoiced_money']) > 0 ? $totalInfo['receipt_money'] - $totalInfo['invoiced_money'] : 0, 2); $tmp['invoice_noreceipt'] = number_format(($totalInfo['invoiced_money'] - $totalInfo['receipt_money']) > 0 ? $totalInfo['invoiced_money'] - $totalInfo['receipt_money'] : 0, 2); $tmp['cost'] = number_format($totalInfo['cost'], 2); $tmp['basic_salary'] = number_format($totalInfo['salary'], 2); $tmp['special_additional'] = number_format($totalInfo['special_additional'], 2); $tmp['social'] = number_format($totalInfo['social'], 2); $tmp['accumulation_fund'] = number_format($totalInfo['accumulation_fund'], 2); $tmp['posting_tickets_fee'] = number_format($totalInfo['tickets_money'], 2); $tmp['advance_fee'] = number_format($totalInfo['advance_fee'], 2); $tmp['payable_commission'] = number_format($totalInfo['payable_commission'], 2); $data[] = $tmp; } return $model->makePaginator( count($data), // 传入总记录数 $data // 传入数据二维数组 ); } // 获取列表数据 public function getList(array $param) { $prePage = $param['per_page'] ?? 20; $offsetstart = $param['offsetstart'] ?? 0; $number = $param['search']['number'] ?? ''; $lname = $param['search']['lname'] ?? ''; $year = $param['search']['year'] ?? date("Y"); $invoiced_at = $param['search']['invoiced_at'] ?? []; $received_at = $param['search']['received_at'] ?? []; $list = []; DB::enableQueryLog(); $note_monitor = DB::table('lawyer as l') ->select(DB::raw('rp.lawyer_id,rp.year,l.number as lnumber,l.name as lname,l.commission_rate,sum(invoiced_money) as invoiced_money,sum(received_amount) as received_amount')) //->select(['rp.*', 'l.name as lname', 'l.number as lnumber', 'l.commission_rate']) ->leftJoin('covenant_receive_payment AS rp', 'rp.lawyer_id', '=', 'l.id') ->whereNull('rp.deleted_at'); if ($number) { $note_monitor = $note_monitor->where("l.number", $number); } if ($lname) { $note_monitor = $note_monitor->where("l.name", $lname); } if ($year) { $note_monitor = $note_monitor->where("rp.year", $year); } if ($invoiced_at['start'] && $received_at['start']) { $in_start = $invoiced_at['start'] ?? ''; $in_end = $invoiced_at['end'] ?? ''; $re_start = $received_at['start'] ?? ''; $re_end = $received_at['end'] ?? ''; 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 ($invoiced_at['start'] && !$received_at['start']) { //开票日期 $start = $invoiced_at['start'] ?? ''; $end = $invoiced_at['end'] ?? ''; if ($start) { if ($end) { $note_monitor = $note_monitor->whereBetween('rp.received_at', [$start, $end]); } else { $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $start); } } // } elseif ($received_at['start'] && !$invoiced_at['start']) { //收款日期 $start = $received_at['start'] ?? ''; $end = $received_at['end'] ?? ''; if ($start) { if ($end) { $note_monitor = $note_monitor->whereBetween('rp.received_at', [$start, $end]); } else { $note_monitor = $note_monitor->whereDate('rp.received_at', '>', $start); } } } // 计算列表总数 $count = 0; $cObj = $note_monitor->groupBy('rp.lawyer_id')->get(); if ($cObj->toArray()) { $count = count($cObj->toArray()); } $queries = DB::getQueryLog(); // 获取列表 $data = []; $list = $note_monitor->limit($prePage)->offset($offsetstart)->groupBy('rp.lawyer_id')->get()->toArray(); foreach ($list as $key => $item) { $tmp = []; //$tmp['id'] = $item->id; //已收款 $receipt_money = $item->received_amount; //已开票 $invoiced_money = $item->invoiced_money; //已收款未开票 $receipt_noinvoice = ($receipt_money - $invoiced_money) > 0 ? $receipt_money - $invoiced_money : 0; //已开票未收款 $invoice_noreceipt = ($invoiced_money - $receipt_money) > 0 ? $invoiced_money - $receipt_money : 0; //贴票金额 $lawyerObj = ModelsLawyer::find($item->lawyer_id); $tickets_money = ModelsLawyerCost::getPostingTicketsMoney($item->lawyer_id, $year, $lawyerObj->commission_rate, $lawyerObj->ticket_ratio); $tmp['lnumber'] = $item->lnumber; $tmp['lname'] = $item->lname; $tmp['commission_ratio'] = $item->commission_rate; $tmp['receipt_money'] = number_format($receipt_money, 2); $tmp['commission_amount'] = self::getCommissionAmount($receipt_money, $item->commission_rate); $tmp['receipt_noinvoice'] = number_format($receipt_noinvoice, 2); $tmp['invoice_noreceipt'] = number_format($invoice_noreceipt, 2); $cost = ModelsLawyerCost::getTotalCost($item->lawyer_id, $year); $tmp['cost'] = number_format($cost, 2); $basic_salary = ModelsLawyerCost::getBasiSalary($item->lawyer_id, $year); $tmp['basic_salary'] = number_format($basic_salary, 2); $special_additional = ModelsLawyerCost::getSpecialAdditional($item->lawyer_id, $year); $tmp['special_additional'] = number_format($special_additional, 2); $social = ModelsLawyerCost::getSocial($item->lawyer_id, $year); $tmp['social'] = number_format($social, 2); $accumulation_fund = ModelsLawyerCost::getAccumulationFund($item->lawyer_id, $year); $tmp['accumulation_fund'] = number_format($accumulation_fund, 2); $tmp['posting_tickets_fee'] = number_format($tickets_money, 2); $advance_fee = ModelsLawyerCost::getAdvanceFee($item->lawyer_id, $year); $tmp['advance_fee'] = number_format($advance_fee, 2); $tmp['payable_commission'] = self::getPayableAmount($item->lawyer_id, $year, $item->commission_rate, $receipt_money); array_push($data, $tmp); } return [ 'total' => $count, 'subjects' => $data ]; } public static function getPayableAmount($lawyer_id, $year, $commission_rate, $receipt_money) { $paid_amount = ModelsLawyerCost::getPaidAmount($lawyer_id, $year); //已支付款项 $result = $receipt_money * ($commission_rate / 100) - $paid_amount; return number_format($result, 2); } //提成金额 public static function getCommissionAmount($receipt_money, $commission_rate) { $result = $receipt_money * ($commission_rate / 100); return number_format($result, 2); } }