t_nine/app/Service/StatisticsService.php

<?php

namespace App\Service;

use App\Model\Construction;
use App\Model\ConstructionProductInfo;
use App\Model\InOutRecord;
use App\Model\InvoiceOrder;
use App\Model\InvoiceOrderInfo;
use App\Model\Product;
use App\Model\ProductCategory;
use App\Model\PurchaseOrder;
use App\Model\PurchaseOrderInfo;
use App\Model\ReturnExchangeOrder;
use App\Model\ReturnExchangeOrderProductInfo;
use App\Model\SalesOrder;
use App\Model\SalesOrderProductInfo;
use Illuminate\Support\Facades\DB;

class StatisticsService extends Service
{
    public function statisticsXs($data,$user){

    }

    public function inoutrecord(){
        $in_data = InvoiceOrder::where('del_time',0)
            ->select('id','sales_order_id')
            ->get()->toArray();

        $map = [];
        $s_p = SalesOrderProductInfo::whereIn('sales_order_id',array_column($in_data,'sales_order_id'))
            ->where('del_time',0)
            ->select('sales_order_id','product_id','final_amount','price')
            ->get()->toArray();
        foreach ($s_p as $value){
            $map[$value['sales_order_id']][] = [
                'product_id' => $value['product_id'],
                'final_amount' => $value['final_amount'],
                'price' => $value['price'],
            ];
        }

        DB::beginTransaction();
        try {
            foreach ($in_data as $value){
                $tmp = $map[$value['sales_order_id']] ?? [];
                if(empty($tmp)) continue;
                foreach ($tmp as $val){
                    InvoiceOrderInfo::where('del_time',0)
                        ->where('invoice_id', $value['id'])
                        ->where('product_id', $val['product_id'])
                        ->update([
                            'final_amount' => $val['final_amount'],
                            'price' => $val['price'],
                        ]);
                }

            }
        }catch (\Throwable $exception){
            DB::rollBack();
            dd($exception->getMessage());
        }

        DB::commit();
        dd(1);

    }

    public function inoutrecord2(){
        DB::beginTransaction();
        try {
            DB::table('in_out_record')
                ->where('price',0)
                ->whereIn('order_type', array_values(ReturnExchangeOrder::$prefix))
                ->select('id','order_number','product_id')
                ->orderBy('id','asc')
                ->chunk(200,function ($data) {;
                    $data = Collect($data)->map(function ($object) {
                        return (array)$object;
                    })->toArray();

                    $map = ReturnExchangeOrder::where('del_time',0)
                        ->whereIn('order_number',array_column($data,'order_number'))
                        ->pluck('id','order_number')
                        ->toArray();
                    $result = ReturnExchangeOrderProductInfo::where('del_time',0)
                        ->whereIn('return_exchange_id',array_values($map))
                        ->select('return_exchange_id','return_exchange_price','product_id')
                        ->get()->toArray();
                    $result_map = [];
                    foreach ($result as $v){
                        $result_map[$v['return_exchange_id']][$v['product_id']] = [
                            'product_id' => $v['product_id'],
                            'price' => $v['return_exchange_price'],
                        ];
                    }

                    foreach ($data as $value){
                        $tmp_id = $map[$value['order_number']] ?? 0;
                        if($tmp_id < 0) continue;
                        $tmp = $result_map[$tmp_id][$value['product_id']] ?? [];
                        if(empty($tmp)) continue;

                        InOutRecord::where('id',$value['id'])->update([
                            'price' => $tmp['price']
                        ]);
                    }
            });

            DB::commit();
        }catch (\Throwable $exception){
            DB::rollBack();
            dd($exception->getMessage());
        }
        dd(1);
    }

    public function statisticsJc($data,$user){
        if(empty($data['top_depart_id'])) return [false, '请选择门店'];
        $model = Product::ProductClear($user,$data);
        $model = $model->where('del_time',0)
            ->select('title','id','code','depart_id','top_depart_id','product_attribute')
            ->orderby('product_attribute', 'desc')
            ->orderby('id', 'desc');

        if(! empty($data['title'])) $model->where('title', 'LIKE', '%'.$data['title'].'%');
        if(isset($data['state'])) $model->where('state', $data['state']);
        if(isset($data['is_use'])) $model->where('is_use', $data['is_use']);
        if(isset($data['product_attribute'])) $model->where('product_attribute', $data['product_attribute']);
        if(! empty($data['product_category_id'])) $model->where('product_category_id', $data['product_category_id']);
        if(! empty($data['product_category'])) {
            $product_category = ProductCategory::where('del_time',0)
                ->where('title', 'LIKE', '%'.$data['product_category'].'%')
                ->select('id')
                ->get()->toArray();
            $model->whereIn('product_category_id',array_unique(array_column($product_category,'id')));
        }
        if(! empty($data['code'])) $model->where('code', 'LIKE', '%'.$data['code'].'%');

        $list = $this->limit($model,'',$data);
        $list = $this->fillData($list,$user,$data);

        return [true, $list];
    }

    public function fillData($data, $user, $search){
        if(empty($data['data'])) return $data;

        //产品
        $product = array_column($data,'id');
        //本月入库 本月出库
        list($in, $out) = $this->getThisMonthData($product,$user,$search);
        //上月结存
        $lastJc = $this->getLastMonthBalance($product,$user,$search);

        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['in_number'] = $in[$value['id']] ?? 0;
            $data['data'][$key]['out_number'] = $out[$value['id']] ?? 0;
            $data['data'][$key]['last_jc_number'] = $lastJc[$value['id']] ?? 0;
            $data['data'][$key]['last_jc_number'] = $lastJc[$value['id']] ?? 0;
        }

