<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Redirect;
use Illuminate\Auth\Middleware\Authenticate;
use Auth;
use App\PurchaseRequestSea;
use App\MaterialRequisitionSea;
use App\PrMrSea;
use App\PRproductSea;
use App\MRseaapprovalproduct;
use App\SupplierRateEntry;
use App\PRSupplierProduct;
use App\PRSupplierProductSea;
use App\MRseaproductschedule;
use App\MRseaapprovedproductschedule;
use App\PRproductScheduleSea;
use App\Product;
use App\UnitMeasurement;
use App\PRsupplierSea;
use App\SupplierUnitRate;
use App\Common;
use App\StandardMaster;
use App\GradeMaster;
/**
* 
*/
class PurchaseRequestSeaController extends Controller
{
  //pr listing page
	 public function index()
	 {	
	 	
	 	 $requests = PurchaseRequestSea::whereIn('status',['GEN','SUP'])->get(); 

	 	 return view('purchase_request_sea/index',compact('requests'));
	 }

  //purchase request sea generation
	public function create()
	{
     $material_list = MaterialRequisitionSea::where('status','=','APR')
											->where('pr_status','=',0)
											->pluck('id')->toArray();

	   $mr_pdts = MRseaproductschedule::whereIn('material_requisition_sea_id',$material_list)->get();
	   							
  	 if(count($material_list) > 0){
    	   	 $Pr_lists = PrMrSea::pluck('mr_sea_id')->toArray();
    	   	 $lists = MaterialRequisitionSea::where('status','=','APR')
    									    ->where('pr_status','=',0)
    	   	                ->whereNotIn('id',$Pr_lists)
    	   	                ->pluck('id')->toArray();
  	   	                              
  	   	if(count($lists) > 0){
    	   	 $count = PurchaseRequestSea::count();
    			 $obj = PurchaseRequestSea::create(array(
    				 			        'request_no'    => 'PR'.($count + 1),
    			                'request_date'  =>  date('Y-m-d'),
    			                'status'        =>  'SUP' ,
    			                'entered_by'    =>  Auth::user()->id,
    				 	    ));
  			 		                               
  		   	  foreach($lists as $list){
    		   	 	PrMrSea::create(array(
    						       'pr_sea_id'    =>   $obj->id,
    		                'mr_sea_id'    =>   $list,
    					));   
  		   	  }

  		   	 $products = MRseaapprovalproduct::whereIn('material_requisition_sea_id',$lists)->groupBy('product_id','unit_id','grade_id','requested_month','schedule_id','id')->selectRaw('sum(qty) as product_sum,unit_id,grade_id,product_id,requested_month,schedule_id,id')->get();

    			 foreach($products as $pdt ){
      			 $data=	PRproductSea::create([
      					'purchase_request_sea_id' => $obj->id,
      					'product_id'              => $pdt->product_id,
      					'qty'                     => $pdt->product_sum,
      					'apr_qty'                 => $pdt->product_sum,
      					'unit_id'                 => $pdt->unit_id,
      					'grade_id'                => $pdt->grade_id,
      					'status'                  => 'G',
      					'requested_month'         => $pdt->requested_month,
      					'schedule_id'             => $pdt->schedule_id ]);
    			   
               $mr_pdts = MRseaapprovedproductschedule::where('mr_sea_approval_product_id','=',$pdt->id)
    			        ->get();
                			 foreach($mr_pdts as $mr){
                			 	PRproductScheduleSea::create([
                			 		     'purchase_request_sea_id'  => $obj->id,
                			 		     'pr_products_sea_id'       => $data->id, 
                			 		     'product_id'               => $mr->product_id, 
                			 		     'qty'                      => $mr->qty,
                					     'apr_qty'                  => $mr->qty,
                					     'unit_id'                  => $mr->unit_id,
                					     'grade_id'                 => $mr->grade_id,
                					     'status'                   => 'G',
                					     'scheduled_date'           => $mr->scheduled_date,
                					     'schedule_id'              => $pdt->schedule_id
                					      ]);
                			 }    
    			  }
  	   	}                                
          return redirect()->action('PurchaseRequestSeaController@index');

  	 }else{
  	   	 return redirect()->route('sea.purchase.request.index')->with('error','error!');
  	 }									

	 }

