<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Gate;
use App\Models\Material;
use Config;

class InventoryController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $user 		= auth()->user();
        $role 		= $user->getRole($user->id);
        $user_id    = $user->id;

        $res    	= $user->getResidential($user->id);
        $residential_id = $res->residential_id;

        $residential = DB::select("SELECT * FROM residential WHERE id IN ($residential_id) order by name asc");           

        return view('inventory.index', compact('residential', 'user', 'role'));
    }
	
	public function getInventoryList(Request $request) {
		define("TABLE_ITEM", Config::get('constants.tables.item'));
		define("TABLE_INVENTORY", Config::get('constants.tables.inventory'));
		
        // dd($request);
        $user    		= auth()->user();
        $role    		= $user->getRole($user->id);
        $user_id 		= $user->id;

        $res            = $user->getResidential($user->id);
        $residential_id = $res->residential_id;
        $company_id     = $res->company_id;

        $materials 		= Material::getMaterial('trade', $company_id);
        $user_info		= "";
        // dd($request);

        ## Read value
        $draw           = $request->get('draw');
        $start          = $request->get("start");
        $rowperpage     = $request->get("length"); // Rows display per page

        $columnIndex_arr= $request->get('order');
        $columnName_arr = $request->get('columns');
        $order_arr      = $request->get('order');
        $search_arr     = $request->get('search');

        $columnIndex    = $columnIndex_arr[0]['column']; 			// Column index
        $columnName     = $columnName_arr[$columnIndex]['data']; 	// Column name
        $columnSortOrder= $order_arr[0]['dir']; 					// asc or desc
        $searchValue    = $search_arr['value']; 					// Search value      

        $project_id     = $request['project_id'];       
        $source      	= $request['source'];
		$action			= "";

        ## Total records
        $totalRecords	= DB::table(TABLE_INVENTORY)->select(DB::raw('COUNT(*) as allcount'))
			->where('residential_id', '=', $project_id)              
			->where('company_id', '=', $company_id)->value('allcount');
        
        $totalRecordswithFilter = DB::table(TABLE_INVENTORY)->select(DB::raw('COUNT('.TABLE_INVENTORY.'.id) as allcount'))
			->leftJoin(TABLE_ITEM, TABLE_ITEM.'.id', '=', TABLE_INVENTORY.'.item_id')
			->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)                
			->where(TABLE_ITEM.'.name', 'LIKE', '%'.$searchValue.'%')->value('allcount');
       
		## Fetch records
        $records = DB::table(TABLE_INVENTORY)
			->select(TABLE_INVENTORY.'.*', TABLE_ITEM.'.name AS item_name', TABLE_ITEM.'.description')			
			->leftJoin(TABLE_ITEM, TABLE_ITEM.'.id', '=', TABLE_INVENTORY.'.item_id')
            ->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)
            ->where(TABLE_INVENTORY.'.company_id', '=', $company_id)            
            ->where(TABLE_ITEM.'.name', 'LIKE', '%'.$searchValue.'%')
			->orderBy($columnName,$columnSortOrder)
			->skip($start)->take($rowperpage)->get();  

        $data_arr   = array();
        $num        = 1;

		if($records) {
			foreach($records as $record){
				$fid			= $record->id;
				$unit         	= $record->unit;
				$qty         	= $record->qty;
				$item_id		= $record->item_id;
				$item_name		= $record->item_name;
				$description	= $record->description;
				$update_at		= $record->update_at;
				$create_by		= $record->create_by;
				// $vendor_id         	= $record->vendor_id;
				
				// if($vendor_id != 0) {
					// $vendor_info	= DB::table(Config::get('constants.tables.vendor'))->select('company_name', 'tel')
						// ->where('id', '=', $vendor_id)->get();
				// } else {
					// $vendor_info = array();
				// }
				
				$total_item         = 0;	//= DB::table(Config::get('constants.tables.do_item'))->select(DB::raw('COUNT(*) as allcount'))
					// ->where('do_id', '=', $fid)->value('allcount');
			
				// if($record->status == 1) {
					// $action	= '<a href="#" class="btn btn-light-success font-weight-bolder btnUpdateAction" data-toggle="modal" data-target="#ConfirmPopup" data-id="'.$fid.'" data-ref-no="'.$do_no.'" data-action="accept" >Accept</a>
					// <a href="#" class="btn btn-light-danger font-weight-bolder btnUpdateAction" data-toggle="modal" data-target="#ConfirmPopup" data-id="'.$fid.'" data-ref-no="'.$do_no.'" data-action="reject" >Reject</a>';
				// } else {
					// $action = '-';
				// }
					
					// $action = '<a href="'.$project_id.'/edit/'.$fid.'" class="action-btn"><i class="fas fa-edit" data-id="'.$fid.'"></i></a>';
				$data_arr[] = array(
					"id"			=> '',					
					"item_name"		=> $item_name,
					"desc"			=> $description,
					"tranx_date"	=> $update_at,
					"balance"		=> number_format($qty, 2),
					"unit"			=> $unit,					
					"action"    	=> '<a href="'.$project_id.'/detail?ref='.$item_id.'" class="action-btn"><i class="fas fa-eye" data-id="'.$fid.'"></i></a>'
					
				);

				$num++;
			}
		}
       
        $response = array(
            "draw"                  => intval($draw),
            "iTotalRecords"         => $totalRecords,
            "iTotalDisplayRecords"  => $totalRecordswithFilter,
            "aaData"                => $data_arr,			
        );
       
        echo json_encode($response);
        exit;        
    }

	public function getInventoryLog(Request $request) {
		define("TABLE_ITEM", Config::get('constants.tables.item'));
		define("TABLE_INVENTORY", Config::get('constants.tables.inventory'));
		define("TABLE_INVENTORY_LOG", Config::get('constants.tables.inventory_log'));
		
        // dd($request);
        $user    		= auth()->user();
        $role    		= $user->getRole($user->id);
        $user_id 		= $user->id;

        $res            = $user->getResidential($user->id);
        $residential_id = $res->residential_id;
        $company_id     = $res->company_id;

        $materials 		= Material::getMaterial('trade', $company_id);
        $user_info		= "";
        // dd($request);

        ## Read value
        $draw           = $request->get('draw');
        $start          = $request->get("start");
        $rowperpage     = $request->get("length"); // Rows display per page

        $columnIndex_arr= $request->get('order');
        $columnName_arr = $request->get('columns');
        $order_arr      = $request->get('order');
        $search_arr     = $request->get('search');

        $columnIndex    = $columnIndex_arr[0]['column']; 			// Column index
        $columnName     = $columnName_arr[$columnIndex]['data']; 	// Column name
        $columnSortOrder= $order_arr[0]['dir']; 					// asc or desc
        $searchValue    = $search_arr['value']; 					// Search value      

        $project_id     = $request['project_id'];       
        $source      	= $request['source'];
        $item_id      	= $request['item_id'];
		$start_date		= ($request['start_date'] != "") ? str_replace('/', '-', $request['start_date']) : "";
        $start_date    	= ($start_date != "") ? date("Y-m-d", strtotime($start_date)) : "";		//str_replace('/', '-', $request['start_date']);
        $end_date      	= ($request['end_date'] != "") ? str_replace('/', '-', $request['end_date']) : "";
		$end_date    	= ($end_date != "") ? date("Y-m-d", strtotime($end_date)) : "";
		
		$from_date		= "";
		$to_date		= "";
		$action			= "";
		
		DB::enableQueryLog();
		## Total records
		$totalRecords	= DB::table(TABLE_INVENTORY_LOG)->select(DB::raw('COUNT(*) as allcount'))
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)              
				->where(TABLE_INVENTORY.'.company_id', '=', $company_id);
				
		if($start_date != "") {
			$start_date = $start_date." 00:00:00";
			$totalRecords->where(TABLE_INVENTORY_LOG.'.create_at', '>=', $start_date);
		}
		
		if($end_date != "") {
			$end_date = $end_date." 23:59:59";
			$totalRecords->where(TABLE_INVENTORY_LOG.'.create_at', '<=', $end_date);
		}
		
		$totalRecords->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id)->value('allcount');
       
		/**
		if($start_date == "" && $end_date == "") {
			$totalRecords	= DB::table(TABLE_INVENTORY_LOG)->select(DB::raw('COUNT(*) as allcount'))
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)              
				->where(TABLE_INVENTORY.'.company_id', '=', $company_id)
				->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id)->value('allcount');
				
		} else {
			$totalRecords	= DB::table(TABLE_INVENTORY_LOG)->select(DB::raw('COUNT(*) as allcount'))
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)              
				->where(TABLE_INVENTORY.'.company_id', '=', $company_id)
				->where(TABLE_INVENTORY_LOG.'.create_at', '>=', $from_date)
				->where(TABLE_INVENTORY_LOG.'.create_at', '<=', $to_date)
				->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id)		
				->value('allcount');
			
			// ->where(function($q) use ($search_arr, $searchValue) {
						// $q->where('po_no', 'LIKE', '%'.$searchValue.'%')
						  // ->orWhere('create_at', 'LIKE', '%'.$searchValue.'%');
					// })
		
		}**/
		
		$totalRecordswithFilter = DB::table(TABLE_INVENTORY_LOG)->select(DB::raw('COUNT('.TABLE_INVENTORY_LOG.'.id) as allcount'))
				->leftJoin(TABLE_ITEM, TABLE_ITEM.'.id', '=', TABLE_INVENTORY_LOG.'.item_id')
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)
				->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id);
        
		if($start_date != "") {			
			$totalRecordswithFilter->where(TABLE_INVENTORY_LOG.'.create_at', '>=', $start_date);
		}
		
		if($end_date != "") {			
			$totalRecordswithFilter->where(TABLE_INVENTORY_LOG.'.create_at', '<=', $end_date);
		}
		
		$totalRecordswithFilter->where(TABLE_ITEM.'.name', 'LIKE', '%'.$searchValue.'%')->value('allcount');
		
		/**
		if($start_date == "" && $end_date == "") {
			$totalRecordswithFilter = DB::table(TABLE_INVENTORY_LOG)->select(DB::raw('COUNT('.TABLE_INVENTORY_LOG.'.id) as allcount'))
				->leftJoin(TABLE_ITEM, TABLE_ITEM.'.id', '=', TABLE_INVENTORY_LOG.'.item_id')
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)
				->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id)
				->where(TABLE_ITEM.'.name', 'LIKE', '%'.$searchValue.'%')->value('allcount');
		} else {
		// if($start_date != "" || $end_date != "") {
			$totalRecordswithFilter = DB::table(TABLE_INVENTORY_LOG)->select(DB::raw('COUNT('.TABLE_INVENTORY_LOG.'.id) as allcount'))
				->leftJoin(TABLE_ITEM, TABLE_ITEM.'.id', '=', TABLE_INVENTORY_LOG.'.item_id')
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)
				->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id)
				->where(TABLE_INVENTORY_LOG.'.create_at', '>=', $from_date)
				->where(TABLE_INVENTORY_LOG.'.create_at', '<=', $to_date)
				->where(TABLE_ITEM.'.name', 'LIKE', '%'.$searchValue.'%')->value('allcount');
		}**/
		
		
		## Fetch records
		$result = DB::table(TABLE_INVENTORY_LOG)
				->select(TABLE_INVENTORY_LOG.'.*', TABLE_ITEM.'.name AS item_name', TABLE_ITEM.'.description', TABLE_ITEM.'.unit')			
				->leftJoin(TABLE_ITEM, TABLE_ITEM.'.id', '=', TABLE_INVENTORY_LOG.'.item_id')
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)
				->where(TABLE_INVENTORY.'.company_id', '=', $company_id)
				->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id);
		
		if($start_date != "") {		
			$result->where(TABLE_INVENTORY_LOG.'.create_at', '>=', $start_date);
		}
		
		if($end_date != "") {			
			$result->where(TABLE_INVENTORY_LOG.'.create_at', '<=', $end_date);
		}
		
		// $records = $result->orderBy($columnName,$columnSortOrder)->skip($start)->take($rowperpage)->get();
		$records = $result->get();
				
		/**
		if($start_date == "" && $end_date == "") {			
		// if($start_date != "" || $end_date != "") {
			
				// ->where(TABLE_ITEM.'.name', 'LIKE', '%'.$searchValue.'%')
				->orderBy($columnName,$columnSortOrder)
				->skip($start)->take($rowperpage)->get();  
			 
		} else {
			$records = DB::table(TABLE_INVENTORY_LOG)
				->select(TABLE_INVENTORY_LOG.'.*', TABLE_ITEM.'.name AS item_name', TABLE_ITEM.'.description', TABLE_ITEM.'.unit')			
				->leftJoin(TABLE_ITEM, TABLE_ITEM.'.id', '=', TABLE_INVENTORY_LOG.'.item_id')
				->leftJoin(TABLE_INVENTORY, TABLE_INVENTORY.'.id', '=', TABLE_INVENTORY_LOG.'.inventory_id')
				->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)
				->where(TABLE_INVENTORY.'.company_id', '=', $company_id)
				->where(TABLE_INVENTORY_LOG.'.item_id', '=', $item_id)
				->where(TABLE_INVENTORY_LOG.'.create_at', '>=', $from_date)
				->where(TABLE_INVENTORY_LOG.'.create_at', '<=', $to_date)
				->orderBy($columnName,$columnSortOrder)
				->skip($start)->take($rowperpage)->get(); 

		}**/

        $data_arr   = array();
        $num        = 1;

		if($records) {
			foreach($records as $record){
				$fid			= $record->id;
				$unit         	= ($record->unit == "") ? "-" : $record->unit; 
				$qty         	= ($record->type == "add") ? "+".number_format($record->qty, 2) : "-".number_format($record->qty, 2);
				$balance       	= $record->last_qty;
				$item_name		= $record->item_name;
				$description	= $record->description;				
				$create_at		= $record->create_at;
				$type			= $record->type;
				$do_id			= $record->do_id;
				// $vendor_id         	= $record->vendor_id;
				
				// if($vendor_id != 0) {
					// $vendor_info	= DB::table(Config::get('constants.tables.vendor'))->select('company_name', 'tel')
						// ->where('id', '=', $vendor_id)->get();
				// } else {
					// $vendor_info = array();
				// }
				
				$do	= DB::table(Config::get('constants.tables.do'))->select(DB::raw('do_no'))
					->where('id', '=', $do_id)->first();
			
				// if($record->status == 1) {
					// $action	= '<a href="#" class="btn btn-light-success font-weight-bolder btnUpdateAction" data-toggle="modal" data-target="#ConfirmPopup" data-id="'.$fid.'" data-ref-no="'.$do_no.'" data-action="accept" >Accept</a>
					// <a href="#" class="btn btn-light-danger font-weight-bolder btnUpdateAction" data-toggle="modal" data-target="#ConfirmPopup" data-id="'.$fid.'" data-ref-no="'.$do_no.'" data-action="reject" >Reject</a>';
				// } else {
					// $action = '-';
				// }
					
					// $action = '<a href="'.$project_id.'/edit/'.$fid.'" class="action-btn"><i class="fas fa-edit" data-id="'.$fid.'"></i></a>';
				$data_arr[] = array(
					"id"			=> '',					
					"item_name"		=> $item_name,
					"do_no"			=> $do->do_no,
					"create_at"		=> $create_at,
					"qty"			=> ($record->type == "add") ? '<label style="color:green;">'.$qty.'</label>' : '<label style="color:red;">'.$qty.'</label>',
					"balance"		=> number_format($balance, 2),
					"unit"			=> $unit,					
					// "action"    	=> '<a href="'.$project_id.'/detail?ref='.$fid.'" class="action-btn"><i class="fas fa-eye" data-id="'.$fid.'"></i></a>'
					
				);

				$num++;
			}
		}
		$sql = DB::getQueryLog();
        $response = array(
            "draw"                  => intval($draw),
            "iTotalRecords"         => $totalRecords,
            "iTotalDisplayRecords"  => $totalRecordswithFilter,
            "aaData"                => $data_arr,			
            "sql"                	=> $sql,			
        );
       
        echo json_encode($response);
        exit;        
    }



    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        // echo 'InventoryController > show';
        $user 		= auth()->user();
        $role 		= $user->getRole($user->id);
        $user_id	= $user->id;

        $res		= $user->getResidential($user->id);
        $residential_id = $res->residential_id;

        $count  = DB::select("SELECT COUNT(id) as count FROM role_user WHERE FIND_IN_SET($id, residential_id)");        
        $count  = $count[0]->count;        
        if($count == 0) {
            dd("Permission not allow for the project");           
        }
		
		$residential = DB::select("SELECT * FROM residential WHERE id IN ($residential_id) order by name asc");
		
		if(Gate::check('is-site')) {
			return view('site.inventory', compact('residential', 'user', 'role'));
		} 

        return view('inventory.index', compact('residential', 'user', 'role'));
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit(Request $request, $id)
    {
		define("TABLE_INVENTORY", Config::get('constants.tables.inventory'));
		define("TABLE_ITEM", Config::get('constants.tables.item'));
		
        $user 		= auth()->user();
        $role 		= $user->getRole($user->id);
        $user_id	= $user->id;

        $res			= $user->getResidential($user->id);
        $residential_id = $res->residential_id;
		$company_id 	= $res->company_id;
		$project_id		= $id;
		$fid 			= $request['ref'];

        $count  = DB::select("SELECT COUNT(id) as count FROM role_user WHERE FIND_IN_SET($id, residential_id)");        
        $count  = $count[0]->count;        
        if($count == 0) {
            dd("Permission not allow for the project");           
        }
        
        $residential = DB::select("SELECT * FROM residential WHERE id IN ($residential_id) order by name asc");
		
		$inventory	= DB::table(TABLE_INVENTORY)->select(DB::raw(TABLE_INVENTORY.'.qty, '.TABLE_INVENTORY.'.id, '.TABLE_ITEM.'.name'))
			->leftJoin(TABLE_ITEM, TABLE_INVENTORY.'.item_id', '=', TABLE_ITEM.'.id')
			->where(TABLE_INVENTORY.'.company_id', '=', $company_id)            
			->where(TABLE_INVENTORY.'.residential_id', '=', $project_id)          
			->where(TABLE_INVENTORY.'.id', '=', $fid)
			->first();       
		
        return view('inventory.create', compact('residential', 'user', 'role', 'inventory'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}
