000

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\LocalUserMapping;
use Auth;
use DB;
use App\Exports\GrnExport;
use App\Exports\MrnExport;
use App\Exports\StocksummeryExport;
use App\Exports\StockviewExport;
use App\Exports\SpareConsumptionExport;
use App\Exports\StnExport;
use App\Exports\StrExport;
use App\Exports\ConsumptionReportOmcMrExport;
use App\Exports\SquareUpRoundOffReportExport;
use App\Exports\ConsumptionReportTrcExport;
use App\Exports\StngstExport;
use Maatwebsite\Excel\Facades\Excel;

class AdminController extends Controller
{
    public $data; 
    public function get_common(){
    	if(Auth::check()){
    		$user = Auth::user();
    		$this->data['userInfo'] = LocalUserMapping::where('LoginID',$user->loginId)->first();
    	}
    }
    public function getCircle($loginId){
        $user = \DB::table('users')->where('loginId',$loginId)->first();
        $warehouses = '';
        // if($user->role == 5){
        //     // Regional Manager

// $warehouses = DB::table('local_user_mapping')
        //  ->join('ware_houses','ware_house',\DB::raw("CONCAT('SER',local_user_mapping.fieldUserCodes)"))
        //  ->select('local_user_mapping.fieldUserCodes','ware_houses.*')
        //  ->where('regionalCircleCodes', $loginId)
        //  ->groupBy('local_user_mapping.fieldUserCodes')
        //  ->get();

// }elseif($user->role == 4){
        //     // Business Head
        //     $warehouses = \App\WareHouse::get();
            
        // }elseif($user->role == 17 || $user->role == 18){
        //     // national
        //      $warehouses = \App\WareHouse::get();

// }elseif($user->role == 6){
        //     // Service Manager
        // $warehouses = DB::table('local_user_mapping')
        //  ->join('ware_houses','ware_house',\DB::raw("CONCAT('SER',local_user_mapping.fieldUserCodes)"))
        //  ->select('local_user_mapping.fieldUserCodes','ware_houses.*')
        //  ->where('serviceManagerCircleCodes', $loginId)
        //  ->groupBy('fieldUserCodes')
        //  ->get();

// }

$assigned_warehouses = DB::table('local_user_mapping')
             ->select('local_user_mapping.warehouseCode')
             ->where('LoginID', $loginId)                
             ->first();
        $warehouses = explode(',', $assigned_warehouses->warehouseCode);                        
        // dd($warehouses);

$warehouses = \App\WareHouse::whereIn('ware_house', $warehouses)->get();
            
        return $warehouses;
    }

public function grn_reports(Request $request){

$this->get_common();
    $loginId = Auth::user()->loginId;
    $warehouses = $this->getCircle($loginId);
    $warehousesin = $warehouses->pluck('ware_house');
    // print_r($warehousesin);
    // dd($warehousesin);
    // exit();
  
    $current_warehouse = $request->warehouse;   
    if($request->fromdate != ""){
        $fromdate= $request->fromdate;
    }else{
        $fromdate= "";
    }

if($request->todate != ""){
        $todate= $request->todate;
    }else{
        $todate= "";
    }

$grns = array();

if($request->action_type == "search"){

$grns = DB::table('goods_receipt_items')->join('goods_receipt_notes','goods_receipt_notes.id','goods_receipt_items.grn_id')->select('goods_receipt_items.*','goods_receipt_notes.grn_no','goods_receipt_notes.reference_number','goods_receipt_notes.supplier_name','goods_receipt_notes.invoice_number','goods_receipt_notes.invoice_date','goods_receipt_notes.freightCharges','goods_receipt_notes.warehouse','goods_receipt_notes.created_at as grn_date','goods_receipt_notes.transporter_details', 'goods_receipt_notes.eway_bill_no',);
  
        if($current_warehouse != "" && $current_warehouse != "all"){
            $grns = $grns->where('goods_receipt_notes.warehouse',$current_warehouse);
        }
        if($current_warehouse != "" && $current_warehouse == "all"){
            $grns = $grns->whereIn('goods_receipt_notes.warehouse',$warehousesin);
           
        }

if($request->fromdate != "" && $request->todate != ""){
            $grns = $grns->whereBetween(\DB::raw('date(goods_receipt_notes.created_at)'),[$fromdate,$todate]);
        }

$grns = $grns->where('type', 1)->orderBy('id','desc')->get();

}

if($request->action_type == "export"){
        // \DB::enableQueryLog();
        $grns = DB::table('goods_receipt_items')->join('goods_receipt_notes','goods_receipt_notes.id','goods_receipt_items.grn_id')->select('goods_receipt_items.*','goods_receipt_notes.grn_no','goods_receipt_notes.reference_number','goods_receipt_notes.supplier_name','goods_receipt_notes.invoice_number','goods_receipt_notes.invoice_date','goods_receipt_notes.freightCharges','goods_receipt_notes.warehouse','goods_receipt_notes.created_at as grn_date', 'goods_receipt_notes.transporter_details', 'goods_receipt_notes.eway_bill_no');

if($current_warehouse != "" && $current_warehouse != 'all'){
            $grns = $grns->where('goods_receipt_notes.warehouse',$current_warehouse);
        }
        if($current_warehouse != "" && $current_warehouse == "all"){
            $grns = $grns->whereIn('goods_receipt_notes.warehouse',$warehousesin);           
        }
      
        if($request->fromdate != "" && $request->todate != ""){
            $grns = $grns->whereBetween(\DB::raw('date(goods_receipt_notes.created_at)'),[$fromdate,$todate]);
        }

$grns = $grns->where('type', 1)->orderBy('id','desc')->get();
        // print_r($warehousesin->toArray());
        // dd(\DB::getQueryLog($grns));
        return Excel::download(new GrnExport($grns), 'grns-reports.xlsx');

}
     $this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'fromdate'=>$fromdate,'todate'=>$todate,'grns'=>$grns, 'grn_no'=>'']);

return view('admin.reports.grn-reports',$this->data);    
}

public function mrn_reports(Request $request){

if($request->todate != ""){
        $todate= $request->todate;
    }else{
        $todate= "";
    }

$all_suppliers = array();

$apiUrl = 'http://scm.aerialtelecom.in/api/get-po-supplier';
    $ch = curl_init($apiUrl);        
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);        
    $response = curl_exec($ch);        
    curl_close($ch);        
    $data = json_decode($response, true);        
    if ($data) {            
        $all_suppliers = $data['all_suppliers'];            
    } else {    
        echo "some errror occured.";        
    }

$grns = array();

if($request->action_type == "search"){

$grns = DB::table('goods_receipt_items')->join('goods_receipt_notes','goods_receipt_notes.id','goods_receipt_items.grn_id')->select('goods_receipt_items.*','goods_receipt_notes.grn_no','goods_receipt_notes.reference_number','goods_receipt_notes.supplier_name','goods_receipt_notes.invoice_number','goods_receipt_notes.invoice_date','goods_receipt_notes.freightCharges','goods_receipt_notes.warehouse','goods_receipt_notes.created_at as grn_date', 'goods_receipt_notes.transporter_details', 'goods_receipt_notes.eway_bill_no');
  
        if($current_warehouse != "" && $current_warehouse != "all"){
            $grns = $grns->where('goods_receipt_notes.warehouse',$current_warehouse);
        }
        if($current_warehouse != "" && $current_warehouse == "all"){
            $grns = $grns->whereIn('goods_receipt_notes.warehouse',$warehousesin);
           
        }

if($request->fromdate != "" && $request->todate != ""){
            $grns = $grns->whereBetween(\DB::raw('date(goods_receipt_notes.created_at)'),[$fromdate,$todate]);
        }

$grns = $grns->where('type', 2)->orderBy('id','desc')->get();

}