	 public function purchaseMrList($id)
	 {
  	 	$pr = PurchaseRequestSea::find($id);
  	 	$mrlist = PrMrSea::where('pr_sea_id','=', $id)
  	 				->join('material_requisition_sea','pr_mr_sea.mr_sea_id' ,'=', 'material_requisition_sea.id')
  	 				->select('material_requisition_sea.request_no','material_requisition_sea.id'
  	 					,'material_requisition_sea.request_date','material_requisition_sea.approved_date')
  	 				->get();

  		 foreach ($mrlist as $mr) {

  		 		$productList = MRseaapprovalproduct::where('material_requisition_sea_id','=',$mr->id)->get();
  		 		$mr->productList  = $productList;
  		 	
  		  }

		  return view('purchase_request_sea/mr_list',compact('mrlist','pr'));
	 }

	 public function edit($id)
	 {
  	 	$supplier_count = [];
  	 	$prid = $id;
  	 	$today = date('Y-m-d');
          $purchase_request_sea_id = $id;
  	 	$purchase_products = PRproductSea::where('purchase_request_sea_id','=',$prid)->where('status','=','G')->get();
         
  		foreach ($purchase_products as $prodList) 
  		{
  			$daily_price = SupplierRateEntry::
  						join('unit_of_measurement','supplier_rate_entry.unit_id','=','unit_of_measurement.id')
  						->join('supplier','supplier_rate_entry.supplier_id','=','supplier.id')
  						->where('supplier_rate_entry.is_active' ,'=', 'Y')
  						->where('product_id','=',$prodList->product_id)
  						->select('supplier.id','supplier.name','supplier_rate_entry.price','unit_of_measurement.unit')->orderBy('supplier_rate_entry.price')->get();
  			$prodList->daily_price = $daily_price;
        $apr_qty = PRproductScheduleSea::where('pr_products_sea_id','=',$prodList->id)->where('supplier_id','!=',NULL)->sum('apr_qty');
        $req_qty = PRproductScheduleSea::where('pr_products_sea_id','=',$prodList->id)->sum('qty');  
        $prodList->bal_qty = $req_qty - $apr_qty;
  			$supplier_count[$prodList->id] = PRproductScheduleSea::where('pr_products_sea_id','=',$prodList->id)
  			->where('supplier_id','!=',NULL)->count();
		  }
		return view('purchase_request_sea/edit',compact('purchase_products','prid','supplier_count','purchase_request_sea_id'));
	 }
    //price and prt schedule listing page
	 public function supplierPriceList(Request $request)
	 {
      $common = Common::where('name','=','SEA SHIPMENT')
                      ->first();
  	 	$pr_prd_sea = PRproductSea::find($request->get('pr_prd_sea'));

      $prd_unit = Product::find($pr_prd_sea->product_id); 
      if($prd_unit->base_unit_id == $pr_prd_sea->unit_id)
      {
        $unit_id = $pr_prd_sea->unit_id;
      }else{
        $unit_id = $prd_unit->base_unit_id;
      }     

  	 	$price = SupplierRateEntry::join('supplier','supplier.id','=','supplier_rate_entry.supplier_id')
  	 	                     ->leftjoin('grade_master','grade_master.id','=','supplier_rate_entry.grade_id')
       	                   ->leftjoin('standard_master','standard_master.id','=','supplier_rate_entry.standard_id')
       	                   ->leftjoin('unit_of_measurement','unit_of_measurement.id','=','supplier_rate_entry.unit_id')
       	                   ->join('currencies','currencies.id','=','supplier.currency_type_id')
  	 	                     ->where('supplier_rate_entry.product_id','=',$pr_prd_sea->product_id)
  	 	                     ->where('supplier_rate_entry.unit_id','=',$unit_id)
                           ->where('supplier.delivery_type_id','=',$common->id)
  	 	  				           ->where('supplier_rate_entry.is_active' ,'=', 'Y')
                           ->where('supplier_rate_entry.price' ,'!=', 'NULL');
      
      if($pr_prd_sea->grade_id != NULL){
      $price = $price->where('supplier_rate_entry.grade_id','=',$pr_prd_sea->grade_id);
      } 

      $price = $price->selectRaw('supplier.name,supplier_rate_entry.price,standard_master.standard,grade_master.grade,unit_of_measurement.unit,currencies.currency,supplier.id')->get();

  	 	$prd = Product::find($request->get('prd'))->name;

      $unit = UnitMeasurement::find($pr_prd_sea->unit_id)->unit;
      $data = PRproductScheduleSea::leftjoin('supplier','supplier.id','=','pr_products_schedule_sea.supplier_id')
                                      ->where('pr_products_sea_id','=',$request->get('pr_prd_sea'))
                                      ->selectRaw('supplier.name,pr_products_schedule_sea.*')->get();
          foreach($data as $dt){
          	$dt->date = date('d-M-Y',strtotime($dt->scheduled_date));
          }
         
       	$apr_qty = PRproductScheduleSea::where('pr_products_sea_id','=',$request->get('pr_prd_sea'))->where('supplier_id','!=',NULL)
       	    ->sum('apr_qty');
       	$req_qty = PRproductScheduleSea::where('pr_products_sea_id','=',$request->get('pr_prd_sea'))->sum('qty');  
       	$bal_qty = $req_qty - $apr_qty;                            
        $data = array($price,$unit,$prd,$data,$apr_qty,$bal_qty);
             
        return $data;  						  
     }
     
