<?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 PurchaseOrderController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        echo 'PurchaseOrderController > 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('ro.index', compact('residential', 'user', 'role'));
    }
	
	public function getPoList(Request $request) {
        // 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'];
        $by_status      = $request['by_status'];
        $source      	= $request['source'];

        switch($by_status) {
            case 'new'      	: $by_status = 1;
                break;
			case 'pending'      : $by_status = 2;
                break;
            case 'po' 			: $by_status = 3;
                break;
			case 'do' 			: $by_status = 4;
                break;
            case 'completed'    : $by_status = 5;
                break;
            case 'rejected'     : $by_status = 6;
                break;
        }
        
        // $columnIndex    = "";
        // $columnName     = "name";
        // $columnSortOrder= "asc";
        // $searchValue    = "a";

        // Total records
        $totalRecords           = DB::table(Config::get('constants.tables.po'))->select(DB::raw('COUNT(*) as allcount'))
                ->where('residential_id', '=', $project_id)
                ->where('status', '=', $by_status)
                ->where('company_id', '=', $company_id)->value('allcount');  
        // Material::select('count(*) as allcount')->count();
        $totalRecordswithFilter = DB::table(Config::get('constants.tables.po'))->select(DB::raw('COUNT(*) as allcount'))
                ->where('residential_id', '=', $project_id)
                ->where('status', '=', $by_status)
				->value('allcount');  
                // ->where('po_no', 'LIKE', '%'.$searchValue.'%')
        //Material::select('count(*) as allcount')->where('name', 'like', '%' .$searchValue . '%')->count();

        // Fetch records
        // $records = Material::orderBy($columnName,$columnSortOrder)
        // ->where('trade.name', 'like', '%' .$searchValue . '%')
        // ->select('trade.*')
        // ->skip($start)
        // ->take($rowperpage)
        // ->get();

        $records = DB::table(Config::get('constants.tables.po'))
			// ->orderBy($columnName,$columnSortOrder)
            ->where('residential_id', '=', $project_id)
            ->where('company_id', '=', $company_id)
            ->where('status', '=', $by_status)
            ->where('po_no', 'LIKE', '%'.$searchValue.'%')
			// ->skip($start)
			->take($rowperpage)
			->get();  

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

		if($records) {
			foreach($records as $record){
				$fid				= $record->id;
				$create_by         	= $record->create_by;
				$vendor_id         	= $record->vendor_id;
				$user_info	    	= DB::table(Config::get('constants.tables.user'))->select('name', 'tel')
					->where('id', '=', $create_by)->get();  
				
				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();
				}				
					
				$po_no     			= $record->po_no;
				$create_at          = $record->create_at;       
				$status 			= ($record->status == 1) ? "Pending" : "";
				
				switch($record->status) {
					case '1'	: $status = "Pending";
						break;
					case '2'	: $status = "Approved";
						break;
					case '3'	: $status = "DO Processed";
						break;
					case '4'	: $status = "PO PRocessed";
						break;
					case '5'	: $status = "Completed";
						break;
					case '6'	: $status = "Rejected";
						break;
				}
				
				$total_item		= DB::table(Config::get('constants.tables.po_item'))->select(DB::raw('COUNT(*) as allcount'))
					->where('po_id', '=', $fid)->value('allcount');
					
				if($source == "po_page") {
					//<a href="{{ url('admin/requisition/'.$residential_id.'/create') }}" class="btn btn-light-primary font-weight-bolder"><i class="la la-plus"></i>Add</a>
					//<button type="button" class="btn btn-success font-weight-bold btnApproveRf" data-rf-id="" data-action="approve">Approve RF</button>
					$action	= '<a href="#" class="btn btn-light-primary font-weight-bolder btnSelect" data-id="'.$fid.'">Select</a>';
				} else {
					$action = '<a href="'.$project_id.'/edit/'.$fid.'" class="action-btn"><i class="fas fa-edit" data-id="'.$fid.'"></i></a>';
				}
					
				$data_arr[] = array(
					"id"                => '',            
					"po_no"				=> '<a class="modal-links po" data-toggle="modal" data-target="#po_form" data-id="'.$fid.'">'.$po_no.'</a>',	//$requisition_no,
					"total_item"        => $total_item,              
					"create_by"         => $user_info[0]->name,
					"create_at"         => $create_at,
					"vendor"           	=> ($vendor_info) ? $vendor_info[0]->company_name : array(),
					"rf_no"            	=> "",
					"do"            	=> "",
					"status"            => $status,					
					"action"            => $action
					// "action"    => '<a href="#" class="action-btn" data-toggle="modal" data-target="#edit_Item"><i class="fas fa-edit" data-id="'.$id.'"></i></a>'
				);

				$num++;
			}
		}
       
        $response = array(
            "draw"                  => intval($draw),
            "iTotalRecords"         => $totalRecords,
            "iTotalDisplayRecords"  => $totalRecords,
            // "iTotalDisplayRecords"  => $totalRecordswithFilter,
            "aaData"                => $data_arr,
			"user_info"				=> $user_info
        );
		
		// $response = array('draw'=> '', 'recordsTotal'=>$totalRecords, 'recordsFiltered'=>$totalRecordswithFilter, 'data' => $data_arr, 'request'=>$request); 
		
        // echo json_encode($response);

        // $response = array(
        //     "recordsTotal"  => intval( $totalRecords ),  // total number of records
		// 	"data"          => $data_arr, // total data array
		// 	"request"       => $request // total data array
        // );

        // echo json_encode(array('draw'=> '', 'recordsTotal'=>$totalData, 'recordsFiltered'=>$totalRecordswithFilter, 'data' => $data, 'request'=>$request)); 
        echo json_encode($response);
        exit;
    }

	public function getPoDetail(Request $request) {
        // dd($request);
		define("TABLE_COMPANY", Config::get('constants.tables.company'));
		define("TABLE_ITEM", Config::get('constants.tables.item'));
		define("TABLE_PO", Config::get('constants.tables.po'));
		define("TABLE_PO_ITEM", Config::get('constants.tables.po_item'));		
		define("TABLE_REQUISITION", Config::get('constants.tables.requisition'));
		define("TABLE_RESIDENTIAL", Config::get('constants.tables.residential'));
		define("TABLE_USER", Config::get('constants.tables.user'));		
		define("TABLE_VENDOR", Config::get('constants.tables.vendor'));		
		
        $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);		
		$residential 	= DB::select("SELECT * FROM ".Config::get('constants.tables.residential')." WHERE id IN ($residential_id) order by name asc"); 
		
		$status 		= "fail";
		$project_id     = $request['project_id'];
		$po_id     		= $request['id'];		
		$po_detail		= "";
		$po_list		= array();
		
		$company_info	= DB::table(TABLE_COMPANY)
			->select(TABLE_COMPANY.'.name AS company_name', TABLE_COMPANY.'.address AS company_addr', TABLE_RESIDENTIAL.'.name AS residential_name', TABLE_RESIDENTIAL.'.address AS residential_addr', TABLE_RESIDENTIAL.'.logo')
			->leftJoin(TABLE_RESIDENTIAL, TABLE_RESIDENTIAL.'.company_id', '=', TABLE_COMPANY.'.id')
            ->where(TABLE_COMPANY.'.id', '=', $company_id)
            ->where(TABLE_RESIDENTIAL.'.id', '=', $project_id)
            ->get(); 
		
		if($company_info && count($company_info) > 0) {
			$status = "success";
		} 
		// DB::enableQueryLog();
		$po_detail 	= DB::table(TABLE_PO)
			->select(TABLE_PO.'.*', TABLE_USER.'.name AS creator_name', TABLE_USER.'.tel AS creator_tel', TABLE_VENDOR.'.company_name AS vendor_company', TABLE_VENDOR.'.address AS vendor_addr', TABLE_REQUISITION.'.requisition_no')
			->leftJoin(TABLE_USER, TABLE_USER.'.id', '=', TABLE_PO.'.create_by')
			->leftJoin(TABLE_VENDOR, TABLE_VENDOR.'.id', '=', TABLE_PO.'.vendor_id')
			->leftJoin(TABLE_REQUISITION, TABLE_REQUISITION.'.id', '=', TABLE_PO.'.requisition_id')
            ->where(TABLE_PO.'.id', '=', $po_id)
            ->where(TABLE_PO.'.company_id', '=', $company_id)
            // ->where(TABLE_REQUISITION.'.company_id', '=', '9')
            ->get(); 
		
				
		$po_list[]	= DB::table(TABLE_PO_ITEM)
			->select(TABLE_PO_ITEM.'.qty', TABLE_PO_ITEM.'.remark', TABLE_PO_ITEM.'.item_id', TABLE_PO_ITEM.'.requisition_item_id', TABLE_ITEM.'.name', TABLE_ITEM.'.description', TABLE_ITEM.'.unit', TABLE_ITEM.'.img')			
			->leftJoin(TABLE_ITEM, TABLE_PO_ITEM.'.item_id', '=', TABLE_ITEM.'.id')
			->leftJoin(TABLE_PO, TABLE_PO_ITEM.'.po_id', '=', TABLE_PO.'.id')
            ->where(TABLE_PO.'.id', '=', $po_id)
            ->where(TABLE_PO.'.company_id', '=', $company_id)
			->orderBy(TABLE_ITEM.'.name', 'desc')            
            ->get();

		// dd(DB::getQueryLog()); 
			
		// if($rf_detail && count($rf_detail) > 0) {
			// $status = "success";			
		// } 
		
		// return view('material.index', compact('residential', 'user', 'role', 'materials', 'result'));
		echo json_encode(array('status' => $status, 'company_info' => $company_info, 'po_detail' => $po_detail, 'po_list' => $po_list));
	}
	 
    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create($id)
    {
        $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;

        $items          = Material::getMaterial('item', $company_id);
        // $count  = DB::select("SELECT COUNT(id) as count FROM role_user WHERE FIND_IN_SET($id, residential_id) AND user_id = '$user_id'"); 
        $count  = DB::select("SELECT COUNT(id) as count FROM ".Config::get('constants.tables.role_user')." WHERE FIND_IN_SET($id, residential_id) AND user_id = '$user_id'");        
        $count  = $count[0]->count;
       
        if($count == 0) {
            // dd("Permission not allow for the project");
            return redirect()->back(); 
            //  return view('overview.index', compact('residential', 'user', 'role'));
        }
        
        $residential= DB::select("SELECT * FROM ".Config::get('constants.tables.residential')." WHERE id IN ($residential_id) order by name asc");           
		
		$vendors 	= DB::select("SELECT * FROM ".Config::get('constants.tables.vendor')." WHERE company_id = '$company_id' order by company_name asc");   
		$po_list 	= DB::select("SELECT po.*, v.company_name FROM ".Config::get('constants.tables.po')." AS po LEFT JOIN vendor AS v ON v.id=po.company_id WHERE po.company_id = '$company_id' AND po.residential_id = '$id' order by po.id desc");   
		
        return view('po.create', compact('residential', 'user', 'role', 'items', 'company_id', 'vendors', 'po_list'));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        $user       = auth()->user();
        $user_id    = $user->id;
        $res        = $user->getResidential($user->id);
        $company_id = $res->company_id;
       
        $project_id = $request['project_id'];
        $rf_id	 	= $request['rf_id'];
        $list       = $request['list'];       
        $vendor_id	= $request['vendor_id'];       

        $residential = DB::select("SELECT * FROM ".Config::get('constants.tables.residential')." WHERE id = '$project_id' AND company_id = '$company_id'")[0];       
       
        if($residential == false) {
            $msg = "Invalid request.";
            return response()->json(['status' => 'fail', 'msg' => $msg, 'request'=>$request->all()]);
            exit;
        }

		
		$total_po   = DB::table(Config::get('constants.tables.po'))->select(DB::raw('COUNT(*) as count'))
		->where('company_id', '=', $company_id)            
		->where('residential_id', '=', $project_id)          
		->whereRaw('Date(create_at) = CURDATE()')->value('count');

		$total_po   = $total_po + 1;
		$po_no     	= "PO-".$residential->code.date("ymd").str_pad($total_po, 3, "0", STR_PAD_LEFT);
		
		$data_row['company_id']     = $company_id;
		$data_row['residential_id'] = $request['project_id'];
		$data_row['po_no'] 			= $po_no;
		$data_row['requisition_id'] = $rf_id;
		$data_row['po_file'] 		= "";
		$data_row['po_date'] 		= '0000-00-00';
		$data_row['status']         = 1;
		$data_row['vendor_id']      = $vendor_id;
		$data_row['create_by']      = $user_id;
		$data_row['create_at']      = date("Y-m-d H:i:s");

		$id = DB::table(Config::get('constants.tables.po'))->insertGetId($data_row);

        if($id) {
            $msg = "PO created! - ID: ".$id;

            foreach($list as $row)   {
                $temp = array();

                $temp['po_id'] 				= $id;               
                $temp['item_id']        	= $row['id'];
                $temp['qty']            	= $row['qty'];               
                $temp['requisition_item_id']= $row['rf_item_id'];               
                $temp['create_by']      	= $user_id;
                $temp['create_at']      	= date("Y-m-d H:i:s");

                DB::table(Config::get('constants.tables.po_item'))->insert($temp);
            }
			
			$data_row = array();			
			$data_row['status']         = 3;
			$data_row['update_by']      = $user_id;
			$data_row['update_at']      = date("Y-m-d H:i:s");
			
			$update_result = DB::table(Config::get('constants.tables.requisition'))->where('id', '=', $rf_id)->update($data_row);
        }

        // if($request['type'] == "trade") {
        //     $validator = Validator::make($request->all(), [
        //         'trade_name' => 'required|max:1000',    
        //     ]);


        //     $data_row['company_id'] = $company_id;
        //     $data_row['parent_id']  = isset($request['trade_parent']) ? $request['trade_parent'] : 0;
        //     $data_row['name']       = $request['trade_name'];
        //     $data_row['create_by']  = $user_id;
        //     $data_row['create_at']  = date("Y-m-d H:i:s");

        //     $id = DB::table('trade')->insertGetId($data_row);

        // } 

        return response()->json(['status' => 'success', 'msg' => $msg, 'request'=>$request->all(), 'po_id'=>$id]);
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        // echo 'PurchaseOrderController > 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) AND user_id = '$user_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.po', compact('residential', 'user', 'role'));
		}

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

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id, $fid)
    {        
		echo 'PurchaseOrderController > Edit > '.$id;       
        // DB::enableQueryLog();
        // print_r($id);
        $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;

        $count  = DB::select("SELECT COUNT(id) as count FROM ".Config::get('constants.tables.role_user')." WHERE FIND_IN_SET($id, residential_id) AND user_id = '$user_id'"); 
        // print_r($count);
        $count  = $count[0]->count;
        // echo 'COUNT : '.$count;
        if($count == 0) {
            // dd("Permission not allow for the project");
            return redirect()->back();  //->with('error', 'Permission not allow for the project');
            //  return view('overview.index', compact('residential', 'user', 'role'));
        }
		
		$po_list 	= DB::select("SELECT po.*, v.company_name FROM ".Config::get('constants.tables.po')." AS po LEFT JOIN vendor AS v ON v.id=po.company_id WHERE po.company_id = '$company_id' AND po.residential_id = '$id' AND po.id <> '$fid' ORDER BY po.id desc");   
		$vendors 	= DB::select("SELECT * FROM ".Config::get('constants.tables.vendor')." WHERE company_id = '$company_id' order by company_name asc"); 
        
        $residential = DB::select("SELECT * FROM ".Config::get('constants.tables.residential')." WHERE id IN ($residential_id) order by name asc");           

        return view('po.create', compact('residential', 'user', 'role', 'po_list', 'vendors'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        $user       = auth()->user();
        $user_id    = $user->id;
        $res        = $user->getResidential($user->id);
        $company_id = $res->company_id;
       
        $project_id 	= $request['project_id'];
        $list       	= $request['list'];       
        $po_no      	= $request['po_no'];       
        $po_file      	= $request['po_file'];       
        $vendor_id      = $request['vendor_id'];       
        $update_result 	= ""; 
		$status			= "";

        $residential = DB::select("SELECT * FROM ".Config::get('constants.tables.residential')." WHERE id = '$project_id' AND company_id = '$company_id'")[0];       
       
        if($residential == false) {
            $msg = "Invalid request.";
            return response()->json(['status' => 'fail', 'msg' => $msg, 'request'=>$request->all()]);
            exit;
        }
		
		// $total_rf   = DB::table(Config::get('constants.tables.requisition'))->select(DB::raw('COUNT(*) as count'))
		// ->where('company_id', '=', $company_id)            
		// ->whereRaw('Date(create_at) = CURDATE()')->value('count');

		// $total_rf   = $total_rf + 1;
		// $rf_no      = "RF-".$residential->code.date("ymd").str_pad($total_rf, 3, "0", STR_PAD_LEFT);
		$data_row = array();
		// $data_row['company_id']     = $company_id;
		// $data_row['residential_id'] = $request['project_id'];
		$data_row['po_no'] 			= $po_no;
		$data_row['po_file'] 		= $po_file;
		$data_row['vendor_id'] 		= $vendor_id;
		$data_row['status']         = 1;
		$data_row['update_by']      = $user_id;
		$data_row['update_at']      = date("Y-m-d H:i:s");
		
		$update_result = DB::table(Config::get('constants.tables.po'))->where('id', '=', $id)->update($data_row);

		DB::table(Config::get('constants.tables.po_item'))->where('po_id', $id)->delete();		       

        if($id) {
            $msg = "PO updated! - ID: ".$id;

            foreach($list as $row)   {
                $temp = array();

                $temp['po_id'] 				= $id;
                $temp['requisition_item_id']= $row['rf_item_id'];
                $temp['item_id']        	= $row['id'];
                $temp['qty']            	= $row['qty'];
                // $temp['status']         	= 1;
                $temp['create_by']      	= $user_id;
                $temp['create_at']      	= date("Y-m-d H:i:s");

                $update_result = DB::table(Config::get('constants.tables.po_item'))->insert($temp);
            }
			
			if($update_result) {
				$status = "success";
			} else {
				$status = "fail";
				$msg 	= "Failed to update.";
			}
        }
		
		return response()->json(['status' => $status, 'msg' => $msg, 'request'=>$request->all(), 'po_id'=>$id]);
    }

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