if($request->action_type == "export"){
        
        $grns = DB::table('goods_receipt_items')->join('goods_receipt_notes','goods_receipt_notes.id','goods_receipt_items.grn_id')->select('goods_receipt_items.*','goods_receipt_notes.grn_no','goods_receipt_notes.reference_number','goods_receipt_notes.supplier_name','goods_receipt_notes.invoice_number','goods_receipt_notes.invoice_date','goods_receipt_notes.freightCharges','goods_receipt_notes.warehouse','goods_receipt_notes.created_at as grn_date', 'goods_receipt_notes.transporter_details', 'goods_receipt_notes.eway_bill_no');

if($current_warehouse != ""  && $current_warehouse != "all"){
            $grns = $grns->where('goods_receipt_notes.warehouse',$current_warehouse);
        }

if($current_warehouse != "" && $current_warehouse == "all"){
            $grns = $grns->whereIn('goods_receipt_notes.warehouse',$warehousesin);
           
        }

if($request->fromdate != "" && $request->todate != ""){
            $grns = $grns->whereBetween(\DB::raw('date(goods_receipt_notes.created_at)'),[$fromdate,$todate]);
        }

$grns = $grns->where('type', 2)->orderBy('id','desc')->get();

return Excel::download(new MrnExport($grns, $all_suppliers), 'mrns-reports.xlsx');

}
     $this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'fromdate'=>$fromdate,'todate'=>$todate,'grns'=>$grns, 'grn_no' => '', 'all_suppliers' => $all_suppliers]);

return view('admin.reports.mrn-reports',$this->data);    
}

public function stock_summery(Request $request){
    	$this->get_common();
        $loginId = Auth::user()->loginId;
        $warehouses = $this->getCircle($loginId);
        $warehousesin = $warehouses->pluck('ware_house');

// dd($warehouses);
        
        $current_warehouse = $request->warehouse; 
     
        $stocks = array();
        if($request->warehouse != "" && $request->action_type != ""){

$n = 0;
            $main_stocks = DB::table('warehouse_inventory')->join('spare_parts_masters','spare_parts_masters.code','warehouse_inventory.spare_code')
            ->select('warehouse_inventory.warehouse','warehouse_inventory.item_type', DB::raw('SUM(warehouse_inventory.available_qty) as qty'),DB::raw('SUM(warehouse_inventory.available_qty*warehouse_inventory.rate) as total_value'), 'warehouse_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.description','spare_parts_masters.hsn');
            if($current_warehouse != "" && $current_warehouse != "all"){
                $main_stocks = $main_stocks->where('warehouse_inventory.warehouse',$current_warehouse);
            }
            if($current_warehouse != "" && $current_warehouse == "all"){
                $main_stocks = $main_stocks->whereIn('warehouse_inventory.warehouse',$warehousesin);
            }
            $main_stocks = $main_stocks->where('warehouse_inventory.available_qty','>',0)
                 ->groupBy('warehouse_inventory.spare_code','warehouse_inventory.item_type')
                 ->get();

foreach ($main_stocks as  $value) {
                $value->zone =  $value->item_type;
               $stocks[$n] = $value;
               $n++;
            }

$pt_stocks = DB::table('pt_inventory')->join('spare_parts_masters','spare_parts_masters.code','pt_inventory.spare_code')
            ->select('pt_inventory.warehouse','pt_inventory.item_type', DB::raw('SUM(pt_inventory.available_qty) as qty'),DB::raw('SUM(pt_inventory.available_qty*pt_inventory.rate) as total_value'), 'pt_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.description','spare_parts_masters.hsn');
                //  if($current_warehouse != "all"){
                //     $pt_stocks = $pt_stocks -> where('pt_inventory.warehouse',$current_warehouse);
                //  }

if($current_warehouse != "" && $current_warehouse != "all"){
                    $pt_stocks = $pt_stocks->where('pt_inventory.warehouse',$current_warehouse);
                }
                if($current_warehouse != "" && $current_warehouse == "all"){
                    $pt_stocks = $pt_stocks->whereIn('pt_inventory.warehouse',$warehousesin);
                }
                 $pt_stocks = $pt_stocks ->where('pt_inventory.available_qty','>',0)
                 ->groupBy('pt_inventory.spare_code','pt_inventory.item_type')
                 ->get();

foreach ($pt_stocks as  $value1) {
                $value1->zone = 'PT';
               $stocks[$n] = $value1;
               $n++;
            }

$trc_stocks = DB::table('trc_inventory')->join('spare_parts_masters','spare_parts_masters.code','trc_inventory.spare_code')
            ->select('trc_inventory.warehouse','trc_inventory.item_type', DB::raw('SUM(trc_inventory.available_qty) as qty'),DB::raw('SUM(trc_inventory.available_qty*trc_inventory.rate) as total_value'), 'trc_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.description','spare_parts_masters.hsn');
                 if($current_warehouse != "" && $current_warehouse != "all"){
                    $trc_stocks =$trc_stocks ->where('trc_inventory.warehouse',$current_warehouse);
                 }

if($current_warehouse != "" && $current_warehouse == "all"){
                    $trc_stocks = $trc_stocks->whereIn('trc_inventory.warehouse',$warehousesin);
                }
                 $trc_stocks =$trc_stocks->where('trc_inventory.available_qty','>',0)
                 ->groupBy('trc_inventory.spare_code','trc_inventory.item_type')
                 ->get();

foreach ($trc_stocks as  $value2) {
               
               $value2->zone = 'TRC';
               $stocks[$n] = $value2;
               $n++;
            }

$eng_stocks = DB::table('engineer_inventory')->join('spare_parts_masters','spare_parts_masters.code','engineer_inventory.spare_code')->join('local_user_mapping','local_user_mapping.LoginID','engineer_inventory.engineer_id')
            ->select('engineer_inventory.warehouse','engineer_inventory.engineer_id','engineer_inventory.item_type', DB::raw('SUM(engineer_inventory.available_qty) as qty'),DB::raw('SUM(engineer_inventory.available_qty*engineer_inventory.rate) as total_value'), 'engineer_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.description','local_user_mapping.StaffName','spare_parts_masters.hsn');
                 if($current_warehouse != "" && $current_warehouse != "all"){
                    $eng_stocks =$eng_stocks ->where('engineer_inventory.warehouse',$current_warehouse);
                 }
                 if($current_warehouse != "" && $current_warehouse == "all"){
                    $eng_stocks = $eng_stocks->whereIn('engineer_inventory.warehouse',$warehousesin);
                }
                 $eng_stocks =$eng_stocks->where('engineer_inventory.available_qty','>',0)
                 ->groupBy('engineer_inventory.engineer_id','engineer_inventory.spare_code','engineer_inventory.item_type')
                 ->get();

foreach ($eng_stocks as  $value3) {
               
               $value3->zone = 'ENG';
               $stocks[$n] = $value3;
               $n++;
            }

$site_stocks = DB::table('engineer_inventory')->join('spare_parts_masters','spare_parts_masters.code','engineer_inventory.spare_code')->join('site_master','site_master.Site_id','engineer_inventory.engineer_id')
            ->select('engineer_inventory.warehouse','engineer_inventory.engineer_id','engineer_inventory.item_type', DB::raw('SUM(engineer_inventory.available_qty) as qty'),DB::raw('SUM(engineer_inventory.available_qty*engineer_inventory.rate) as total_value'), 'engineer_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.description','site_master.Site_Name as StaffName','spare_parts_masters.hsn');
                 if($current_warehouse != "" && $current_warehouse != "all"){
                    $site_stocks =$site_stocks ->where('engineer_inventory.warehouse',$current_warehouse);
                 }
                 if($current_warehouse != "" && $current_warehouse == "all"){
                    $site_stocks = $site_stocks->whereIn('engineer_inventory.warehouse',$warehousesin);
                }
                 $site_stocks =$site_stocks->where('engineer_inventory.available_qty','>',0)
                 ->groupBy('engineer_inventory.engineer_id','engineer_inventory.spare_code','engineer_inventory.item_type')
                 ->get();
            // dd($site_stocks);

foreach ($site_stocks as  $value4) {
               
               $value4->zone = 'SITE';
               $stocks[$n] = $value4;
               $n++;
            }

if($current_warehouse == "Scrap Zone"){
                $stocks =array();
                $cir_stocks = DB::table('stock_in_circle_stores')->join('spare_parts_masters','spare_parts_masters.code','stock_in_circle_stores.spare_code')->join('warehouse_inventory','warehouse_inventory.spare_code','stock_in_circle_stores.spare_code')
                 ->select('stock_in_circle_stores.warehouse','stock_in_circle_stores.faulty_qty','stock_in_circle_stores.rate as total_value','warehouse_inventory.item_type',DB::raw('SUM(warehouse_inventory.available_qty*warehouse_inventory.rate) as total_value'),'warehouse_inventory.scrap_bin', DB::raw('SUM(warehouse_inventory.available_qty) as qty'), 'stock_in_circle_stores.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.description','spare_parts_masters.hsn')
                 ->where('stock_in_circle_stores.warehouse','=',$current_warehouse)
                 ->where('warehouse_inventory.available_qty','>',0)
                 ->where('warehouse_inventory.warehouse','=',$current_warehouse)
                 ->groupBy('warehouse_inventory.spare_code','warehouse_inventory.item_type')
                 ->get();
    
                 
                foreach ($cir_stocks as  $value5) {
                    $value5->zone =  $value5->item_type;
                   $stocks[$n] = $value5;
                   $n++;
                }
            }

if($request->action_type == "export"){

return Excel::download(new StocksummeryExport($stocks), 'stock-summery-report.xlsx');

}

// dd($stocks);

}

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'stocks'=>$stocks]);
    		return view('admin.reports.stock-summery',$this->data);
    }

