<?php


namespace App\Model\stock_inventory;


use App\Fun\Fun;
use App\Model\pos\SaleDetailsModel;
use App\Model\product\ColorModel;
use App\Model\product\ProductModel;
use App\Model\product\ProductUnitModel;
use App\Model\product\SizeModel;
use App\Model\production\cutting\CuttingDetailModel;
use App\Model\production\packing\ProductionPackingDoneDetailModel;
use App\Model\production\ProductionDoneDetailModel;
use App\Model\production\ProductionRequestDetailModel;
use App\Model\setup\WarehouseModel;
use Illuminate\Database\Eloquent\Model;
use Auth;


class StockModel extends Model

{
    protected $table = "nso007_stocks";

    protected $fillable = ['product_id', 'warehouse_id', 'type', 'stock_qty', 'size_code', 'color_code', 'barcode'];

    public function select_images_first()
    {

        return $this->belongsTo('App\Model\ProductImageModel', 'id', 'product_id');

    }

    public function __construct()
    {
        Fun::lang();
    }

    public function warehouse_name()

    {

        $data = WarehouseModel::find($this->warehouse_id);

        return $data ? $data->warehouse_name : 'NAN';

    }

    public function production_qty()
    {
        $product = ProductionPackingDoneDetailModel::selectRaw('SUM(nso007_production_packing_done_detail.qty) as production_qty')
            ->join('nso007_production_packing_done', 'nso007_production_packing_done.id', 'nso007_production_packing_done_detail.packing_done_id')
            ->where([
                'nso007_production_packing_done.warehouse_id' => $this->warehouse_id,
                'nso007_production_packing_done_detail.barcode' => $this->barcode,
            ])->first();
        $qty = $product->production_qty??0;
        return $qty?:'';
    }

    public function production_out()
    {
        $qty = 0;
        $material = ProductionRequestDetailModel::select(
            'nso007_production_request_detail.qty',
            'nso007_production_request_detail.unit_id',
            'nso007_production_request_detail.material_id'
        )
            ->join('nso007_production_request', 'nso007_production_request.id', 'nso007_production_request_detail.production_id')
            ->where([
                'nso007_production_request.material_warehouse' => $this->material_warehouse,
                'nso007_production_request_detail.material_id' => $this->product_id,
                'nso007_production_request_detail.type' => 'material',
                'nso007_production_request.status' => 1,
            ])
            ->get();
        $store = '';
        foreach ($material as $row) {
            $material_unit = ProductUnitModel::where(['product_id' => $row->material_id, 'unit_id' => $row->unit_id])->first();
            $qty_per_unit = 1;
            if ($material_unit->type == 'parent') {
                $qty_per_unit = findChildProductUnit($row->material_id);
                if ($qty_per_unit) {
                    $qty_per_unit = $qty_per_unit->qty_per_unit;
                }
            }
            $qty += $row->qty * $qty_per_unit;
        }
        $child_unit = findChildProductUnit($this->product_id);
        $child_unit = $child_unit ? $child_unit->qty_per_unit : 1;
        $parent = $qty % $child_unit;
        $child = intval($qty / $child_unit);

        $unit_p = findParentProductUnit($this->product_id);
        $unit_c = findChildProductUnit($this->product_id);
        if ($unit_p) {
            $parent = ($parent > 0 ? ($parent . ' ' . findUnit($unit_p->unit_id)->unit) : '');
        }
        if ($unit_c) {
            $child = ($child > 0 ? ($child . ' ' . findUnit($unit_c->unit_id)->unit) : '');
        }
        if ($parent > 0 && $child > 0) {
            $store = ' , ';
        }
        return $parent . $store . ($child ? $child : '');
    }

    public function return_stock()
    {
        $sale = \DB::table('nso007_sales')
            ->join('nso007_sale_details', 'nso007_sales.id', 'nso007_sale_details.sale_id')
            ->where('nso007_sales.warehouse_id', $this->warehouse_id)
            ->Where('nso007_sale_details.barcode', $this->barcode)
            ->sum('nso007_sale_details.return_qty');
        return $sale;
    }

