t_nine/app/Service/ProductInventoryService.php

<?php

namespace App\Service;

use App\Model\BasicType;
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\ProductInventory;
use App\Model\PurchaseOrder;
use App\Model\ReturnExchangeOrder;
use App\Model\SalesOrder;
use App\Model\Setting;
use App\Model\Storehouse;
use Illuminate\Support\Facades\DB;

class ProductInventoryService extends Service
{
    //获取产品真实库存
    public static function getRealStock($product_id = [],$data=[]){
        if(empty($product_id)) return [];
        $array = ProductInventory::whereIn('product_id',$product_id)
            ->where('number','>',0)
            ->select('id','product_id','number','crt_time','lock_number')
            ->get()->toArray();

        $map = [];
        if(! empty($data['order_number'])){
            if(strpos($data['order_number'],InvoiceOrder::prefix) !== false){
                $id = InvoiceOrder::where('order_number',$data['order_number'])
                    ->where('del_time',0)
                    ->value('id');
                $model = new InvoiceOrderInfo();
            }else{
                foreach (Construction::$prefix as $value){
                    if(strpos($data['order_number'],$value) !== false){
                        $model = new ConstructionProductInfo();
                    }
                }
                $id = Construction::where('order_number',$data['order_number'])
                    ->where('del_time',0)
                    ->value('id');
            }
            if(! empty($model) && ! empty($id)){
                $save = $model->where('del_time',0)
                    ->where('id',$id)
                    ->select('product_id','number')
                    ->get()->toArray();
                foreach ($save as $value){
                    $key = $value['product_id'];
                    if(isset($map[$key])){
                        $map[$key] += $value['number'];
                    }else{
                        $map[$key] = $value['number'];
                    }
                }
            }
        }

        $return = [];
        if(! empty($array)){
            $setting_map = Setting::where('setting_name','lock_number')
                ->pluck('setting_value','setting_name')
                ->toArray();
            foreach ($array as $value){
                if(! empty($setting_map['lock_number'])){//真实库存
                    $tmp = $map[$value['product_id']] ?? 0;//已保存数量
                    $tmp_lock = ($value['lock_number'] > 0 ? $value['lock_number'] : 0) - $tmp;
                    if($value['number'] > $tmp_lock) {
                        $return[] = [
                            'id' => $value['id'],
                            'product_id' => $value['product_id'],
                            'number' => $value['number'] - ($tmp_lock > 0 ? $tmp_lock : 0),
                        ];
                    }
                }else{
                    $return[] = [
                        'id' => $value['id'],
                        'product_id' => $value['product_id'],
                        'number' => $value['number'],
                    ];
                }
            }
        }

        return $return;
    }

    //更新锁定库存
    //第一个数组 (提交的数据)  第二个数组（保存过的数据）
    public static function changeLockNumber($submit_total = [], $save_total = []){
        if(empty($submit_total) && empty($save_total)) return;

        $setting_map = Setting::where('setting_name','lock_number')
            ->pluck('setting_value','setting_name')
            ->toArray();
        if(empty($setting_map['lock_number'])) return; //是否使用锁定库存

        //产品数扣减
        if(! empty($save_total)){
            foreach ($save_total as $key => $value){
                if(! isset($submit_total[$key])){
                    $submit_total[$key] = - $value;
                }else{
                    $submit_total[$key] = $submit_total[$key] - $value;
                }
            }
        }
        //更新锁定库存
        foreach ($submit_total as $key => $value){
            $tmp = explode(',',$key);

            ProductInventory::where('product_id',$tmp[0])
                ->where('storehouse_id',$tmp[1])
                ->update(['lock_number' => DB::raw('lock_number + ('. $value . ')')]);
        }
    }