public function view_stock(Request $request){
    	$this->get_common();
        $loginId = Auth::user()->loginId;
        $warehouses = $this->getCircle($loginId);
        $warehousesin = $warehouses->pluck('ware_house'); 
        $current_warehouse = $request->warehouse;
        $stocks = array();
        if($request->warehouse != "" && $request->action_type != ""){

$n = 0;
            $main_stocks = DB::table('warehouse_inventory')
                ->join('spare_parts_masters', 'spare_parts_masters.code', 'warehouse_inventory.spare_code')
                ->leftJoin('goods_receipt_items', function ($join) {
                    $join->on('warehouse_inventory.spare_code', '=', 'goods_receipt_items.item_code')
                         ->where('goods_receipt_items.created_at', '=', DB::raw('(select max(created_at) from goods_receipt_items where goods_receipt_items.item_code = warehouse_inventory.spare_code)'));
                })
                ->select(
                    'warehouse_inventory.warehouse',
                    'warehouse_inventory.item_type',
                    'warehouse_inventory.entry_type as transaction_type',
                    'warehouse_inventory.available_qty as qty',
                    'warehouse_inventory.spare_code',
                    'spare_parts_masters.uom',
                    'spare_parts_masters.type',
                    'spare_parts_masters.category',
                    'spare_parts_masters.product_type',
                    'spare_parts_masters.description',
                    DB::raw('IF(goods_receipt_items.created_at IS NOT NULL, goods_receipt_items.created_at, warehouse_inventory.created_at) as created_at'),
                    'spare_parts_masters.updated_at',
                    'warehouse_inventory.lot_no',
                    'warehouse_inventory.rate',
                    'warehouse_inventory.updated_at',   
                    'spare_parts_masters.hsn'
                );
            
            if ($current_warehouse != "" && $current_warehouse != "all") {
                $main_stocks = $main_stocks->where('warehouse_inventory.warehouse', $current_warehouse);
            }
            
            if ($current_warehouse != "" && $current_warehouse == "all") {
                $main_stocks = $main_stocks->whereIn('warehouse_inventory.warehouse', $warehousesin);
            }
            
            $main_stocks = $main_stocks->where('warehouse_inventory.available_qty', '>', 0)->get();
            
            foreach ($main_stocks as $value) {
                $value->zone = $value->item_type;
                $stocks[$n] = $value;
                $n++;
            }

$pt_stocks = DB::table('pt_inventory')->join('spare_parts_masters','spare_parts_masters.code','pt_inventory.spare_code')
            ->select('pt_inventory.warehouse','pt_inventory.entry_type as transaction_type','pt_inventory.item_type','pt_inventory.available_qty as qty','pt_inventory.updated_at','pt_inventory.spare_code','pt_inventory.entry_type as transaction_type', 'pt_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.updated_at','spare_parts_masters.category','spare_parts_masters.updated_at','pt_inventory.created_at','spare_parts_masters.description','spare_parts_masters.product_type','pt_inventory.lot_no','pt_inventory.rate','spare_parts_masters.hsn');
                 if($current_warehouse != "" && $current_warehouse != "all"){
                    $pt_stocks =$pt_stocks->where('pt_inventory.warehouse',$current_warehouse);
                 }

if($current_warehouse != "" && $current_warehouse == "all"){
                    $pt_stocks =$pt_stocks->whereIn('pt_inventory.warehouse',$warehousesin);
                }
                 $pt_stocks =$pt_stocks->where('pt_inventory.available_qty','>',0)

->get();

foreach ($pt_stocks as  $value1) {
                $value1->zone = 'PT';
               $stocks[$n] = $value1;
               $n++;
            }

$trc_stocks = DB::table('trc_inventory')->join('spare_parts_masters','spare_parts_masters.code','trc_inventory.spare_code')
             ->select('trc_inventory.warehouse','trc_inventory.entry_type as transaction_type','trc_inventory.item_type','trc_inventory.updated_at','trc_inventory.created_at', 'trc_inventory.available_qty as qty','trc_inventory.entry_type as transaction_type','trc_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.updated_at','spare_parts_masters.category','spare_parts_masters.product_type','spare_parts_masters.description','trc_inventory.lot_no','trc_inventory.rate','spare_parts_masters.hsn');
                 if($current_warehouse != "" && $current_warehouse != "all"){
                    $trc_stocks =$trc_stocks ->where('trc_inventory.warehouse',$current_warehouse);
                 }
                 if($current_warehouse != "" && $current_warehouse == "all"){
                    $trc_stocks =$trc_stocks->whereIn('trc_inventory.warehouse',$warehousesin);
                }
                 $trc_stocks =$trc_stocks->where('trc_inventory.available_qty','>',0)

->get();

foreach ($trc_stocks as  $value2) {
               
               $value2->zone = 'TRC';
               $stocks[$n] = $value2;
               $n++;
            }

$eng_stocks = DB::table('engineer_inventory')->join('spare_parts_masters','spare_parts_masters.code','engineer_inventory.spare_code')->join('local_user_mapping','local_user_mapping.LoginID','engineer_inventory.engineer_id')
            ->select('engineer_inventory.warehouse','engineer_inventory.updated_at','engineer_inventory.created_at','engineer_inventory.engineer_id','engineer_inventory.entry_type as transaction_type','engineer_inventory.item_type','engineer_inventory.available_qty as qty', 'engineer_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.product_type','spare_parts_masters.type','spare_parts_masters.description','spare_parts_masters.updated_at','spare_parts_masters.category','spare_parts_masters.product_type','local_user_mapping.StaffName','engineer_inventory.lot_no','engineer_inventory.rate','spare_parts_masters.hsn');
                 if($current_warehouse != "" && $current_warehouse != "all"){
                    $eng_stocks =$eng_stocks ->where('engineer_inventory.warehouse',$current_warehouse);
                 }
                 if($current_warehouse != "" && $current_warehouse == "all"){
                    $eng_stocks =$eng_stocks->whereIn('engineer_inventory.warehouse',$warehousesin);
                }
                 $eng_stocks =$eng_stocks->where('engineer_inventory.available_qty','>',0)
                 ->get();

foreach ($eng_stocks as  $value3) {
               
               $value3->zone = 'ENG';
               $stocks[$n] = $value3;
               $n++;
            }

$site_stocks = DB::table('engineer_inventory')->join('spare_parts_masters','spare_parts_masters.code','engineer_inventory.spare_code')->join('site_master', function($join){
                $join->on('site_master.Site_id', 'engineer_inventory.engineer_id')->on('site_master.product', 'engineer_inventory.site_product');
            })->select('engineer_inventory.warehouse','engineer_inventory.engineer_id','engineer_inventory.item_type','engineer_inventory.available_qty as qty', 'engineer_inventory.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.description','spare_parts_masters.product_type','spare_parts_masters.category','engineer_inventory.entry_type as transaction_type','spare_parts_masters.created_at','engineer_inventory.created_at','site_master.product','engineer_inventory.lot_no','engineer_inventory.rate','spare_parts_masters.hsn');
            if($current_warehouse != "" && $current_warehouse != "all"){
                $site_stocks =$site_stocks ->where('engineer_inventory.warehouse',$current_warehouse);
            }
            if($current_warehouse != "" && $current_warehouse == "all"){
                $site_stocks =$site_stocks->whereIn('engineer_inventory.warehouse',$warehousesin);
            }
            $site_stocks =$site_stocks->where('engineer_inventory.available_qty','>',0)->get();

foreach ($site_stocks as  $value4) {
               
               $value4->zone = 'SITE';
               $stocks[$n] = $value4;
               $n++;
            }

if($current_warehouse == "Scrap Zone"){
                $stocks =array();
                $cir_stocks = DB::table('stock_in_circle_stores')->join('spare_parts_masters','spare_parts_masters.code','stock_in_circle_stores.spare_code')->join('warehouse_inventory','warehouse_inventory.spare_code','stock_in_circle_stores.spare_code')
                 ->select('stock_in_circle_stores.warehouse','stock_in_circle_stores.faulty_qty','stock_in_circle_stores.rate as total_value','warehouse_inventory.item_type',DB::raw('SUM(warehouse_inventory.available_qty*warehouse_inventory.rate) as total_value'),'warehouse_inventory.scrap_bin','warehouse_inventory.rate','warehouse_inventory.entry_type as transaction_type','warehouse_inventory.updated_at','warehouse_inventory.created_at','warehouse_inventory.entry_type as transaction_type','warehouse_inventory.lot_no', DB::raw('SUM(warehouse_inventory.available_qty) as qty'), 'stock_in_circle_stores.spare_code','spare_parts_masters.uom','spare_parts_masters.type','spare_parts_masters.category','spare_parts_masters.product_type','spare_parts_masters.description','spare_parts_masters.updated_at','spare_parts_masters.hsn')
                 ->where('stock_in_circle_stores.warehouse','=',$current_warehouse)
                 ->where('warehouse_inventory.available_qty','>',0)
                 ->where('warehouse_inventory.warehouse','=',$current_warehouse)
                 ->groupBy('warehouse_inventory.spare_code','warehouse_inventory.item_type')
                 ->get();
                 
                //  dd($cir_stocks);
                 
                foreach ($cir_stocks as  $value4) {
                    $value4->zone =  $value4->item_type;
                   $stocks[$n] = $value4;
                   $n++;
                }
            }

if($request->action_type == "export"){

return Excel::download(new StockviewExport($stocks), 'stock-view-report.xlsx');

}

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'stocks'=>$stocks]);
    		return view('admin.reports.view-stock',$this->data);
    }