     public function getPrdetail(Request $request)
     {
       	$data = PRproductScheduleSea::find($request->get('option'));
       	return $data;
     }

     public function updateSupPr(Request $request)
     {
        $schdle =  PRproductScheduleSea::find($request->get('id'));
        $exist = PRsupplierSea::where('purchase_request_sea_id','=',$schdle->purchase_request_sea_id)
                  ->where('supplier_id','=',$request->get('sup_id'))->where('status','=','PR')->count();
        if($exist == 0){
       	

       	if($schdle->qty < $request->get('qty')){
       		return 'Grater';
       	}

       	 
        $pdt_sea_pr = PRSupplierProductSea::where('pr_products_schedule_sea_id','=',$request->get('id'))->first();
        
        if($pdt_sea_pr == NULL){
            $pdt_unit = Product::find($schdle->product_id);
          	$unit_price = SupplierRateEntry::where('supplier_id','=',$request->get('sup_id'))->where('product_id','=',$schdle->product_id)
          	    ->where('is_active','=','Y')->first();

              //calculate the unit conversion value
              if($schdle->unit_id == $pdt_unit->base_unit_id){
                $price = $unit_price->price * $request->get('qty');
                $unitPrice = $unit_price->price;	
              }else{

              	$unit_rate = SupplierUnitRate::where('supplier_id','=',$request->get('sup_id'))
                                               ->where('product_id','=',$schdle->product_id)
                                               ->where('unit_id','=',$schdle->unit_id);
                  if($schdle->grade_id > 0){
                  	$unit_rate = $unit_rate->where('grade_id','=',$schdle->grade_id);
                  }
                  $unit_rate = $unit_rate->first();

                  if(count($unit_rate) == 0){
      			        return 'Add Price';
      		        } 
      		    
                $unitPrice = $unit_price->price * $unit_rate->value;
                  
                  if(count($unit_rate) > 0){
                  	 $price = $unit_rate->value * $unit_price->price * $request->get('qty');
                  }
              }  

            $std = null;
            $std_name = explode('-', $request->get('sup'));
            $nm_std = explode('(', $std_name[0]);
                  $grd_name = explode(')', $std_name[1]);
            
            if($nm_std[1] != null){
             $std = StandardMaster::where('standard','like',$nm_std[1])->first(); 
            }

            if($schdle->grade_id > 0){
                     $grd_id = $schdle->grade_id;
            }else if($grd_name[0] != null) {
               $gr_id = GradeMaster::where('grade','like',$grd_name[0])->first(); 
               $grd_id = (count($gr_id) > 0) ? $gr_id->id : null;
            }else{
              $grd_id = null; 
            } 

            $data = PRproductScheduleSea::where('id','=',$request->get('id'))
                           ->update(['supplier_id'  => $request->get('sup_id'), 
                                     'apr_qty'      => $request->get('qty')]);

                          
            $chk = PRsupplierSea::where('purchase_request_sea_id','=',$schdle->purchase_request_sea_id)
                            ->where('supplier_id','=',$request->get('sup_id'))->first();

            if($chk == NULL){
              $sup_sea =  PRsupplierSea::create([
                 'purchase_request_sea_id'     => $schdle->purchase_request_sea_id, 
                   'supplier_id'                 => $request->get('sup_id'),
                   'status'                      => 'G']);
            }

            $sup_sea_id = ($chk == NULL) ? $sup_sea->id : $chk->id;     

          	PRSupplierProductSea::create([
          		'pr_supplier_sea_id' => $sup_sea_id,
          		'pr_product_sea_id'  => $schdle->pr_products_sea_id,
          		'pr_products_schedule_sea_id' => $request->get('id'),
          		'product_id'                  => $schdle->product_id,
          		'qty'                         => $schdle->qty,
          		'apr_qty'                     => $request->get('qty'),
          		'unit_id'                     => $schdle->unit_id,
          		'grade_id'                    => $grd_id,
          		'standard_id'                 => ($std != NULL) ? $std->id : $std,
          		'unitprice'                   => $unitPrice,
          		'actual_unit'                 => $unitPrice,
          		'price'                       => $price,
          		'scheduled_date'              => $schdle->scheduled_date,
          		'schedule_id'                 => $schdle->schedule_id,
          		'status'                      => $schdle->qty ]);
        }
         
       	$count = PRproductScheduleSea::where('pr_products_sea_id','=',$request->get('pr_prd_id'))->where('supplier_id','!=',NULL)->count();
       	$apr_qty = PRproductScheduleSea::where('pr_products_sea_id','=',$request->get('pr_prd_id'))->where('supplier_id','!=',NULL)->sum('apr_qty');
       	$req_qty = PRproductScheduleSea::where('pr_products_sea_id','=',$request->get('pr_prd_id'))->sum('qty');  
       	$bal_qty = $req_qty - $apr_qty;
       	$value = array($data,$apr_qty,$bal_qty,$count);  
       	 return $value;
        }else{
          return 'EXIST';
        }                 
       	                   
     }