    public function out_stock()
    {
        $sale = \DB::table('nso007_sales')
            ->join('nso007_sale_details', 'nso007_sales.id', 'nso007_sale_details.sale_id')
            ->where('nso007_sales.warehouse_id', $this->warehouse_id)
            ->where('nso007_sale_details.product_id', $this->product_id)
            ->where('nso007_sale_details.barcode', $this->barcode)
            ->sum('nso007_sale_details.qty');
        return $sale;
    }

    public function in_stock()
    {
        $purchase_child = PurchaseDetailsModel::selectRaw('SUM(qty) as qty,qty_per_unit')
            ->join('nso007_purchases', 'nso007_purchase_details.purchase_id', 'nso007_purchases.id')
            ->where('nso007_purchases.warehouse_id', $this->warehouse_id)
            ->where('nso007_purchase_details.barcode', $this->barcode)
            ->where('nso007_purchases.type', $this->type)
            ->where(function ($q){
                if($this->type==='fabric'){
                    $q->whereBetween('qty_per_unit',[$this->qty_per_unit-0.01,$this->qty_per_unit+0.01]);
                }
            })
            ->groupBy('nso007_purchase_details.barcode','nso007_purchases.warehouse_id','nso007_purchase_details.qty_per_unit')
            ->first();
        $pur = $purchase_child->qty??0;
        $product_id = $this->product_id;
        $product = getProductById($product_id);
        $unit = findUnit($product->unit_id);
        return $pur?($pur .' '.($unit->unit??'')):'';
    }

    public function in_stock_material()
    {
        $purchase_child = PurchaseDetailsModel::selectRaw('nso007_purchase_details.*')
            ->join('nso007_purchases', 'nso007_purchase_details.purchase_id', 'nso007_purchases.id')
            ->where('nso007_purchases.warehouse_id', $this->warehouse_id)
            ->where('nso007_purchase_details.product_id', $this->product_id)
            ->where('nso007_purchases.type', 'material')
            ->get();
        $qty = 0;

        foreach ($purchase_child as $row) {
            $product_unit = ProductUnitModel::where('product_id', $row->product_id)
                ->where('unit_id', $row->unit_id)
                ->first();
            $qty += $row->qty * ($product_unit->qty_per_unit??1);

        }
        return getStockQty($this->product_id, $qty);
    }

    public function cutting()
    {
        $detail = CuttingDetailModel::selectRaw('SUM(qty) as cutting_qty')
            ->join('nso007_production_cutting', 'nso007_production_cutting.id', 'nso007_production_cutting_detail.cutting_id')
            ->where('nso007_production_cutting.status', 1)
            ->where('nso007_production_cutting.from_warehouse', $this->warehouse_id)
            ->where('nso007_production_cutting_detail.barcode', $this->barcode)
            ->where('nso007_production_cutting_detail.fabric_id', $this->product_id)
            ->whereBetween('nso007_production_cutting_detail.qty_per_unit',[$this->qty_per_unit-0.01,$this->qty_per_unit+0.01])
            ->where('nso007_production_cutting_detail.type', 'fabric')->first();
        $unit = getProductById($this->product_id);
        $unit = findUnit($unit->unit_id);
        if ($detail->cutting_qty > 0) {
            return $detail->cutting_qty . ' ' . ($unit ? $unit->unit : '');
        }
    }

    public function cutting_qty()
    {
        $cut_qty = CuttingDetailModel::selectRaw('SUM(nso007_production_cutting_detail.qty*nso007_production_cutting_detail.size_qty) as cut_qty')
            ->join('nso007_production_cutting','nso007_production_cutting_detail.cutting_id','nso007_production_cutting.id')
            ->where('nso007_production_cutting_detail.barcode', $this->barcode)
            ->where('nso007_production_cutting.to_warehouse', $this->warehouse_id)
            ->first();
        return $cut_qty->cut_qty ? $cut_qty->cut_qty : '';
    }

