000

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Session;
use Auth;
use DataTables;
use App\Report;
use App\Exports\ReportExport;
use App\Order;
use App\Exports\OrderExport;
use App\Exports\DailySaleReportExport;
use Maatwebsite\Excel\Facades\Excel;
use DB;

class ReportController extends Controller
{
	
             // Sale Reports Controller 
		        public function dealerSaleReportFilter(Request $request){
						$data = array();
						
							$data['acting_company'] = Session::get('acting_company');

$from = $request->from_date;
								$to = $request->to_date;
								$dealer_id = $request->dealer_id;
///old

// if(!empty($from) && !empty($to) && !empty($dealer_id)){
						// 	$orders = \App\Order::where('is_active',1)->whereBetween('created_at',[$from,$to])->orderBy('created_at','desc')->where('dealer_id',$dealer_id)->where('invoice_status',1)->groupBy('product_id')->groupBy('retailer_id')->groupBy('created_at')->select('retailer_id','product_id',\DB::raw('(DATE_FORMAT(created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();
								
						// }else if(!empty($dealer_id)){
						// 	// echo 'hello';
						// 		$orders = \App\Order::where('is_active',1)->where('dealer_id',$dealer_id)->groupBy('product_id')->groupBy('retailer_id')->groupBy('created_at')->select('retailer_id','product_id',\DB::raw('(DATE_FORMAT(created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();

// }else{
						// 	$orders = \App\Order::where('is_active',1)->whereBetween('created_at',[$from,$to])->orderBy('created_at','desc')->where('invoice_status',1)->groupBy('product_id')->groupBy('retailer_id')->groupBy('created_at')->select('retailer_id','product_id',\DB::raw('(DATE_FORMAT(created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();

// }

if(!empty($from) && !empty($to) && !empty($dealer_id)){
							$orders = \App\Order::where('is_active',1)->whereBetween('created_at',[$from,$to])->orderBy('created_at','desc')->where('dealer_id',$dealer_id)->where('invoice_status',1)->groupBy(['product_id','retailer_id','created_at'])->select('id','retailer_id','product_id','order_from','rake_point','from_warehouse_id',\DB::raw('(DATE_FORMAT(created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();
                      
								
						}else if(!empty($dealer_id)){
							// echo 'hello';
								$orders = \App\Order::where('is_active',1)->where('dealer_id',$dealer_id)->orderBy('created_at','desc')->groupBy(['product_id','retailer_id','created_at'])
								->select('id','retailer_id','product_id','order_from','rake_point','from_warehouse_id',\DB::raw('(DATE_FORMAT(created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();

}else{
							$orders = \App\Order::where('is_active',1)->whereBetween('created_at',[$from,$to])->orderBy('created_at','desc')->where('invoice_status',1)->groupBy(['product_id','retailer_id','created_at'])->select('id','retailer_id','product_id','order_from','rake_point','from_warehouse_id',\DB::raw('(DATE_FORMAT(created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();

}
					
						return Datatables::of($orders)
						->addIndexColumn()

->addColumn('order_date', function($orders){
 						
 						
 							$btn =' <td>';
 							$btn=$btn.date('d/m/Y',strtotime($orders->created_at));
 							
 							$btn=$btn.'</td>';
 						
 						return $btn;
 					})

->addColumn('retailer_name', function($orders){
						
								$btn =' <td>';
								
							
								//$btn=$btn.getretailer($orders->retailer_id)->name." &nbsp;&nbsp;[".$orders->retailer_id."]";
							 $btn=$btn.getretailer($orders->retailer_id)->name;
								
								$btn=$btn.'</td>';

return $btn;
 					})
 					
 					->addColumn('retailer_code', function($orders){
						
								//$btn =' <td>';
								$btn = '';
								
								$btn=$btn.$orders->retailer_id;
								
								//$btn=$btn.'</td>';
 
 						return $btn;
 					})

->addColumn('location', function($orders){
					
 						$btn =' <td>';
 						
 						if($orders->order_from == 1){
 						 $btn = $btn.getModelById('RakePoint', $orders->rake_point)->rake_point; 
 						} else {
 						 $btn = $btn.getModelById('Warehouse', $orders->from_warehouse_id)->name; 
 						}
 						
 						$btn=$btn.'</td>';
 					
 					return $btn;
 				// print_r($btn);
 			})
 			
						->addColumn('truck_no', function($orders){
					
 						$btn =' <td>';
 					
 				
 						$truck_numbers=\App\LoadingSlip::where('order_id',$orders->id)->pluck('vehicle_no');
 	
 						
 	
 						
 						$tn=[];
 						foreach($truck_numbers as $k => $v){
 						 array_push($tn,$v);	
 						}
 						$btn=$btn.implode(',',$tn);
 					
 						
 						$btn=$btn.'</td>';
 					
 		
 					return $btn;
 				// print_r($btn);
 			})

->addColumn('product_name', function($orders){
 					$role_id=Auth::user()->role_id;
 					$btn =' <td>';
 						
 					
 						$btn=$btn.getModelById('Product',$orders->product_id)->name;
 					
 						
 						$btn=$btn.'</td>';
 					
 					return $btn;
 					})
					
					 ->addColumn('destination', function($orders){
						
							$btn =' <td>';
 				if($orders->retailer_address !=null){
 					$location=$orders->retailer_address;
 				}else{
 if($orders->dealer_id !=null && $orders->retailer_id =="" ){
 						$location_name=\App\Location::where('location_id', getdealer($orders->dealer_id)->destination_code)->first();
 						if($location_name !=null){
 							$location=$location_name->name;
 						}else{
 							$location="";
 						}
 	
 					}else{
 				 $location_name=\App\Location::where('location_id', getretailer($orders->retailer_id)->destination_code)->first();
 				 if($location_name !=null){
 					$location=$location_name->name;
 				}else{
 					$location="";
 				}
 					} 
 				}
 			
 				$btn=$btn."".$location."";
 			
 				
 				$btn=$btn.'</td>';

return $btn;
					})
					
					
					->rawColumns(['retailer_name','product_name','location','truck_no','destination'])
					->with('totalBag', function() use ($orders) {
						return $orders->sum('qty');
					})->make(true);
				}

function dealerSaleReport(){
			   	$data=[];
			   	$data['dealers']= \App\Dealer::where('is_active',1)->get();

return view('dashboard.report.dealer-sale-report',$data);

}

public function dealerMonthSaleReportFilter(Request $request){
				$data = array();
				
				$data['acting_company'] = Session::get('acting_company');
				if(!is_null($request->month)){
					$date = date('Y-m',strtotime($request->month));
				}else{
					$date = "";
				}
				
				$dealer_id = $request->dealer_id;

if(!empty($dealer_id) && !empty($date)){
					
					$orders = \App\Order::where('is_active',1)->where(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'),$date)->orderBy('created_at','desc')->where('dealer_id',$dealer_id)->where('order_status',"approved")->groupBy(['product_id','retailer_id','created_at'])->select('id','retailer_id','product_id','order_from','rake_point','from_warehouse_id',\DB::raw('(DATE_FORMAT(created_at,"%M-%Y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();
			  		
						
				}elseif(!empty($dealer_id) && empty($date)){
				
					
					$orders = \App\Order::where('is_active',1)->where('dealer_id',$dealer_id)->where('order_status',"approved")->orderBy('created_at','desc')->groupBy(['product_id','retailer_id','created_at'])->select('id','retailer_id','product_id','order_from','rake_point','from_warehouse_id',\DB::raw('(DATE_FORMAT(created_at,"%M-%Y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();
					
				}else{
					
					// $orders = \App\Order::where('is_active',1)->where('invoice_status',1)->orderBy('created_at','desc')->groupBy(['dealer_id','product_id','retailer_id','created_at'])->select('id','retailer_id','product_id','order_from','rake_point','from_warehouse_id',\DB::raw('(DATE_FORMAT(created_at,"%M-%Y")) as date'),\DB::raw("SUM(quantity) as qty"))->get();
					// dd($orders->toSql());
				}
			
				return Datatables::of($orders)
				->addIndexColumn()

->addColumn('date', function($orders){
					
						$btn ='<td>';
						$btn=$btn.date('M-Y',strtotime($orders->date));
						
						$btn=$btn.'</td>';
					
					return $btn;
				})

->addColumn('retailer_name', function($orders){
				
						$btn ='<td>';
						
						$btn=$btn.getretailer($orders->retailer_id)->name;
						
						$btn=$btn.'</td>';

return $btn;
				})
				
				->addColumn('retailer_code', function($orders){
				
						$btn = '<td>';
						
						$btn=$btn.$orders->retailer_id;
						
						$btn=$btn.'</td>';
				 
					return $btn;
				})

->addColumn('product_name', function($orders){
					$role_id=Auth::user()->role_id;
					$btn =' <td>';
						
					$btn=$btn.getModelById('Product',$orders->product_id)->name;
					
					$btn=$btn.'</td>';
					
					return $btn;
				})
			
				->addColumn('destination', function($orders){
				
					$btn =' <td>';
					if($orders->retailer_address !=null){
						$location=$orders->retailer_address;
					}else{
						if($orders->dealer_id !=null && $orders->retailer_id =="" ){
							$location_name=\App\Location::where('location_id', getdealer($orders->dealer_id)->destination_code)->first();
							if($location_name !=null){
								$location=$location_name->name;
							}else{
								$location="";
							}
		
						}else{
						$location_name=\App\Location::where('location_id', getretailer($orders->retailer_id)->destination_code)->first();
						if($location_name !=null){
								$location=$location_name->name;
							}else{
								$location="";
							}
						} 
				}
			
				$btn=$btn."".$location."";
			
				
				$btn=$btn.'</td>';

return $btn;
			})
			
			
			->rawColumns(['retailer_code','retailer_name','destination','product_name','qty','date'])
			->with('totalBag', function() use ($orders) {
				return $orders->sum('qty');
			})->make(true);
		}

function dealerMonthSaleReport(){
		   $data=[];
		   $data['dealers']= \App\Dealer::where('is_active',1)->get();

return view('dashboard.report.dealer-month-wise-sale-report',$data);

}

public function retailerSaleReportFilter(Request $request){
						$data = array();

$from = $request->from_date;
								$to = $request->to_date;
								$retailer_id = $request->retailer_id;

if(!empty($from) && !empty($to) && !empty($retailer_id)){
							$orders = \App\Order::where('is_active',1)->whereBetween('created_at',[$from,$to])->orderBy('created_at','desc')->where('retailer_id',$retailer_id)->where('invoice_status',1)->select('retailer_id','product_id','quantity',DB::raw('DATE_FORMAT(created_at,"%y-%m-%d") AS date'))->get();
								
						}else if(!empty($retailer_id)){
							// echo 'hello';
								$orders = \App\Order::where('is_active',1)->where('retailer_id',$retailer_id)->select('retailer_id','product_id','quantity',DB::raw('DATE_FORMAT(created_at,"%y-%m-%d") AS date'))->get();

}else{
							$orders = \App\Order::where('is_active',1)->whereBetween('created_at',[$from,$to])->orderBy('created_at','desc')->where('invoice_status',1)->select('retailer_id','product_id','quantity',DB::raw('DATE_FORMAT(created_at,"%y-%m-%d") AS date'))->get();

}
					
						return Datatables::of($orders)
						->addIndexColumn()

->addColumn('order_date', function($orders){
						
						
							$btn =' <td>';
							$btn=$btn.date('d/m/Y',strtotime($orders->created_at));
							
							$btn=$btn.'</td>';
						
						return $btn;
					})

->addColumn('retailer_name', function($orders){
						
								$btn =' <td>';
								
							
								$btn=$btn.getretailer($orders->retailer_id)->name." &nbsp;&nbsp;[".$orders->retailer_id."]";
							
								
								$btn=$btn.'</td>';

return $btn;
					})

->addColumn('product_name', function($orders){
					$role_id=Auth::user()->role_id;
					$btn =' <td>';
						
					
						$btn=$btn.getModelById('Product',$orders->product_id)->name;
					
						
						$btn=$btn.'</td>';
					
					return $btn;
					})
					
					
						->rawColumns(['retailer_name','product_name'])
						->with('totalBag', function() use ($orders) {
						return $orders->sum('qty');
						})->make(true);
				}
			 public function retailerSaleReport(){
			 	$data=[];
			 		$data['retailers']= \App\Retailer::where('is_active',1)->get();
			 	 return view('dashboard.report.retailer-sale-report',$data);
			 }

public function destinationSaleReportFilter(Request $request){
						$data = array();

$from = $request->from_date;
								$to = $request->to_date;
								if(isset($request->destination_id)){
								   	$destination_id = $request->destination_id;
								   	// $destination_id = json_decode(json_encode($destination_id));
                                // $destination_id = join(',',$destination_id);
                                // dd($destination_id);
                                $des_new='';
                                foreach($destination_id as $des){
                                    $des_new.="'".$des."',";
                                }
                                
                                $des_new= substr($des_new,0,-1);
                               
                                //  dd();
								}
								
								// dd($from);

ini_set('max_execution_time', 1000);
						if(!empty($from) && !empty($to) && !empty($request->destination_id)){
						    
						    $orders =  \DB::select(\DB::raw("select orders.retailer_address,`orders`.`dealer_id`, `orders`.`retailer_id`, `orders`.`product_id`, `dealers`.`destination_code` as dealer_destination,`retailers`.`destination_code` as retailer_destination, (DATE_FORMAT(orders.created_at,'%d/%m/%y')) as date, SUM(orders.quantity) as qty, `dealers`.`name` as `dealer_name`, `retailers`.`name` as `retailer_name` from `orders` 
                                inner join `dealers` on `dealers`.`unique_id` = `orders`.`dealer_id`
                                inner join `retailers` on `retailers`.`unique_code` = `orders`.`retailer_id`
                                where `orders`.`is_active` = 1 
                                and date(`orders`.`created_at`) between '".$from."' and '".$to."'
                                and `orders`.`invoice_status` = 1
                                and
                                if(orders.retailer_address!='',if(orders.retailer_id=0,dealers.destination_code,retailers.destination_code),dealers.destination_code) in
                                (".
                                
                                
                                $des_new
                                
                                
                                
                                .") 
                              
                                group by `orders`.`product_id`, `orders`.`retailer_id`, `orders`.`dealer_id`, Date(orders.created_at) 
                                order by `orders`.`created_at` desc"));
							
								// $orders = \App\Order::join('dealers','dealers.unique_id','orders.dealer_id')->join('retailers','retailers.unique_code','orders.retailer_id')
								//     ->where('orders.is_active',1)
								//     ->whereBetween(\DB::raw('DATE(orders.created_at)'),[$from,$to])->orderBy('orders.created_at','desc')
								//     ->where('orders.invoice_status',1)
								//     ->whereRaw("if(orders.retailer_address!='',retailers.destination_code in".$destination_id.",dealers.destination_code in".$destination_id.")")
								//     // ->WhereIn('dealers.destination_code',$destination_id)
								//     ->groupBy('orders.product_id')
								//     ->groupBy('orders.retailer_id')
								//     ->groupBy('orders.dealer_id')
								//     ->groupBy(DB::raw('Date(orders.created_at)'))
								//     ->select('orders.dealer_id','orders.retailer_id','orders.product_id','dealers.destination_code',\DB::raw('(DATE_FORMAT(orders.created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(orders.quantity) as qty"),'dealers.name as dealer_name','retailers.name as retailer_name')->get();
								// dd($orders);
								
						}else if(!empty($request->destination_id)){
						     $orders =  \DB::select(\DB::raw("select orders.retailer_address,`orders`.`dealer_id`, `orders`.`retailer_id`, `orders`.`product_id`, `dealers`.`destination_code` as dealer_destination,`retailers`.`destination_code` as retailer_destination, (DATE_FORMAT(orders.created_at,'%d/%m/%y')) as date, SUM(orders.quantity) as qty, `dealers`.`name` as `dealer_name`, `retailers`.`name` as `retailer_name` from `orders` 
                                inner join `dealers` on `dealers`.`unique_id` = `orders`.`dealer_id`
                                inner join `retailers` on `retailers`.`unique_code` = `orders`.`retailer_id`
                                where `orders`.`is_active` = 1 
                                and  `orders`.`invoice_status` = 1
                                and if(orders.retailer_address!='',if(orders.retailer_id=0,dealers.destination_code,retailers.destination_code),dealers.destination_code) in
                                (".$des_new.") 
                                group by `orders`.`product_id`, `orders`.`retailer_id`, `orders`.`dealer_id`, Date(orders.created_at) 
                                order by `orders`.`created_at` desc"));
							
								// 	$orders = \App\Order::join('dealers','dealers.unique_id','orders.dealer_id')->join('retailers','retailers.unique_code','orders.retailer_id')->where('orders.is_active',1)->orderBy('orders.created_at','desc')->where('orders.invoice_status',1)->WhereIn('dealers.destination_code',$destination_id)
								// 	    ->groupBy('orders.product_id')
								// 	    ->groupBy('orders.retailer_id')
								// 	    ->groupBy('orders.dealer_id')
								// 	    ->groupBy(\DB::raw('Date(orders.created_at)'))
								// 	    ->select('orders.dealer_id','orders.retailer_id','orders.product_id','dealers.destination_code',\DB::raw('(DATE_FORMAT(orders.created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(orders.quantity) as qty"),'dealers.name as dealer_name','retailers.name as retailer_name')->get();
									   // dd($orders);

}else{
						    
						     
						    $orders =  \DB::select(\DB::raw("select orders.retailer_address,`orders`.`dealer_id`, `orders`.`retailer_id`, `orders`.`product_id`, `dealers`.`destination_code` as dealer_destination,`retailers`.`destination_code` as retailer_destination, (DATE_FORMAT(orders.created_at,'%d/%m/%y')) as date, SUM(orders.quantity) as qty, `dealers`.`name` as `dealer_name`, `retailers`.`name` as `retailer_name` from `orders` 
                                inner join `dealers` on `dealers`.`unique_id` = `orders`.`dealer_id`
                                inner join `retailers` on `retailers`.`unique_code` = `orders`.`retailer_id`
                                where `orders`.`is_active` = 1 
                                and date(`orders`.`created_at`) between '".date('Y-m-d')."' and '".date('Y-m-d')."'
                                and `orders`.`invoice_status` = 1
                                
                                group by `orders`.`product_id`, `orders`.`retailer_id`, `orders`.`dealer_id`, Date(orders.created_at) 
                                order by `orders`.`created_at` desc"));
                                
						  //  	$orders = \App\Order::join('dealers','dealers.unique_id','orders.dealer_id')->join('retailers','retailers.unique_code','orders.retailer_id')->where('orders.is_active',1)->whereBetween(\DB::raw('DATE(orders.created_at)'),[date('Y-m-d'),date('Y-m-d')])->orderBy('orders.created_at','desc')->where('orders.invoice_status',1)->groupBy('orders.product_id')->groupBy('orders.retailer_id')->groupBy('orders.dealer_id')->groupBy(DB::raw('Date(orders.created_at)'))->select('orders.dealer_id','orders.retailer_id','orders.product_id','dealers.destination_code',\DB::raw('(DATE_FORMAT(orders.created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(orders.quantity) as qty"),'dealers.name as dealer_name','retailers.name as retailer_name')->get();
						}
					
						return Datatables::of($orders)
						->addIndexColumn()

->addColumn('order_date', function($orders){
						
						
							$btn =' <td>';
							$btn=$btn.date('d/m/Y',strtotime($orders->date));
							
							$btn=$btn.'</td>';
						
						return $btn;
					})

return $btn;
					})
						->addColumn('delear_name', function($orders){
						
								$btn =' <td>';
								
							
								$btn=$btn.getdealer($orders->dealer_id)->name." &nbsp;&nbsp;[".$orders->dealer_id."]";
							
								
								$btn=$btn.'</td>';

return $btn;
					})

->addColumn('product_name', function($orders){
					$role_id=Auth::user()->role_id;
					$btn =' <td>';
						
					
						$btn=$btn.getModelById('Product',$orders->product_id)->name;
					
						
						$btn=$btn.'</td>';
					
					return $btn;
					})
					
					->addColumn('destination', function($orders){
						
								$btn =' <td>';
								
								if($orders->retailer_address!=""){
								 $destination=$orders->retailer_destination;
								 if($orders->retailer_id==0){
								 $destination=$orders->retailer_address;
								 
								 }else{
								 $destination=getModelByCode('Location','location_id',$destination)->name." (".getModelByCode('Location','location_id',$destination)->district.")";
								 
								 }
								 
								 
								}else{
								 $destination=$orders->dealer_destination;
								 $destination=getModelByCode('Location','location_id',$destination)->name." (".getModelByCode('Location','location_id',$destination)->district.")";
								}
								
							
								$btn=$btn."".$destination."";
							
								
								$btn=$btn.'</td>';

return $btn;
					})
						->addColumn('date', function($orders){
						
								$btn =' <td>';
								
							
								$btn=$btn."".$orders->date."";
							
								
								$btn=$btn.'</td>';

return $btn;
					})
					
						->addColumn('total_qty', function($orders){
						
								$btn =' <td>';
								
							
								$btn=$btn."".$orders->qty."";
							
								
								$btn=$btn.'</td>';

return $btn;
					})
					
					
					
					
					
					
						->rawColumns(['retailer_name','product_name','delear_name','destination','date','total_qty'])
				// 		->with('totalBag', function() use ($orders) {
				// 		return $orders->sum('qty');
				// 		})
						->make(true);
				}

public function destinationSaleReportFilter2(Request $request){
                        
						$data = array();
						$data['destinations']= \App\Location::where('is_active',1)->get();
						
						if ($request->isMethod('post')){
                                //dd($request->all());
								$from = $request->from_date;
								$to = $request->to_date;
								$destination_id = $request->destination_id;

// ini_set('max_execution_time', 1000000);
                        ini_set('memory_limit', '-1');
						if(!empty($from) && !empty($to) && !empty($destination_id)){
							$data['orders'] = \App\Order::join('dealers','dealers.unique_id','orders.dealer_id')->join('retailers','retailers.unique_code','orders.retailer_id')->where('orders.is_active',1)->whereBetween('orders.created_at',[$from,$to])->orderBy('orders.created_at','desc')->where('orders.invoice_status',1)->whereIn('retailers.destination_code',$destination_id)->orWhereIn('dealers.destination_code',$destination_id)->groupBy('orders.product_id')->groupBy('orders.retailer_id')->groupBy('orders.dealer_id')->select('dealers.name as delear_name','retailers.name as retailer_name','orders.dealer_id','orders.retailer_id','orders.product_id','dealers.destination_code',\DB::raw('(DATE_FORMAT(orders.created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(orders.quantity) as qty"),'dealers.name as dealer_name','retailers.name as retailer_name')->get();
								//dd($data['orders']);
						}else if(!empty($destination_id)){
							// echo 'hello';
								$data['orders'] = \App\Order::join('dealers','dealers.unique_id','orders.dealer_id')->join('retailers','retailers.unique_code','orders.retailer_id')->where('orders.is_active',1)->orderBy('orders.created_at','desc')->where('orders.invoice_status',1)->whereIn('retailers.destination_code',$destination_id)->orWhereIn('dealers.destination_code',$destination_id)->groupBy('orders.product_id')->groupBy('orders.retailer_id')->groupBy('orders.dealer_id')->groupBy(DB::raw('Date(orders.created_at)'))->select('orders.dealer_id','orders.retailer_id','orders.product_id','dealers.destination_code',\DB::raw('(DATE_FORMAT(orders.created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(orders.quantity) as qty"),'dealers.name as dealer_name','retailers.name as retailer_name')->get();

}else{
							$data['orders'] = \App\Order::join('dealers','dealers.unique_id','orders.dealer_id')->join('retailers','retailers.unique_code','orders.retailer_id')->where('orders.is_active',1)->whereBetween('orders.created_at',[$from,$to])->orderBy('orders.created_at','desc')->where('orders.invoice_status',1)->groupBy('orders.product_id')->groupBy('orders.retailer_id')->groupBy('orders.dealer_id')->select('orders.dealer_id','orders.retailer_id','orders.product_id','dealers.destination_code',\DB::raw('(DATE_FORMAT(orders.created_at,"%d/%m/%y")) as date'),\DB::raw("SUM(orders.quantity) as qty"),'dealers.name as dealer_name','retailers.name as retailer_name')->get();
						}
						}
						
						 return view('dashboard.report.destination-sale-report2',$data);
					
				}

public function destinationSaleReport(){
			    	$data=[];
			   		$data['destinations']= \App\Location::where('is_active',1)->get();
			   		
			    	return view('dashboard.report.destination-sale-report',$data);

}
 
 public function retailerPotentialSaleReportFilter(Request $request){
						
						// echo $financial_year_start.' '.$financial_year_end;

}
				 public function retailerPotentialSaleReport(Request $request){
				   	  $data=[];
				   	  	$data['sessions']= \App\Session::where('is_active',1)->get();
							if ($request->isMethod('post')){
									$session_id = $request->session_id;
									$years = explode("-", $session_id);
									$financial_year_start=$years[0].'-04-01';
									$financial_year_end=$years[1].'-03-31';
					             $data['orders'] =  \App\Order::where('is_active',1)->whereBetween('created_at',[$financial_year_start,$financial_year_end])->orderBy('m','asc')->where('invoice_status',1)->groupBy('product_id')->groupBy('retailer_id')->groupBy('y')->groupBy('m')->select(\DB::raw("YEAR(created_at) as y"),\DB::raw("MONTH(created_at) as m"),'retailer_id','product_id',\DB::raw("SUM(quantity) as qty"))->get();

$data['month_codes']=[
					             	0=>4,
					             	1=>5,
					             	2=>6,
					             	3=>7,
					             	4=>8,
					             	5=>9,
					             	6=>10,
					             	7=>11,
					             	8=>12,
					             	9=>1,
					             	10=>2,
					             	11=>3,
					             	
					             	
					             ];
					             $data['session_id']=$session_id;
					                // dd($data['orders']);
					       }
						   
						   	return view('dashboard.report.retailer-potential-sale-report',$data);

}
				  
				public function monthlySaleReportNew(Request $request){
					$data = [];
					if($request->isMethod('post')){
						
						$validator = \Validator::make($request->all(),
							array(
									'month' => 'required'
								)
						);
						if($validator->fails()){
							return redirect()->back()->withInput();
						}else{
							ini_set('memory_limit', '256M');
							$date = date('Y-m',strtotime($request->month));
							
							$orders =  \App\Order::where([['order_status','=','approved'],['is_active','=',1]])->where(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'),$date)->groupBy(['dealer_id','product_id'])->select('dealer_id','product_id','created_at')->get();
							
							foreach($orders as $key=>$order){
								$temQty = \App\Order::where([['dealer_id','=',$order->dealer_id],['product_id','=',$order->product_id],['order_status','=','approved'],['is_active','=',1]])->where(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'),$date)->sum('quantity');
								
								$temArr['dealer_id']= $order->dealer_id;
								$temArr['product_id']= $order->product_id;
								$temArr['qty']= $temQty;
								$temArr['date']= $order->created_at;
								
								$data['monthly_sale'][$key]= $temArr;
							}
							$data['monthDate']= $date;
						}
					}
					return view('dashboard.report.monthly_sale_reports',$data);
				}
				public function dealerWiseMonthlySaleReport(Request $request){
					
					$data = [];
					$data['dealers']= \App\Dealer::where('is_active',1)->get();
					if($request->isMethod('post')){
						
						$validator = \Validator::make($request->all(),
							array(
									'dealer_id' => 'required'
								)
						);
						if($validator->fails()){
							return redirect()->back()->withInput();
						}else{
							
							ini_set('memory_limit', '256M');
							if(!is_null($request->month)){
								$date = date('Y-m',strtotime($request->month));
							}else{
								$date = null;
							}
							$dealer_id = $request->dealer_id;

if(!is_null($dealer_id) && is_null($date)){
								
								$key=0;
								$dates =  \App\Order::where([['order_status','=','approved'],['is_active','=',1]])->groupBy(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'))->select(DB::raw('DATE_FORMAT(created_at,"%Y-%m") as date'))->orderBy('id','desc')->get();
								foreach($dates as $dateVal){
									
									$temOrder = \App\Order::where([['dealer_id','=',$dealer_id],['order_status','=','approved'],['is_active','=',1]])->where(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'),$dateVal->date)->groupBy(['retailer_id','product_id'])->select('retailer_id','product_id','retailer_address')->get();
									
									if(count($temOrder)>0){
										foreach($temOrder as $val){
											$qty = \App\Order::where([['dealer_id','=',$dealer_id],['retailer_id','=',$val->retailer_id],['product_id','=',$val->product_id],['order_status','=','approved'],['is_active','=',1]])->where(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'),$dateVal->date)->sum('quantity');
											
											$temArr['retailer_id']= $val->retailer_id;
											$temArr['product_id']= $val->product_id;
											if(!is_null($val->retailer_address)){
												$retailerAddress= $val->retailer_address.", ".getretailer($val->retailer_id)->district;
											}else{
												$retailerAddress = getdealer($dealer_id)->address1.", ".getdealer($dealer_id)->district;
											}
											$temArr['destination']= $retailerAddress;
											$temArr['qty']= $qty;
											$temArr['date']= date('M-Y',strtotime($dateVal->date));
											
											$data['reports'][$key]= $temArr;
											$key++;
										}
									}
								}
							}elseif(!is_null($dealer_id) && !is_null($date)){
								
								$key=0;
								$temOrder = \App\Order::where([['dealer_id','=',$dealer_id],['order_status','=','approved'],['is_active','=',1]])->where(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'),$date)->groupBy(['retailer_id','product_id'])->select('retailer_id','product_id','retailer_address')->orderBy('id','desc')->get();
								
								if(count($temOrder)>0){
									foreach($temOrder as $val){
										$qty = \App\Order::where([['dealer_id','=',$dealer_id],['retailer_id','=',$val->retailer_id],['product_id','=',$val->product_id],['order_status','=','approved'],['is_active','=',1]])->where(DB::raw('DATE_FORMAT(created_at,"%Y-%m")'),$date)->sum('quantity');
										
										$temArr['retailer_id']= $val->retailer_id;
										$temArr['product_id']= $val->product_id;
										$temArr['destination']= $val->retailer_address;
										$temArr['qty']= $qty;
										$temArr['date']= date('M-Y',strtotime($date));
										
										$data['reports'][$key]= $temArr;
										$key++;
									}
								}
								$data['monthDate'] = $date;
							}
							$data['dealerId'] = $dealer_id;
						}
					}
					return view('dashboard.report.dealer-month-wise-sale-report',$data);
				}

public function monthlySaleReport(){
					return view('dashboard.report.monthly_sale_reports');
				}

public function monthlySaleReports(Request $request){
	
					  if($request->year!=""){
						$year= $request->year;
	
					  }else{
						  $year=date("Y");
	
					  }
					  
					$month=($request->month_id);
					//$year=($request->year);
					return Excel::download(new ReportExport($year,$month), 'report.xlsx');
					
                }
                
                public function dailySaleReport(){
					$data=[];
					$data['dealers']= \App\Dealer::where('is_active',1)->get();
					$data['retailers']= \App\Dealer::where('is_active',1)->get();
					// $data['products'] = \App\Product::where('is_active',1)->get();
					return view('dashboard.report.daily_sale_report',$data);
    
			    }

public function dailySaleReports(Request $request){
					// dd($request->all());
					if($request->date!=""){
						$date=($request->date);
					}else{
						$date=date('Y-m-d');
					}
					$dealer_id=($request->dealer_id);
					return Excel::download(new DailySaleReportExport($dealer_id,$date), 'dailysalereport.xlsx');
					}
					
					
			public function report()
         	{
         	$data = array();
		$data['rakes']  = \App\RakePoint::all();
		$data['warehouses'] = \App\Warehouse::all();
		   return view('dashboard.report.report-genrate',$data);
           	}
           	
           	
      public function getreport(Request $request)
{
    $from_date = $request->from_date;
    $to_date = $request->to_date;
    $order_from = $request->order_from;
    $rake_points = $request->rake_point;
    $godowns = $request->godown;

return Excel::download(new OrderExport($from_date, $to_date, $order_from, $rake_points, $godowns), 'master_report.xlsx');
    
}

}