    public function priceList(Request $request)
     {
        $common = Common::where('name','=','SEA SHIPMENT')
                        ->first();
        $pdtSea = PRproductSea::find($request->get('pr_sea_pdt')); 
        $prd = Product::find($pdtSea->product_id); 
       
        if($prd->base_unit_id == $pdtSea->unit_id)
        {
          $unit_id = $pdtSea->unit_id;
        }else{
          $unit_id = $prd->base_unit_id;
        }              
      
      $data = SupplierRateEntry::join('supplier','supplier.id','=','supplier_rate_entry.supplier_id')
       	                   ->leftjoin('grade_master','grade_master.id','=','supplier_rate_entry.grade_id')
       	                   ->leftjoin('standard_master','standard_master.id','=','supplier_rate_entry.standard_id')
       	                   ->leftjoin('unit_of_measurement','unit_of_measurement.id','=','supplier_rate_entry.unit_id')
       	                   ->join('currencies','currencies.id','=','supplier.currency_type_id') 
       	                    ->where('supplier_rate_entry.product_id','=',$pdtSea->product_id)
       	                    ->where('supplier_rate_entry.unit_id','=',$unit_id)
                            ->where('supplier.delivery_type_id','=',$common->id)
                            ->where('supplier_rate_entry.is_active','=','Y');
                          
      if($pdtSea->grade_id != NULL){
        $data = $data->where('supplier_rate_entry.grade_id','=',$pdtSea->grade_id);
      } 
        $data = $data->selectRaw('supplier.name,supplier_rate_entry.price,standard_master.standard,grade_master.grade,unit_of_measurement.unit,currencies.currency')->get();                       
           	                    
        return $data; 
    }
    