    //比较库存
    public static function compareStock($product_id = [],$product_submit = [], $product_save = []){
        if(empty($product_id) || empty($product_submit)) return [false,'比较参数不能为空'];

        //库存
        $array = ProductInventory::whereIn('product_id', $product_id)
            ->where('number','>',0)
            ->select('id','product_id','number','crt_time','lock_number','storehouse_id')
            ->get()->toArray();
        if(empty($array)) return [false,'未找到产品库存数据'];
        $stock = [];
        foreach ($array as $value){
            $key = $value['product_id'] . ',' . $value['storehouse_id'];
            $stock[$key] = $value;
        }

        $pro = Product::whereIn('id',$product_id)
            ->pluck('title','id')
            ->toArray();
        $setting_map = Setting::where('setting_name','lock_number')
            ->pluck('setting_value','setting_name')
            ->toArray();

        foreach ($product_submit as $key => $value){
            $tmp = explode(',',$key);
            $product_id = $tmp[0];
            $pro_tmp = $pro[$product_id] ?? '';
            if(! $pro_tmp) return [false,'异常产品数据'];
            if(! isset($stock[$key])) return [false,'产品:'. $pro_tmp .'库存不存在'];
            $stock_product = $stock[$key];

            if(! empty($setting_map['lock_number'])){//真实库存
                $save_data = $product_save[$key] ?? 0;//已保存数量
                $tmp_lock = ($stock_product['lock_number'] > 0 ? $stock_product['lock_number'] : 0) - $save_data;
                $number = $stock_product['number'] - ($tmp_lock > 0 ? $tmp_lock : 0);
            }else{
                $number = $stock_product['number'];
            }
            if($value > $number) return [false, $pro_tmp. '数量不足,当前数量:' . $number];
        }

        return [true,''];
    }

    //现存量
    public function productInventoryList($data,$user){
        $model = ProductInventory::from('product_inventory as a')
            ->join('product as b','b.id','a.product_id')
            ->select('a.product_id','a.id','a.number','b.title','b.code','b.product_category_id','b.unit','b.bar_code','a.crt_time','a.storehouse_id')
            ->orderby('a.crt_time', 'desc');
        if(! empty($data['product_name'])){
            $product_name = $data['product_name'];
            $id = Product::where('del_time',0)
                ->when(! empty($product_name), function ($query) use ($product_name) {
                    return $query->where('title', 'LIKE', '%'.$product_name.'%');
                })
                ->select('id')
                ->get()->toArray();
            $model->whereIn('a.product_id',array_unique(array_column($id,'id')));
        }
        if(! empty($data['product_category_name'])){
            $product_category_name = $data['product_category_name'];
            $c = ProductCategory::where('del_time',0)
                ->where('title', 'LIKE', '%'.$product_category_name.'%')
                ->select('id')
                ->get()->toArray();
            $model->whereIn('b.product_category_id',array_unique(array_column($c,'id')));
        }
        if(!empty($data['code'])) $model->where('b.code', 'LIKE', '%'.$data['code'].'%');
        if(! empty($data['crt_time'][0]) && ! empty($data['crt_time'][1])) {
            $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
            $model->whereBetween('a.crt_time',[$return[0],$return[1]]);
        }

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

        return [true, $list];
    }

    public function fillListData($data){
        if(empty($data['data'])) return $data;

        $category = ProductCategory::whereIn('id',array_column($data['data'],'product_category_id'))
            ->pluck('title','id')
            ->toArray();
        $basic_map = BasicType::whereIn('id',array_unique(array_column($data['data'],'unit')))
            ->pluck('title','id')
            ->toArray();
        $storehouse = Storehouse::whereIn('id',array_unique(array_column($data['data'],'storehouse_id')))
            ->pluck('title','id')
            ->toArray();
        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['unit_title'] = $basic_map[$value['unit']] ?? '';
            $data['data'][$key]['product_category_title'] = $category[$value['product_category_id']] ?? '';
            $data['data'][$key]['crt_time'] = $value['crt_time'] ? date("Y-m-d H:i:s",$value['crt_time']):'';
            $data['data'][$key]['storehouse_title'] = $storehouse[$value['storehouse_id']] ?? '';
        }