// public function spare_consumption_report(Request  $request){
    //     $this->get_common();
    //     $loginId = Auth::user()->loginId;
        
    //     $warehouses = $this->getCircle($loginId);
    //     $warehousesin = $warehouses->pluck('ware_house');
        
    //     $current_warehouse = $request->warehouse;

//     if($request->fromdate != ""){
    //         // $fromdate = date('Y-m-d', strtotime($request->fromdate. ' - 1 days'));
    //         $fromdate= $request->fromdate;
    //     }else{
    //         $fromdate= "";
    //     }

//     if($request->todate != ""){
    //         $todate= $request->todate;
    //         // $todate = date('Y-m-d', strtotime($request->todate. ' + 1 days'));
    //     }else{
    //         $todate= "";
    //     }
    //     $stocks = array();
    //     if($request->warehouse != "" && $request->action_type != ""){

//         //engineer sapre consumption 
    //         $eng_stocks_consumption = DB::table('consumption_spares')->join('fsr_detail','fsr_detail.Call_Log_No','consumption_spares.call_log_no')->join('call_log','call_log.Call_Log_No','consumption_spares.call_log_no')->join('local_user_mapping','local_user_mapping.LoginID','consumption_spares.engineer_id')->join('spare_parts_masters','spare_parts_masters.code','consumption_spares.spare_code')->where('call_log.job_process_status','Complete');
    //          if($current_warehouse != "" && $current_warehouse != "all"){
    //                 $eng_stocks_consumption = $eng_stocks_consumption->where('consumption_spares.warehouse',$current_warehouse);
    //         }
    //         if($current_warehouse != "" && $current_warehouse == "all"){
    //             $eng_stocks_consumption = $eng_stocks_consumption->whereIn('consumption_spares.warehouse',$warehousesin);
    //         }

//         if($request->fromdate != "" && $request->todate != ""){
    //             $eng_stocks_consumption = $eng_stocks_consumption->whereBetween(\DB::raw('date(fsr_detail.fsr_fill_date)'),[$fromdate,$todate]);                            
    //         }
    //         // elseif ($request->fromdate != "" && $request->todate != "" && $current_warehouse != ""){
    //         //     $eng_stocks_consumption = $eng_stocks_consumption->select('consumption_spares.*','fsr_detail.job_process_status','local_user_mapping.StaffName','call_log.Customer_Name','call_log.Site_ID','call_log.Circle_Code','call_log.product','call_log.Call_Type_Code','spare_parts_masters.description','spare_parts_masters.type as spare_type','fsr_detail.fsr_fill_date')->get();
    //         // }
    //         // else{
    //         //     $eng_stocks_consumption=[];
    //         // }
    //         $eng_stocks_consumption = $eng_stocks_consumption->select('consumption_spares.*','fsr_detail.job_process_status','local_user_mapping.StaffName','call_log.Customer_Name','call_log.Site_ID','call_log.Circle_Code','call_log.product','call_log.Call_Type_Code','spare_parts_masters.description','spare_parts_masters.type as spare_type','fsr_detail.fsr_fill_date')->get();

//         $stocks = array();
    //         $n = 0;
    //         foreach ($eng_stocks_consumption as  $value) {           
    //             $value->zone = 'ENG';
    //             $stocks[$n] = $value;
    //             $n++;
    //         }

//         //site spare consumption
    //         // $site_stock_consumption = DB::table('consumption_spares')->join('fsr_detail','fsr_detail.Call_Log_No','consumption_spares.call_log_no')->join('call_log','call_log.Call_Log_No','consumption_spares.call_log_no')->join('local_user_mapping','local_user_mapping.LoginID','consumption_spares.engineer_id')->join('spare_parts_masters','spare_parts_masters.code','consumption_spares.spare_code')->where('call_log.job_process_status','Complete');
    //         //  if($current_warehouse != "" && $current_warehouse != "all"){
    //         //         $site_stock_consumption = $site_stock_consumption->where('consumption_spares.warehouse',$current_warehouse);
    //         // }
    //         // if($current_warehouse != "" && $current_warehouse == "all"){
    //         //     $site_stock_consumption = $site_stock_consumption->whereIn('consumption_spares.warehouse',$warehousesin);
    //         // }