    //delete pr sea product
    public function deletePrSea(Request $request)
     {
       	PRproductSea::where('id','=',$request->get('id'))->delete();
       	PRproductScheduleSea::where('pr_products_sea_id','=',$request->get('id'))->delete();

     }

    public function prMrListSea($id)
    {
      	$pr_prd = PRproductSea::find($id);
      	$pr = PurchaseRequestSea::find($pr_prd->purchase_request_sea_id);

      	$mrlist = PrMrSea::join('material_requisition_sea','material_requisition_sea.id','=','pr_mr_sea.mr_sea_id')
      						->join('customers','customers.id','=','material_requisition_sea.customer_id')
      						->join('mr_sea_approval_products','mr_sea_approval_products.material_requisition_sea_id','=','pr_mr_sea.mr_sea_id')
      						->where('mr_sea_approval_products.product_id','=',$pr_prd->product_id)
      						->where('mr_sea_approval_products.unit_id','=',$pr_prd->unit_id)
      						->where('mr_sea_approval_products.grade_id','=',$pr_prd->grade_id)
      						->where('pr_mr_sea.pr_sea_id','=',$pr_prd->purchase_request_sea_id)
      						->selectRaw('material_requisition_sea.request_no,material_requisition_sea.request_date,material_requisition_sea.approved_date,customers.name,sum(mr_sea_approval_products.qty) AS qty')
      						->groupBy('material_requisition_sea.request_no','material_requisition_sea.request_date','material_requisition_sea.approved_date','customers.name')
      						->get();
      	return view('purchase_request_sea/pr_mr_list',compact('mrlist','pr','pr_prd'));
    }

    public function generatePr($id)
    {
       $purchase_sea = PurchaseRequestSea::find($id);
       $pdtSeaSchd = PRproductScheduleSea::where('purchase_request_sea_id','=',$id)->where('supplier_id','=',NULL)->count();
        if($pdtSeaSchd == 0){
        	$purchase_sea->status = 'PR';
        	$purchase_sea->save();
        }

       $prPdts = PRproductSea::where('purchase_request_sea_id','=',$id)->get();
       
       foreach($prPdts as $pdt){
      	$pdtSchd = PRproductScheduleSea::where('pr_products_sea_id','=',$pdt->id)->where('supplier_id','=',NULL)->count();
        PRsupplierSea::where('purchase_request_sea_id','=',$id)
                     ->update(['status' => 'PR' ]); 
        PRproductScheduleSea::where('pr_products_sea_id','=',$pdt->id)
                            ->where('supplier_id','!=',NULL)
                            ->where('status','!=','PO')
                            ->update(['status' => 'PR' ]); 
        $pr_prd_scd_Ids = PRproductScheduleSea::where('pr_products_sea_id','=',$pdt->id)->where('status','=','PR')->pluck('id')->toArray();
          PRSupplierProductSea::whereIn('pr_products_schedule_sea_id',$pr_prd_scd_Ids)->update(['status' => 'PR' ]);                                
          	if($pdtSchd == 0){
          	  $pdt->status = 'PR';
          	  $pdt->save();
          	}

       }

       return redirect('sea_purchase_request/index')->with('generate','true');
     

    }

    public function deleteSeaPurchase(Request $request)
    {
      	PRproductSea::where('purchase_request_sea_id','=',$request->get('option'))
                    ->delete();
      	PRproductScheduleSea::where('purchase_request_sea_id','=',$request->get('option'))->delete();
      	PurchaseRequestSea::where('id','=',$request->get('option'))->delete();
      	PrMrSea::where('pr_sea_id','=',$request->get('option'))->delete();
      	PRsupplierSea::where('purchase_request_sea_id','=',$request->get('option'))->delete();
    }




}