<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use DB;

class Stock extends Model
{
    use HasFactory;

    protected $fillable = ['image','item','design','supplierserialno','grosswtgms','netgoldwtgms','diamondwtcts','diamondclarity','diamondrate','diamondcolour','labourgold','colorstonewtcts','colorstonerate','kwdcost','saleamount','consultancy','diacsamount','customamount','totalamount','status'];

    public function getAllStocks($search = [], $sort = array(), $limit = null, $offset = null) {
        $stockObj = self::select('*')->where('status','=','Available');

        if ($search['freetext'] != '') {
            $stockObj->where(function ($query) use ($search) {
                $query->orWhere('image', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('design', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('supplierserialno', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('item', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('grosswtgms', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('netgoldwtgms', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondwtcts', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondclarity', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondrate', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondcolour', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('labourgold', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('colorstonewtcts', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('colorstonerate', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('kwdcost', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('totalamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('saleamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('consultancy', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diacsamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('customamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('barcodenumber', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('status', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere(DB::raw("DATE_FORMAT(created_at,'%d-%m-%Y %H:%i:%s')"), "like", "%" . $search['freetext'] . "%");
            });
        }
        $fields = array('', 'created_at','supplierserialno','image','design','item','grosswtgms','netgoldwtgms','labourgold','diamondwtcts','diamondrate','diamondcolour','diamondclarity','colorstonewtcts','colorstonerate','consultancy','diacsamount','totalamount','customamount','kwdcost','');
        if (!empty($sort)) {
            foreach ($sort as $index => $type) {
                if (!empty($fields[$index])) {
                    $stockObj->orderBy($fields[$index], $type);
                }
            }
        }

        if (empty($limit)) {
            return $stockObj->count();
        } else {
            $stockObj->offset($offset);
            $stockObj->limit($limit);
            return $stockObj->get();
        }
    }

    public function scopeActive($query)
    {
        return $query->where('status', 'Active');
    }

    public function getAllStockshistory($search = [], $sort = array(), $limit = null, $offset = null) {
        $stockhistoryObj = self::select('*')->where('status','!=','Available');

        if ($search['freetext'] != '') {
            $stockhistoryObj->where(function ($query) use ($search) {
                $query->orWhere('image', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('design', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('supplierserialno', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('item', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('grosswtgms', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('netgoldwtgms', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondwtcts', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondclarity', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondrate', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diamondcolour', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('labourgold', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('colorstonewtcts', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('colorstonerate', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('kwdcost', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('totalamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('saleamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('consultancy', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('diacsamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('customamount', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere('status', 'like', "%" . $search["freetext"] . "%");
                $query->orWhere(DB::raw("DATE_FORMAT(created_at,'%d-%m-%Y %H:%i:%s')"), "like", "%" . $search['freetext'] . "%");
            });
        }
        $fields = array('', 'created_at','supplierserialno','image','design','item','grosswtgms','netgoldwtgms','labourgold','diamondwtcts','diamondrate','diamondcolour','diamondclarity','colorstonewtcts','colorstonerate','consultancy','diacsamount','totalamount','customamount','saleamount','');
        if (!empty($sort)) {
            foreach ($sort as $index => $type) {
                if (!empty($fields[$index])) {
                    $stockhistoryObj->orderBy($fields[$index], $type);
                }
            }
        }
        
        if (empty($limit)) {
            return $stockhistoryObj->count();
        } else {
            $stockhistoryObj->offset($offset);
            $stockhistoryObj->limit($limit);
            return $stockhistoryObj->get();
        }
    }

}