//         // if($request->fromdate != "" && $request->todate != ""){
    //         //     $site_stock_consumption = $site_stock_consumption->whereBetween('fsr_detail.fsr_fill_date',[$fromdate,$todate]);

//         // }

//         // $site_stock_consumption = $site_stock_consumption->select('consumption_spares.*','fsr_detail.job_process_status','local_user_mapping.StaffName','call_log.Customer_Name','call_log.Site_ID','call_log.Circle_Code','call_log.product','call_log.Call_Type_Code','spare_parts_masters.description','spare_parts_masters.type as spare_type','fsr_detail.fsr_fill_date')->get();      
            
    //         // foreach ($site_stock_consumption as  $value1) {           
    //         //     $value1->zone = 'SITE';
    //         //     $stocks[$n] = $value1;
    //         //     $n++;
    //         // }

//         $fromdate = $request->fromdate;
    //         $todate = $request->todate;

//         if($request->action_type == "export"){

//             return Excel::download(new SpareConsumptionExport($stocks), 'spare-consumption-report.xlsx');

//         }

//     }

//     $this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'stocks'=>$stocks,'fromdate'=>$fromdate,'todate'=>$todate]);
    //         return view('admin.reports.spare-consumption-report',$this->data);
    // }

public function spare_consumption_report(Request  $request){
        $this->get_common();
        $loginId = Auth::user()->loginId;
        
        $warehouses = $this->getCircle($loginId);
        $warehousesin = $warehouses->pluck('ware_house');
        
        $current_warehouse = $request->warehouse;

if($request->fromdate != ""){
            // $fromdate = date('Y-m-d', strtotime($request->fromdate. ' - 1 days'));
            $fromdate= $request->fromdate;
        }else{
            $fromdate= "";
        }

if($request->todate != ""){
            $todate= $request->todate;
            // $todate = date('Y-m-d', strtotime($request->todate. ' + 1 days'));
        }else{
            $todate= "";
        }
        $stocks = array();
        if($request->action_type == 'search' || $request->action_type == 'export') {

//engineer sapre consumption 
            $eng_stocks_consumption = DB::table('consumption_spares')->join('fsr_detail','fsr_detail.Call_Log_No','consumption_spares.call_log_no')->join('call_log','call_log.Call_Log_No','consumption_spares.call_log_no')->join('local_user_mapping','local_user_mapping.LoginID','consumption_spares.engineer_id')->join('spare_parts_masters','spare_parts_masters.code','consumption_spares.spare_code')->where('call_log.job_process_status','Complete');
             if($current_warehouse != "" && $current_warehouse != "all"){
                $eng_stocks_consumption = $eng_stocks_consumption->where('consumption_spares.warehouse',$current_warehouse);
            }

if($request->fromdate != "" && $request->todate != ""){
                $eng_stocks_consumption = $eng_stocks_consumption->whereBetween(\DB::raw('date(fsr_detail.fsr_fill_date)'),[$fromdate,$todate]);                            
            }
            // elseif ($request->fromdate != "" && $request->todate != "" && $current_warehouse != ""){
            //     $eng_stocks_consumption = $eng_stocks_consumption->select('consumption_spares.*','fsr_detail.job_process_status','local_user_mapping.StaffName','call_log.Customer_Name','call_log.Site_ID','call_log.Circle_Code','call_log.product','call_log.Call_Type_Code','spare_parts_masters.description','spare_parts_masters.type as spare_type','fsr_detail.fsr_fill_date')->get();
            // }
            // else{
            //     $eng_stocks_consumption=[];
            // }
            $eng_stocks_consumption = $eng_stocks_consumption->select('consumption_spares.call_log_no','consumption_spares.warehouse','consumption_spares.spare_code','consumption_spares.type','consumption_spares.qty','consumption_spares.rate','consumption_spares.lot_no','consumption_spares.engineer_id as eng_site','fsr_detail.job_process_status','fsr_detail.mob_user_staff_code as engineer_id','local_user_mapping.StaffName','call_log.Customer_Name','call_log.Site_ID','call_log.Customer_ID','call_log.Circle_Code','call_log.product','call_log.SiteDescr as SiteName','call_log.Call_Type_Code','spare_parts_masters.description','spare_parts_masters.type as spare_type','fsr_detail.fsr_fill_date')->where('call_log.job_process_status','Complete')->where('fsr_detail.job_process_status','Complete')->where('local_user_mapping.StaffStatus','=','AC')->groupBy('consumption_spares.call_log_no', 'consumption_spares.spare_code', 'consumption_spares.type', 'consumption_spares.lot_no')->get();
            
            $n = 0;
            foreach ($eng_stocks_consumption as  $value) {           
                $value->zone = 'ENG';
                $stocks[$n] = $value;
                $n++;
            }

// spare consumed through site 
            $site_stocks_consumption = DB::table('consumption_spares')->join('fsr_detail','fsr_detail.Call_Log_No','consumption_spares.call_log_no')->join('call_log','call_log.Call_Log_No','consumption_spares.call_log_no')->join('site_master','site_master.Site_id','consumption_spares.engineer_id')->join('spare_parts_masters','spare_parts_masters.code','consumption_spares.spare_code');
            
            if($current_warehouse != ""){
                $site_stocks_consumption = $site_stocks_consumption->where('consumption_spares.warehouse',$current_warehouse);
            }

if($request->fromdate != "" && $request->todate != ""){
                $site_stocks_consumption = $site_stocks_consumption->whereBetween(\DB::raw('date(fsr_detail.fsr_fill_date)'),[$fromdate,$todate]);

}

$site_stocks_consumption = $site_stocks_consumption->select('consumption_spares.call_log_no','consumption_spares.warehouse','consumption_spares.spare_code','consumption_spares.type','consumption_spares.qty','consumption_spares.rate','consumption_spares.lot_no','consumption_spares.engineer_id as eng_site','fsr_detail.job_process_status','site_master.eng_name as StaffName','site_master.Site_Name as SiteName','call_log.Customer_Name','fsr_detail.mob_user_staff_code as engineer_id','call_log.Site_ID','call_log.Customer_ID','call_log.Circle_Code','call_log.product','call_log.Call_Type_Code','spare_parts_masters.description','spare_parts_masters.type as spare_type','fsr_detail.fsr_fill_date')->where('call_log.job_process_status','Complete')->where('fsr_detail.job_process_status','Complete')->groupBy('consumption_spares.call_log_no', 'consumption_spares.spare_code', 'consumption_spares.type', 'consumption_spares.lot_no')->get();
                    
            foreach ($site_stocks_consumption as  $value1) {           
                $value1->zone = 'SITE';
                $stocks[$n] = $value1;
                $n++;
            }

if($request->action_type == "export"){

return Excel::download(new SpareConsumptionExport($stocks), 'spare-consumption-report.xlsx');

}
        }

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'stocks'=>$stocks,'fromdate'=>$fromdate,'todate'=>$todate]);
            return view('admin.reports.spare-consumption-report',$this->data);
    }