        return $data;
    }

    //库存台账
    public function productInventoryStockList($data,$user){
        $model = InOutRecord::from('in_out_record as a')
            ->where('a.del_time',0)
            ->join('product as b','b.id','a.product_id')
            ->select('a.id','a.product_id','a.number','a.order_type','a.crt_time','b.title','b.code','b.product_category_id','b.unit',DB::raw('SUM(CASE WHEN number < 0 THEN number ELSE 0 END) as out_number'),DB::raw('SUM(CASE WHEN number >= 0 THEN number ELSE 0 END) as in_number'),'a.order_number','a.crt_time','a.storehouse_id')
            ->groupby('a.product_id','a.order_number')
            ->orderBy('a.crt_time','asc');

        if(empty($data['crt_time'][0]) || empty($data['crt_time'][1])) return [false,'时间必须选择'];
        $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
        $model->whereBetween('a.crt_time',[$return[0],$return[1]]);
        if(! empty($data['product_name'])){
            $product_name = $data['product_name'];
            $id = Product::where('del_time',0)
                ->when(! empty($product_name), function ($query) use ($product_name) {
                    return $query->where('title', 'LIKE', '%'.$product_name.'%');
                })
                ->select('id')
                ->get()->toArray();
            $model->whereIn('a.product_id',array_unique(array_column($id,'id')));
        }
        if(! empty($data['product_category_name'])){
            $product_category_name = $data['product_category_name'];
            $c = ProductCategory::where('del_time',0)
                ->where('title', 'LIKE', '%'.$product_category_name.'%')
                ->select('id')
                ->get()->toArray();
            $model->whereIn('b.product_category_id',array_unique(array_column($c,'id')));
        }
        if(! empty($data['code'])) $model->where('b.code', 'LIKE', '%'.$data['code'].'%');
        if(! empty($data['order_number'])) $model->where('a.order_number', 'LIKE', '%'.$data['order_number'].'%');

        $list = $model->get()->toArray();
        $list = $this->fillData($list, $return);

        return [true, $list];
    }

    public function fillData($data,$time_arr){
        if (empty($data)) return $data;

        $start_data = InOutRecord::whereIn('product_id',array_column($data,'product_id'))
            ->where('crt_time','<',$time_arr[0])
            ->where('del_time',0)
            ->select('product_id','number',DB::raw('sum(number) as number'))
            ->groupby('product_id')
            ->get()->toArray();
        $start_map = [];
        foreach ($start_data as $value){
            $start_map[$value['product_id']] = $value['number'];
        }

        $category = ProductCategory::whereIn('id',array_unique(array_column($data,'product_category_id')))
            ->pluck('title','id')
            ->toArray();

        $basic_map = BasicType::whereIn('id',array_unique(array_column($data,'unit')))
            ->pluck('title','id')
            ->toArray();

        $roll_tmp = [];
        $order_type = $this->getOrderType();
        $storehouse = Storehouse::whereIn('id',array_unique(array_column($data['data'],'storehouse_id')))
            ->pluck('title','id')
            ->toArray();
        foreach ($data as $key => $value){
            $keys = $value['product_id'];
            if(! isset($roll_tmp[$keys])){
                if(isset($start_map[$keys])){
                    $data[$key]['start_number'] = $start_map[$keys];
                }else{
                    $data[$key]['start_number'] = 0;
                }
                $tmp = $data[$key]['start_number'] + $value['in_number'] + $value['out_number'];
                $data[$key]['end_number'] = round($tmp,2);
                $roll_tmp[$keys] = $data[$key]['end_number'];
            }else{
                $data[$key]['start_number'] = $roll_tmp[$keys];
                $tmp = $data[$key]['start_number'] + $value['in_number'] + $value['out_number'];
                $data[$key]['end_number'] = round($tmp,2);
                $roll_tmp[$keys] = $data[$key]['end_number'];
            }

            $data[$key]['out_number'] = abs($value['out_number']);
            $data[$key]['product_category_title'] = $category[$value['product_category_id']] ?? '';
            $data[$key]['order_name'] = $order_type[$value['order_type']] ?? '';
            $data[$key]['crt_time'] = $value['crt_time'] ? date("Y-m-d",$value['crt_time']):'';
            $data[$key]['unit_title'] = $basic_map[$value['unit']] ?? '';
            $data['data'][$key]['storehouse_title'] = $storehouse[$value['storehouse_id']] ?? '';
        }

        return $data;
    }

    public function getOrderType(){
        $array = [];
        foreach (Construction::$prefix as $value){
            $array[$value] = '施工单';
        }
        foreach (ReturnExchangeOrder::$prefix as $key => $value){
            $array[$value] = ReturnExchangeOrder::$model_type_name[$key] ?? '';
        }
        foreach (SalesOrder::$prefix as $value){
            $array[$value] = '合同';
        }
        $array[InvoiceOrder::prefix] = '发货单';
        $array[PurchaseOrder::prefix] = '采购单';

        return $array;
    }
}