    public function use_cut_piece()
    {
        $qty = ProductionRequestDetailModel::
        selectRaw( 'SUM(nso007_production_request_detail.qty*nso007_production_request_detail.template_qty) as use_qty')
//        selectRaw( 'nso007_production_request_detail.*,nso007_production_request.export_to_warehouse,nso007_production_request.material_warehouse')
            ->join('nso007_production_request', 'nso007_production_request.id', 'nso007_production_request_detail.production_id')
            ->where([
                'nso007_production_request.material_warehouse' => $this->warehouse_id,
                'nso007_production_request_detail.barcode' => $this->barcode,
                'nso007_production_request_detail.type' => 'item',
            ])
            ->groupBy('nso007_production_request_detail.barcode','nso007_production_request.material_warehouse')
            ->first();
        return $qty->use_qty??0;
    }

    public function transfer_stock_in()
    {
        $transfer = TransferStockDetailModel::selectRaw('SUM(nso007_transfer_stock_detail.qty) as qty')
            ->join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
            ->where('nso007_transfer_stock.confirm_status', 1)
            ->where('nso007_transfer_stock_detail.barcode', $this->barcode)
            ->where('nso007_transfer_stock.to_warehouse_id', $this->warehouse_id)
            ->first();
        $unit = getProductById($this->product_id);
        $unit = $unit ? $unit->unit_id : '';
        $unit = findUnit($unit);
        $unit = $unit ? $unit->unit : '';
        $transfer = $transfer ? $transfer->qty : 0;
        if ($transfer) {
            $transfer = '<u><b class="text-success" data-qty="' . $transfer . '">' . __('administrator.in') . ' </b>:<span class="text-primary">' . ($transfer ? ($transfer . $unit) : '0') . '</span></u>';
        } else {
            $transfer = '';
        }

        return $transfer;
    }

    public function transfer_stock_out()
    {
        $transfer_out = TransferStockDetailModel::selectRaw('SUM(nso007_transfer_stock_detail.qty) as qty')
            ->join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
            ->where('nso007_transfer_stock.confirm_status', 1)
            ->where('nso007_transfer_stock_detail.barcode', $this->barcode)
            ->where('nso007_transfer_stock.from_warehouse_id', $this->warehouse_id)
            ->first();
        $unit = getProductById($this->product_id);
        $unit = $unit ? $unit->unit_id : '';
        $unit = findUnit($unit);
        $unit = $unit ? $unit->unit : '';
        $transfer_out = $transfer_out ? $transfer_out->qty : 0;
        if ($transfer_out) {
            $transfer_out = '<u><b class="text-danger" data-qty="' . $transfer_out . '">' . __('administrator.out') . ' </b>: <span class="text-primary">' . ($transfer_out ? ($transfer_out . $unit) : '0') . '</span></u>';
        } else {
            $transfer_out = '';
        }
        return $transfer_out;


    }