        return $data;
    }

    //本月入库 出库
    public function getThisMonthData($product = [], $user = [], $search = []){
        $in = $out = [];
        $startStamp = strtotime(date("Y-m-01 00:00:00"));
        $endStamp = strtotime(date("Y-m-t 23:59:59"));

        //本月出和入的数据
        $model = InOutRecord::TopClear($user,$search);
        $list = $model->where('del_time',0)
            ->where('crt_time','>=',$startStamp)
            ->where('crt_time','<=',$endStamp)
            ->whereIn('product_id',$product)
            ->select('product_id','number')
            ->get()->toArray();

        foreach ($list as $value){
            if($value['number'] >= 0){
                if(isset($in[$value['product_id']])){
                    $in[$value['product_id']] += $value['number'];
                }else{
                    $in[$value['product_id']] = $value['number'];
                }
            }else{
                if(isset($out[$value['product_id']])){
                    $out[$value['product_id']] += abs($value['number']);
                }else{
                    $out[$value['product_id']] = abs($value['number']);
                }
            }
        }

        return [$in, $out];
    }

    //上月结存
    public function getLastMonthBalance($product = [], $user = [], $search = []){
        $return = [];
        $startStamp = strtotime(date("Y-m-01 00:00:00"));
        $model = InOutRecord::TopClear($user,$search);
        $list = $model->where('del_time',0)
            ->where('crt_time','<',$startStamp)
            ->whereIn('product_id',$product)
            ->select('product_id','number')
            ->get()->toArray();

        foreach ($list as $value){
            if(isset($return[$value['product_id']])){
                $return[$value['product_id']] += $value['number'];
            }else{
                $return[$value['product_id']] = $value['number'];
            }
        }

        return $return;
    }

    //本月入库
    public function getThisMonthIn1($product = [], $user = [], $search = []){
        $return = [];
        $startStamp = strtotime(date("Y-m-01 00:00:00"));
        $endStamp = strtotime(date("Y-m-t 23:59:59"));
        //本月采购单
        $model = PurchaseOrder::Clear($user,$search);
        $list = $model->where('del_time',0)
            ->where('state', PurchaseOrder::STATE_Four)
            ->where('crt_time','>=',$startStamp)
            ->where('crt_time','<=',$endStamp)
            ->select('id')
            ->get()->toArray();
        if(empty($list)) return $return;
        //本月采购产品
        $purchase_product_array = [];
        $purchase_product = PurchaseOrderInfo::where('del_time',0)
            ->whereIn('product_id',$product)
            ->whereIn('purchase_order_id',array_column($list,'id'))
            ->select('product_id','number','price')
            ->get()->toArray();
        foreach ($purchase_product as $value){
            $total = bcmul($value['number'],$value['price'],2);
            if(isset($purchase_product_array[$value['product_id']])){
                $purchase_product_array[$value['product_id']]['number'] += $value['number'];
                $total_tmp = bcadd($purchase_product_array[$value['product_id']]['total'], $total, 2);
                $purchase_product_array[$value['product_id']]['total'] = $total_tmp;
            }else{
                $purchase_product_array[$value['product_id']] = [
                    'number' => $value['number'],
                    'total' => $total,
                ];
            }
        }

        //本月退货(采购)
        $model2 = ReturnExchangeOrder::Clear($user, $search);
        $return_list = $model2->where('del_time',0)
            ->where('state', ReturnExchangeOrder::State_two)
            ->where('type',ReturnExchangeOrder::Order_type2)
            ->where('crt_time','>=',$startStamp)
            ->where('crt_time','<=',$endStamp)
            ->select('id')
            ->get()->toArray();
        //本月退货产品
        $return_product_array = [];
        if(! empty($return_list)){
            $return_product = ReturnExchangeOrderProductInfo::where('del_time',0)
                ->where('return_exchange_id', array_column($return_list, 'id'))
                ->whereIn('product_id',$product)
                ->where('return_or_exchange',ReturnExchangeOrderProductInfo::type_one)
                ->select('product_id','number','return_or_exchange')
                ->get()->toArray();
            foreach ($return_product as $value){
                $total = bcmul($value['number'],$value['return_or_exchange'],2);
                if(isset($return_product_array[$value['product_id']])){
                    $return_product_array[$value['product_id']]['number'] += $value['number'];
                    $total_tmp = bcadd($return_product_array[$value['product_id']]['total'], $total, 2);
                    $return_product_array[$value['product_id']]['total'] = $total_tmp;
                }else{
                    $return_product_array[$value['product_id']] = [
                        'number' => $value['number'],
                        'total' => $total,
                    ];
                }
            }
        }

        foreach ($return_product_array as $p => $n){
            $number_tmp = -$n['number'];
            $total_tmp = -$n['total'];
            $purchase_product_tmp = $purchase_product_array[$p] ?? [];
            if(empty($purchase_product_tmp)){
                $purchase_product_array[$p] = [
                    'number' => $number_tmp,
                    'total' => $total_tmp,
                ];
            }else{
                $purchase_product_array[$p]['number'] += $number_tmp;
                $total_tmp2 = bcadd($purchase_product_array[$p]['total'], $total_tmp, 2);
                $purchase_product_array[$p]['total'] += $total_tmp2;
            }
        }

        return $purchase_product_array;
    }

    public function getThisMonthOut($product = [], $user = [], $search = []){

    }
}