public function stn_excel_report(Request $request){
        $this->get_common();
        $loginId = Auth::user()->loginId;
        $warehouses = $this->getCircle($loginId);
        $warehousesin = $warehouses->pluck('ware_house');
        $current_warehouse = $request->warehouse;

if($request->fromdate != ""){
            $fromdate= $request->fromdate;
        }else{
            $fromdate= "";
        }

if($request->todate != ""){
            $todate= $request->todate;
        }else{
            $todate= "";
        }

$stns = array();

if($request->action_type == "search"){

$stns = DB::table('stock_transfer_notes');
             if($current_warehouse != "" && $current_warehouse != "all"){
                $stns = $stns->where('stock_transfer_notes.from_warehouse',$current_warehouse);
            }
           
            if($current_warehouse != "" && $current_warehouse == "all"){
                $stns = $stns->whereIn('stock_transfer_notes.from_warehouse',$warehousesin);
            }
            if($request->fromdate != "" && $request->todate != ""){
                $stns = $stns->whereBetween(\DB::raw('date(stock_transfer_notes.stn_date)'),[$fromdate,$todate]);
            }

$stns = $stns->orderBy('id','desc')->get();

}

if($request->action_type == "export"){
            
            $stns = DB::table('stock_transfer_notes');

if($current_warehouse != "" && $current_warehouse != "all"){
                $stns = $stns->where('stock_transfer_notes.from_warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $stns = $stns->whereIn('stock_transfer_notes.from_warehouse',$warehousesin);
            }

if($request->fromdate != "" && $request->todate != ""){
                $stns = $stns->whereBetween(\DB::raw('date(stock_transfer_notes.stn_date)'),[$fromdate,$todate]);
            }

// $stns = $stns->orderBy('id','desc')->get();

$stn_items = $stns->join('stn_items','stock_transfer_notes.id','stn_items.stn_id')->join('spare_parts_masters','spare_parts_masters.code','stn_items.spare_code')->select('stn_items.*','stock_transfer_notes.id as stn_id','stock_transfer_notes.*','spare_parts_masters.description','spare_parts_masters.type','spare_parts_masters.hsn','spare_parts_masters.gst_slab_rates','spare_parts_masters.uom')->orderBy('stock_transfer_notes.id', 'desc')->get();

$data = array();
            $n = 0;

foreach($stn_items as $stn_item){
                $total_taxable_value = 0;
                $total_tax = 0;

$to_warehouse = \DB::table('ware_houses')->where('ware_house',$stn_item->to_warehouse)->first();

$from_warehouse = \DB::table('ware_houses')->where('ware_house',$stn_item->from_warehouse)->first();

$data[$n]['invoice_type'] = 'Tax Invoice';
                $data[$n]['invoice_no'] = $stn_item->invoice_no;
                $data[$n]['stn_no'] = $stn_item->stn_no;                
                $data[$n]['stn_date'] = $stn_item->stn_date;
                $data[$n]['spare_code'] = $stn_item->spare_code;
                $data[$n]['description'] = $stn_item->description;
                $data[$n]['hsn'] = $stn_item->hsn;
                $data[$n]['type'] = $stn_item->type;
                $data[$n]['gst_slab_rates'] = $stn_item->gst_slab_rates;
                $data[$n]['uom'] = $stn_item->uom;
                $data[$n]['to_warehouse'] = $stn_item->to_warehouse;
                $data[$n]['from_warehouse'] = $stn_item->from_warehouse;
                $data[$n]['buyer_gst'] = $to_warehouse->gst_no;
                $data[$n]['buyer_name'] = 'Aerial Telecom Solutions Pvt. Ltd.';
                $data[$n]['buyer_add'] = $to_warehouse->address;
                $data[$n]['buyer_add1'] = '';
                $data[$n]['buyer_location'] = $to_warehouse->address;
                $data[$n]['buyer_pincode'] = $to_warehouse->pincode;
                $data[$n]['circle'] =  $stn_item->to_warehouse;
                $data[$n]['fresh_qty'] = $stn_item->fresh_qty;
                $data[$n]['repaired_qty'] = $stn_item->repaired_qty;
                $data[$n]['faulty_qty'] = $stn_item->faulty_qty;
                $data[$n]['rate'] = $stn_item->rate;
                $qty = 0;
                if($stn_item->fresh_qty > 0 ){
                    $qty = $stn_item->fresh_qty;
                }

if($stn_item->repaired_qty > 0 ){
                    $qty = $stn_item->repaired_qty;
                }

if($stn_item->faulty_qty > 0 ){
                    $qty = $stn_item->faulty_qty;
                }

$gross_total = $qty*$stn_item->rate;

$data[$n]['gross_total'] = $gross_total;
                $data[$n]['qty'] = $qty;

$tax_amount = ($gross_total*$stn_item->gst_slab_rates)/100;

if($from_warehouse->label == $to_warehouse->label){
                    $data[$n]['cgst'] = $tax_amount/2;
                    $data[$n]['sgst'] = $tax_amount/2;
                    $data[$n]['igst'] = '';
                }else{
                    $data[$n]['cgst'] = '';
                    $data[$n]['sgst'] = '';
                    $data[$n]['igst'] = $tax_amount;
                }

$data[$n]['total_item'] = $tax_amount+$gross_total;
                $total_taxable_value = $total_taxable_value + $gross_total;
                $total_tax = $total_tax + $tax_amount;
                $data[$n]['total_taxable_value'] = $total_taxable_value;
                $data[$n]['total_tax'] = $total_tax;
                $data[$n]['stn_status'] = $stn_item->status;    
                $n++;
            }
            // $m = 0;
            // foreach($stn_items as $stn_item){
            //    $data[$m]['total_taxable_value'] = $total_taxable_value;
            //    $data[$m]['total_tax'] = $total_tax;
            //    $m++;
            // }

return Excel::download(new StnExport($data), 'stn-reports.xlsx');

}

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'fromdate'=>$fromdate,'todate'=>$todate,'stns'=>$stns]);

return view('admin.reports.stn-excel-report',$this->data);
    }

public function download_gst_format($stn_id){
        $stn = \DB::table('stock_transfer_notes')->where('id',$stn_id)->first();
        if($stn  != null ){
            $stn_items = \DB::table('stn_items')->join('stock_transfer_notes','stock_transfer_notes.id','stn_items.stn_id')->join('spare_parts_masters','spare_parts_masters.code','stn_items.spare_code')->select('stn_items.*','stock_transfer_notes.id as stn_id','stock_transfer_notes.*','spare_parts_masters.description','spare_parts_masters.hsn','spare_parts_masters.gst_slab_rates','spare_parts_masters.uom')->where('stn_items.stn_id',$stn_id)->get();
            $to_warehouse = \DB::table('ware_houses')->where('ware_house',$stn->to_warehouse)->first();
            $from_warehouse = \DB::table('ware_houses')->where('ware_house',$stn->from_warehouse)->first();
            $data = array();
            $n = 0;
            $total_taxable_value = 0;
            $total_tax = 0;

foreach($stn_items as $stn_item){
                $data[$n]['invoice_type'] = 'Tax Invoice';
                $data[$n]['invoice_no'] = $stn_item->invoice_no;
                $data[$n]['stn_date'] = $stn_item->stn_date;
                $data[$n]['spare_code'] = $stn_item->spare_code;
                $data[$n]['description'] = $stn_item->description;
                $data[$n]['hsn'] = $stn_item->hsn;
                $data[$n]['gst_slab_rates'] = $stn_item->gst_slab_rates;
                $data[$n]['uom'] = $stn_item->uom;
                
                $data[$n]['buyer_gst'] = $to_warehouse->gst_no;
                $data[$n]['buyer_name'] = 'Aerial Telecom Solutions Pvt. Ltd.';
                $data[$n]['buyer_add'] = $to_warehouse->address;
                $data[$n]['buyer_add1'] = '';
                $data[$n]['buyer_location'] = $to_warehouse->location;
                $data[$n]['buyer_pincode'] = $to_warehouse->pincode;
                $data[$n]['circle'] =  $stn->to_warehouse;
                $data[$n]['fresh_qty'] = $stn_item->fresh_qty;
                $data[$n]['repaired_qty'] = $stn_item->repaired_qty;
                $data[$n]['faulty_qty'] = $stn_item->faulty_qty;
                $data[$n]['rate'] = $stn_item->rate;
                $qty = 0;
                if($stn_item->fresh_qty > 0 ){
                    $qty = $stn_item->fresh_qty;
                }

if($stn_item->repaired_qty > 0 ){
                    $qty = $stn_item->repaired_qty;
                }

if($stn_item->faulty_qty > 0 ){
                    $qty = $stn_item->faulty_qty;
                }

$gross_total = $qty*$stn_item->rate;

$data[$n]['gross_total'] = $gross_total;
                $data[$n]['qty'] = $qty;

$tax_amount = ($gross_total*$stn_item->gst_slab_rates)/100;

$data[$n]['total_item'] = $tax_amount+$gross_total;
                $total_taxable_value = $total_taxable_value + $gross_total;
                $total_tax = $total_tax + $tax_amount;
                $n++;
            }
            $m = 0;
            foreach($stn_items as $stn_item){
               $data[$m]['total_taxable_value'] = $total_taxable_value;
               $data[$m]['total_tax'] = $total_tax;
               $m++;
            }

return Excel::download(new StngstExport($data), 'gst-formate.xlsx');

}else{
            return redirect('admin/stn-excel-report')->with('error','Invalid STN Id !!');
        }
    }