    public function transfer_stock($type_, $from_date, $to_date)
    {
        if ($type_ == 'in') {
            $type = 'nso007_transfer_stock.to_warehouse_id';
        } else {
            $type = 'nso007_transfer_stock.from_warehouse_id';
        }
        $transfer = TransferStockDetailModel::selectRaw('SUM(nso007_transfer_stock_detail.qty) as qty,"' . $type_ . '" as type')
            ->join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
            ->where('nso007_transfer_stock_detail.barcode', $this->barcode)
            ->whereDate('nso007_transfer_stock.date', '>=', $from_date ? date('Y-m-d', strtotime($from_date)) : date('Y-m-d'))
            ->whereDate('nso007_transfer_stock.date', '<=', $to_date ? date('Y-m-d', strtotime($to_date)) : date('Y-m-d'))
            ->where($type, $this->warehouse_id)
            ->first();
        $unit = getProductById($this->product_id);
        $unit = $unit ? $unit->unit_id : '';
        $unit = findUnit($unit);
        $unit = $unit ? $unit->unit : '';
        $transfer = $transfer ? $transfer->qty : 0;
        if ($transfer) {
            $transfer = '<u><b class="text-' . ($type_ == 'in' ? 'success' : 'danger') . '" data-qty="' . $transfer . '">' . __('administrator.' . $type_) . ' </b>:<span class="text-primary">' . ($transfer ? ($transfer . $unit) : '0') . '</span></u>';
        } else {
            $transfer = '';
        }

        return $transfer;
    }
    public function request_stock()
    {

        $request_stock_in = ItemRequestDetailModel::selectRaw('SUM(qty) as qty')
            ->join('nso007_item_request', 'nso007_item_request_detail.request_id', 'nso007_item_request.id')
            ->where('nso007_item_request.to_warehouse', $this->warehouse_id)
            ->where('nso007_item_request_detail.barcode', $this->barcode)
            ->where('nso007_item_request.status', 1)
            ->where('nso007_item_request.confirm_status',1)
            ->groupBy('nso007_item_request_detail.barcode','nso007_item_request.to_warehouse')
            ->first();

        $request_stock_out = ItemRequestDetailModel::selectRaw('SUM(qty) as qty')
            ->join('nso007_item_request', 'nso007_item_request_detail.request_id', 'nso007_item_request.id')
            ->where('nso007_item_request.warehouse_id', $this->warehouse_id)
            ->where('nso007_item_request_detail.barcode', $this->barcode)
            ->where('nso007_item_request.status', 1)
            ->where('nso007_item_request.confirm_status', '!=',2)
            ->groupBy('nso007_item_request_detail.barcode','nso007_item_request.warehouse_id')
            ->first();
        $unit = getProductById($this->product_id);
        $unit = $unit ? $unit->unit_id : '';
        $unit = findUnit($unit);
        $unit = $unit ? $unit->unit : '';
        $request_stock_in = $request_stock_in ? $request_stock_in->qty : 0;
        $request_stock_out = $request_stock_out ? $request_stock_out->qty : 0;
        $text = '';
        if($request_stock_in){
            $text.="<b class='text-success' data-qty='$request_stock_in'>បានស្នើសុំ: </b>$request_stock_in $unit";

        }
        if($request_stock_in&&$request_stock_out){
            $text.=" , ";
        }
        if($request_stock_out){
            $text.="<b class='text-danger' data-qty='$request_stock_out'>បានចេញ: </b>$request_stock_out $unit";
        }
        return $text;

    }