public function str_report(Request $request) {

$this->get_common();

$loginId = Auth::user()->loginId;
        $assigned_warehouses = DB::table('local_user_mapping')
             ->select('local_user_mapping.warehouseCode')
             ->where('LoginID', $loginId)           
             ->first();
        $warehouses = explode(',', $assigned_warehouses->warehouseCode);             
        if($request->warehouse != ""){
        	$current_warehouse = $request->warehouse;
        }else{
        	$current_warehouse = $warehouses[0];
        }

if($request->fromdate != ""){
        	$fromdate= $request->fromdate;
        }else{
        	$fromdate= "";
        }

if($request->todate != ""){
        	$todate= $request->todate;
        }else{
        	$todate= "";
        }

$strs = array();

if($request->action_type == "search"){

$strs = DB::table('stock_transfer_receipts as str')->join('stock_transfer_notes as stn','stn.stn_no','str.stn_no')->join('stn_items', 'stn_items.stn_id', 'stn.id')->join('spare_parts_masters as spm', 'spm.code', 'stn_items.spare_code')->select('str.warehouse','stn.from_warehouse', 'str.str_no','str.stn_no','stn.invoice_no', 'stn.stn_date','str.created_at', 'stn_items.spare_code', 'spm.description', 'spm.uom', 'spm.type', 'stn_items.rate', 'stn_items.fresh_qty', 'stn_items.repaired_qty', 'stn_items.faulty_qty');

if($request->fromdate != "" && $request->todate != ""){
	        	$strs = $strs->whereBetween(\DB::raw('date(str.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $strs = $strs->where('str.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $strs = $strs->whereIn('str.warehouse',$warehouses);
            }

$strs = $strs->orderBy('str.id','desc')->get();

}

if($request->action_type == "export"){
        	          
        	$strs = DB::table('stock_transfer_receipts as str')->join('stock_transfer_notes as stn','stn.stn_no','str.stn_no')->join('stn_items', 'stn_items.stn_id', 'stn.id')->join('spare_parts_masters as spm', 'spm.code', 'stn_items.spare_code')->select('str.warehouse','stn.from_warehouse', 'str.str_no','str.stn_no','stn.invoice_no', 'stn.stn_date','str.created_at', 'stn_items.spare_code', 'spm.description', 'spm.uom', 'spm.type','stn_items.rate', 'stn_items.fresh_qty', 'stn_items.repaired_qty', 'stn_items.faulty_qty');

if($request->fromdate != "" && $request->todate != ""){
	        	$strs = $strs->whereBetween(\DB::raw('date(str.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $strs = $strs->where('str.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $strs = $strs->whereIn('str.warehouse',$warehouses);
            }

$strs = $strs->orderBy('str.id','desc')->get();
           
        	return Excel::download(new StrExport($strs), 'strs-reports.xlsx');

}

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'fromdate'=>$fromdate,'todate'=>$todate,'strs'=>$strs]);
       
        return view('admin.reports.str-report', $this->data);
    }

public function consumption_report_mr_omc(Request $request) {
        $this->get_common();

// $warehouses = $this->getCircle($loginId);
        // $warehousesin = $warehouses->pluck('ware_house');

if($request->warehouse != ""){
        	$current_warehouse = $request->warehouse;
        }else{
        	$current_warehouse = $warehouses[0];
        }

if($request->fromdate != ""){
        	$fromdate= $request->fromdate;
        }else{
        	$fromdate= "";
        }

if($request->todate != ""){
        	$todate= $request->todate;
        }else{
        	$todate= "";
        }

$all_omc = array();

if($request->action_type == "search"){

$all_omc = \DB::table('omc_requests as omc')->join('omc_request_items as omc_item', 'omc_item.omc_id', 'omc.id')->join('spare_parts_masters as spm', 'spm.code', 'omc_item.spare_code')->select('omc.omc_type', 'omc.mr_no','omc.created_at', 'omc.po_no', 'omc.customer_code', 'omc.customer_name', 'omc.cutomer_po_date', 'omc.warehouse', 'omc.status','omc.invoice_no', 'omc.invoice_date', 'omc_item.spare_code', 'omc_item.fresh_qty', 'omc_item.repaired_qty', 'omc_item.rate', 'spm.description','omc.invoice_basic_value','omc.site_id','omc.site_name','omc.complaint_closure_date','omc.cust_open_po_status','omc.open_po_amount','omc.invoice_value');

// if($current_warehouse != ""){
        	// 	$all_omc = $all_omc->where('omc.warehouse',$current_warehouse);
        	// }

// if($request->fromdate != "" && $request->todate != ""){
	        // 	$all_omc = $all_omc->whereBetween('omc.created_at',[$fromdate,$todate]);
	        // }

if($request->fromdate != "" && $request->todate != ""){
	        	$all_omc = $all_omc->whereBetween(\DB::raw('date(omc.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $all_omc = $all_omc->where('omc.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $all_omc = $all_omc->whereIn('omc.warehouse',$warehouses);
            }

$all_omc = $all_omc->orderBy('omc.id','desc')->get();

}

if($request->action_type == "export"){
        	      
            // \DB::enableQueryLog();
        	$all_omc = \DB::table('omc_requests as omc')->join('omc_request_items as omc_item', 'omc_item.omc_id', 'omc.id')->join('spare_parts_masters as spm', 'spm.code', 'omc_item.spare_code')->select('omc.omc_type', 'omc.mr_no', 'omc.po_no','omc.created_at', 'omc.customer_code', 'omc.customer_name', 'omc.cutomer_po_date', 'omc.warehouse', 'omc.invoice_no','omc.status', 'omc.invoice_date', 'omc_item.spare_code', 'omc_item.fresh_qty', 'omc_item.repaired_qty', 'omc_item.rate', 'spm.description','omc.invoice_basic_value','omc.site_id','omc.site_name','omc.complaint_closure_date','omc.cust_open_po_status','omc.open_po_amount','omc.invoice_value');

if($request->fromdate != "" && $request->todate != ""){
	        	$all_omc = $all_omc->whereBetween(\DB::raw('date(omc.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $all_omc = $all_omc->where('omc.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $all_omc = $all_omc->whereIn('omc.warehouse',$warehouses);
            }
            
	        $all_omc = $all_omc->orderBy('omc.id','desc')->get();
           
            // dd(\DB::getQueryLog($all_omc));
        	return Excel::download(new ConsumptionReportOmcMrExport($all_omc), 'consumption-report-omc-mr.xlsx');
        }

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'fromdate'=>$fromdate,'todate'=>$todate,'all_omc_mr'=>$all_omc]);

return view('admin.reports.consumption-report-mr-omc', $this->data);
    }

public function consumption_report_trc(Request $request) {
        $this->get_common();

// $warehouses = $this->getCircle($loginId);
        // $warehousesin = $warehouses->pluck('ware_house');

if($request->warehouse != ""){
        	$current_warehouse = $request->warehouse;
        }else{
        	$current_warehouse = $warehouses[0];
        }

if($request->fromdate != ""){
        	$fromdate= $request->fromdate;
        }else{
        	$fromdate= "";
        }

if($request->todate != ""){
        	$todate= $request->todate;
        }else{
        	$todate= "";
        }

$all_trc = array();

if($request->action_type == "search"){

$all_trc = \DB::table('repaired_stocks')->leftjoin('used_components', 'used_components.repaired_spare_id', 'repaired_stocks.id')->join('spare_parts_masters as spm', 'spm.code', 'repaired_stocks.spare_code')->select('repaired_stocks.id','repaired_stocks.warehouse','spm.description','spm.uom','spm.type','spm.name','spm.category', 'repaired_stocks.spare_code','repaired_stocks.engineer_id as trc_engineer', 'repaired_stocks.qty', 'repaired_stocks.rate', 'used_components.engineer_id', 'used_components.spare_code as component_code', 'used_components.qty as component_qty','used_components.component_rate', 'repaired_stocks.created_at', 'repaired_stocks.is_component');

if($request->fromdate != "" && $request->todate != ""){
	        	$all_trc = $all_trc->whereBetween(\DB::raw('date(repaired_stocks.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $all_trc = $all_trc->where('repaired_stocks.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $all_trc = $all_trc->whereIn('repaired_stocks.warehouse',$warehouses);
            }

$all_trc = $all_trc->orderBy('repaired_stocks.id','desc')->get();           
    	}

if($request->action_type == "export"){        	      
            
        	$all_trc = \DB::table('repaired_stocks')->leftjoin('used_components', 'used_components.repaired_spare_id', 'repaired_stocks.id')->join('spare_parts_masters as spm', 'spm.code', 'repaired_stocks.spare_code')->select('repaired_stocks.id', 'repaired_stocks.warehouse','spm.description','spm.uom','spm.type','spm.name','spm.category', 'repaired_stocks.spare_code','repaired_stocks.engineer_id as trc_engineer', 'repaired_stocks.qty', 'repaired_stocks.rate', 'used_components.engineer_id', 'used_components.spare_code as component_code', 'used_components.qty as component_qty', 'used_components.component_rate','repaired_stocks.created_at', 'repaired_stocks.is_component');

if($request->fromdate != "" && $request->todate != ""){
	        	$all_trc = $all_trc->whereBetween(\DB::raw('date(repaired_stocks.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $all_trc = $all_trc->where('repaired_stocks.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $all_trc = $all_trc->whereIn('repaired_stocks.warehouse',$warehouses);
            }

$all_trc = $all_trc->orderBy('repaired_stocks.id','desc')->get();           
                       
        	return Excel::download(new ConsumptionReportTrcExport($all_trc), 'consumption-report-trc.xlsx');
        }

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'fromdate'=>$fromdate,'todate'=>$todate,'all_trc'=>$all_trc]);

return view('admin.reports.consumption-report-trc', $this->data);
    }

public function squareUpRoundOffReport(Request $request) {
        $this->get_common();

// $warehouses = $this->getCircle($loginId);
        // $warehousesin = $warehouses->pluck('ware_house');

if($request->warehouse != ""){
        	$current_warehouse = $request->warehouse;
        }else{
        	$current_warehouse = $warehouses[0];
        }

if($request->fromdate != ""){
        	$fromdate= $request->fromdate;
        }else{
        	$fromdate= "";
        }

if($request->todate != ""){
        	$todate= $request->todate;
        }else{
        	$todate= "";
        }

$finalArr = array();

if($request->action_type == "search"){

$all_users = \DB::table('users')->select('loginId', 'name')->get();

$usersArr = array();                
            if(!$all_users->isEmpty()) {
                foreach ($all_users as $key => $value) {
                    $usersArr[$value->loginId] = $value->name;
                }
            }

$all_squareUp = \DB::table('amc_requests as amc')->join('amc_request_items as amc_item', 'amc.id', 'amc_item.amc_id')->join('spare_parts_masters as spm', 'spm.code', 'amc_item.spare_code')->select('amc.mr_no','amc.warehouse','amc.request_by','amc.authorize_by','spm.description','spm.uom','spm.type','spm.name','spm.category', 'amc_item.spare_code','amc_item.fresh_qty','amc_item.repaired_qty','amc_item.faulty_qty', 'amc_item.rate', 'amc.created_at', 'amc.updated_at', 'amc.status');

if($request->fromdate != "" && $request->todate != ""){
	        	$all_squareUp = $all_squareUp->whereBetween(\DB::raw('date(amc.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $all_squareUp = $all_squareUp->where('amc.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $all_squareUp = $all_squareUp->whereIn('amc.warehouse',$warehouses);
            }

$all_squareUp = $all_squareUp->orderBy('amc.id','desc')->get();             
            
            
            $i = 0;

foreach ($all_squareUp as $key => $squareUp) {
                $finalArr[$i]['mr_no'] = $squareUp->mr_no;
                $finalArr[$i]['mr_date'] = $squareUp->created_at;
                $finalArr[$i]['warehouse'] = $squareUp->warehouse;
                $finalArr[$i]['spare_code'] = $squareUp->spare_code;
                $finalArr[$i]['description'] = $squareUp->description;
                $finalArr[$i]['uom'] = $squareUp->uom;
                $finalArr[$i]['type'] = $squareUp->type;
                $finalArr[$i]['fresh_qty'] = $squareUp->fresh_qty;
                $finalArr[$i]['repaired_qty'] = $squareUp->repaired_qty;
                $finalArr[$i]['faulty_qty'] = $squareUp->faulty_qty;
                $finalArr[$i]['basic_rate'] = $squareUp->rate;
                $finalArr[$i]['total_rate'] = ($squareUp->fresh_qty*$squareUp->rate)+($squareUp->rate*$squareUp->repaired_qty);
                $finalArr[$i]['requested_by'] = (array_key_exists($squareUp->request_by, $usersArr))?$usersArr[$squareUp->request_by]:$squareUp->request_by;
                $finalArr[$i]['approved_by'] = (array_key_exists($squareUp->authorize_by, $usersArr))?$usersArr[$squareUp->authorize_by]:$squareUp->authorize_by;
                $finalArr[$i]['approved_date'] = $squareUp->updated_at;
                $finalArr[$i]['status'] = $squareUp->status;

$i++;
            }

}

if($request->action_type == "export"){        	      
            
        	$all_users = \DB::table('users')->select('loginId', 'name')->get();

if($request->fromdate != "" && $request->todate != ""){
	        	$all_squareUp = $all_squareUp->whereBetween(\DB::raw('date(amc.created_at)'),[$fromdate,$todate]);
	        }

if($current_warehouse != "" && $current_warehouse != "all"){
                $all_squareUp = $all_squareUp->where('amc.warehouse',$current_warehouse);
            }

if($current_warehouse != "" && $current_warehouse == "all"){
                $all_squareUp = $all_squareUp->whereIn('amc.warehouse',$warehouses);
            }

$all_squareUp = $all_squareUp->orderBy('amc.id','desc')->get();             
            
            
            $i = 0;

$i++;
            }       
                       
        	return Excel::download(new SquareUpRoundOffReportExport($finalArr), 'squareup-roundoff-report.xlsx');
        }

$this->data = array_merge($this->data,['warehouses'=> $warehouses,'current_warehouse'=>$current_warehouse,'fromdate'=>$fromdate,'todate'=>$todate,'all_squareUp'=>$finalArr]);

return view('admin.reports.square-up-round-off-report', $this->data);
    }

}