    public function old_stock($date = null)
    {
        $code = $this->barcode;
        $warehouse = $this->warehouse_id;
        $in_stock = PurchaseDetailsModel::join('nso007_purchases', 'nso007_purchases.id', 'nso007_purchase_details.purchase_id')
            ->where('nso007_purchase_details.barcode', $code)
            ->where('nso007_purchases.warehouse_id', $warehouse)
            ->where('nso007_purchases.type', 'product')
            ->where(function ($q) use ($date) {
                if ($date) {
                    $q->where('nso007_purchases.purchase_date', '<', date('Y-m-d', strtotime($date)) . ' 00:00:00');
                }
            })
            ->selectRaw('SUM(nso007_purchase_details.qty) as total_qty,nso007_purchase_details.unit_id')
            ->first();
        $out_stock = SaleDetailsModel::join('nso007_sales', 'nso007_sales.id', 'nso007_sale_details.sale_id')
            ->where('nso007_sale_details.barcode', $code)
            ->where('nso007_sales.warehouse_id', $warehouse)
            ->where(function ($q) use ($date) {
                if ($date) {
                    $q->where('nso007_sales.sale_date', '<', date('Y-m-d', strtotime($date)) . ' 00:00:00');
                }
            })
            ->selectRaw('SUM(nso007_sale_details.qty) as total_qty,nso007_sale_details.unit_id')
            ->first();
        $old_stock = ($in_stock ? $in_stock->total_qty : 0) - ($out_stock ? $out_stock->total_qty : 0);
        $unit = findUnit($in_stock?$in_stock->unit_id:0);
        return $old_stock . ' ' . ($unit ? $unit->unit : '');

    }
    public function in_stock_today($date = null)
    {
        $code = $this->barcode;
        $warehouse = $this->warehouse_id;
        $in_stock = PurchaseDetailsModel::join('nso007_purchases', 'nso007_purchases.id', 'nso007_purchase_details.purchase_id')
            ->where('nso007_purchase_details.barcode', $code)
            ->where('nso007_purchases.warehouse_id', $warehouse)
            ->where('nso007_purchases.type', 'product')
            ->where(function ($q) use ($date) {
                if ($date) {
                    $q->whereDate('nso007_purchases.purchase_date',date('Y-m-d', strtotime($date)));
                }
            })
            ->selectRaw('SUM(nso007_purchase_details.qty) as total_qty,nso007_purchase_details.unit_id')
            ->first();
        $transfer = TransferStockDetailModel::selectRaw('SUM(nso007_transfer_stock_detail.qty) as qty')
            ->join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
            ->where('nso007_transfer_stock.confirm_status', 1)
            ->where('nso007_transfer_stock_detail.barcode', $this->barcode)
            ->where('nso007_transfer_stock.to_warehouse_id', $this->warehouse_id)
            ->where(function ($q) use ($date) {
                if ($date) {
                    $q->whereDate('nso007_transfer_stock.date',date('Y-m-d', strtotime($date)));
                }
            })
            ->first();
        $transfer = $transfer?$transfer->qty:0;
        $in = ($in_stock?$in_stock->total_qty:0);
        $unit = findUnit($in_stock?$in_stock->unit_id:0);
        return ($transfer+$in) . ' ' . ($unit ? $unit->unit : '');

    }
    public function out_stock_today($date = null)
    {
        $code = $this->barcode;
        $warehouse = $this->warehouse_id;
        $out_stock = SaleDetailsModel::join('nso007_sales', 'nso007_sales.id', 'nso007_sale_details.sale_id')
            ->where('nso007_sale_details.barcode', $code)
            ->where('nso007_sales.warehouse_id', $warehouse)
            ->where(function ($q) use ($date) {
                if ($date) {
                    $q->whereDate('nso007_sales.sale_date', date('Y-m-d', strtotime($date)));
                }
            })
            ->selectRaw('SUM(nso007_sale_details.qty) as total_qty,nso007_sale_details.unit_id')
            ->first();
        $transfer_out = TransferStockDetailModel::selectRaw('SUM(nso007_transfer_stock_detail.qty) as qty')
            ->join('nso007_transfer_stock', 'nso007_transfer_stock_detail.transfer_id', 'nso007_transfer_stock.id')
            ->where('nso007_transfer_stock.confirm_status', 1)
            ->where('nso007_transfer_stock_detail.barcode', $this->barcode)
            ->where('nso007_transfer_stock.from_warehouse_id', $this->warehouse_id)
            ->where(function ($q) use ($date) {
                if ($date) {
                    $q->whereDate('nso007_transfer_stock.date',date('Y-m-d', strtotime($date)));
                }
            })
            ->first();
        $transfer = $transfer_out?$transfer_out->qty:0;
        $out = ($out_stock?$out_stock->total_qty:0);
        $unit = findUnit($out_stock?$out_stock->unit_id:0);
        return ($transfer+$out) . ' ' . ($unit ? $unit->unit : '');

    }

    public function getSize()
    {
        return $this->hasOne(SizeModel::class, 'code','size_code');
    }

    public function getColor()
    {
        return $this->hasOne(ColorModel::class, 'code','color_code');
    }
    public function product()
    {
        return $this->hasOne(ProductModel::class, 'id','product_id');
    }
}

