000

<?php

namespace App\Http\Controllers;
use Illuminate\Support\Facades\View;
use Illuminate\support\Facades\DB;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Session;
use Maatwebsite\Excel\Facades\Excel;
// use App\Imports\ImportUser;
use App\Exports\ExportFormList;
use App\Exports\ExportOtherFormList;
use App\Exports\ExportMCF;
use App\Exports\ExportFNF;
use App\Exports\UserFormExport;
use App\Exports\AnimalFormExport;
use App\Exports\UserApprovedFormExport;
use App\Exports\UserRejectedFormExport;
use App\Exports\UserPendingFormExport;
use App\Exports\memberExport;
use Carbon\Carbon;
use PDF;

class ReportController extends Controller
{

public function user_form_report(Request $request){
	   // dd($request->all());
		$data = array();
		$data['page'] = 'User Form Report';

if(\Auth::user()->role_id=='1'){
            $data['mcc'] = \DB::table('mcc')->where('is_active', 1)->get();
            $data['mpps'] = \DB::table('mpp')->where('is_active', 1)->get();
            $form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by','finance_edit_permissions.user_id as fo_user_id','legal_edit_permissions.user_id as lo_user_id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->whereDate('msd.created_at', Carbon::today())->where('msd.is_active', 1)->orderBy('msd.id','desc')->get();
        }elseif(\Auth::user()->role_id=='2'){
            if(\Auth::user()->role_type=='1'){
            $data['mcc'] = \DB::table('mcc')->whereIn('code',explode(',',\Auth::user()->assigned_mcc))->where('is_active', 1)->get();
	    	$data['mpps'] = \DB::table('mpp')->whereIn('mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('is_active', 1)->get();
            $form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by','finance_edit_permissions.user_id as fo_user_id','legal_edit_permissions.user_id as lo_user_id')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->whereIn('mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->whereDate('msd.created_at', Carbon::today())->where('msd.is_active', 1)->orderBy('msd.id','desc')->get();
            }elseif(\Auth::user()->role_type=='2'){
                $data['mcc'] = \DB::table('mcc')->whereIn('code',explode(',',\Auth::user()->assigned_mcc))->where('is_active', 1)->get();
	    	$data['mpps'] = \DB::table('mpp')->whereIn('mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('is_active', 1)->get();
            $form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by','finance_edit_permissions.user_id as fo_user_id','legal_edit_permissions.user_id as lo_user_id')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->whereIn('mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->whereDate('msd.created_at', Carbon::today())->where('msd.is_active', 1)->orderBy('msd.id','desc')->get();
            }elseif(\Auth::user()->role_type=='3'){
                $data['mcc'] = \DB::table('mcc')->whereIn('code',explode(',',\Auth::user()->assigned_mcc))->where('is_active', 1)->get();
	    	$data['mpps'] = \DB::table('mpp')->whereIn('mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('is_active', 1)->get();
            $form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by','finance_edit_permissions.user_id as fo_user_id','legal_edit_permissions.user_id as lo_user_id')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->whereIn('mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->whereDate('msd.created_at', Carbon::today())->where('msd.is_active', 1)->orderBy('msd.id','desc')->get();
            }
        }
	   
        $finalArr = array();
        $finalArrExt = array();
        if(!$form_data->isEmpty()) {
            
            foreach($form_data as $key=>$form){
                if($request->pibuser){
                    // dd($request->pibuser);
                $allotted_data = \DB::table('user_edit_permissions as up')->select('up.form_id', 'up.module_id', 'up.user_id', 'up.update_status', 'up.update_status_details')->where('form_id', $form->id)->where('user_id', $request->pibuser)->where('is_active', 1)->get();
                }else{
                $allotted_data = \DB::table('user_edit_permissions as up')->select('up.form_id', 'up.module_id', 'up.user_id', 'up.update_status', 'up.update_status_details')->where('form_id', $form->id)->where('is_active', 1)->get();
                }
                $finalArr['allotted_data'] = array();
                if(!$allotted_data->isEmpty()) {
                    
                    $arr = array();
                    $i = 0;
                    foreach($allotted_data as $key=>$value) {
                        $arr[$i]['module_id'] = $value->module_id;
                        $arr[$i]['user_name'] = getModelById('User',$value->user_id)->name;
                        $arr[$i]['update_status'] = $value->update_status;
                        $i++;
                    }
                    
                    $finalArr['allotted_data'] = $arr;
                }
                        
                $finalArr['form_no'] = $form->form_no;
                $finalArr['created_by'] = getModelById('User',$form->created_by_id)->name;
                $finalArr['lo_user_name'] = getModelById('User',$form->lo_user_id)->name;
                $finalArr['fo_user_name'] = getModelById('User',$form->fo_user_id)->name;
                $finalArr['mcc_name'] = $form->mcc_name;
                $finalArr['mpp_name'] = $form->mpp_name;
                $finalArr['approval_status'] = $form->approval_status;
                $finalArr['ao_approval_status'] = $form->ao_approval_status;
                $finalArr['lo_approval_status'] = $form->lo_approval_status;
                $finalArr['fo_approval_status'] = $form->fo_approval_status;
                $finalArr['final_approval_status'] = $form->final_approval_status;
                $finalArr['remark'] = $form->remark;
                
                $finalArr['final_approved_by'] = '';
                if(!is_null($form->final_approved_by)) {
                    $finalArr['final_approved_by'] = getModelById('User',$form->final_approved_by)->name;    
                }
                
                
                array_push($finalArrExt, $finalArr);
            }
            
        }
        // dd($finalArrExt);
        $data['form'] = $finalArrExt;
	   	if($request->isMethod('post')){

$validator = \Validator::make($request->all(),array(
				'from_date' => 'required_with:to_date',
				'to_date' => 'nullable|after_or_equal:from_date'
			),array(
				'from_date.required_with' => "The 'From Date' field is required when 'To Date' is present.",
				'to_date.after_or_equal' => "'To Date' must not be less than 'From Date'"
			));

if($validator->fails()){
				return redirect()->back()->withErrors($validator)->withInput();
			}else{
                if(\Auth::user()->role_id == '1' || '2'){
                    
                $from = $request->from_date;
                $to = $request->to_date;
                $currentDate= date('Y-m-d');

if(!empty($request->mcc_name) && empty($request->mpp_name) && empty($from) && empty($to)){
					
					$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mcc_code','msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mcc_code', $request->mcc_name)->where('msd.is_active', 1)->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                        if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mccCode'] = $request->mcc_name;

}elseif(!empty($request->mcc_name) && !empty($request->mpp_name) && empty($from) && empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approval_status', 'msd.remark', 'msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mcc_code', $request->mcc_name)->where('msd.mpp_code',$request->mpp_name)->where('msd.is_active', 1)->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mccCode'] = $request->mcc_name;
					$data['mppCode'] = $request->mpp_name;
                   
				}elseif(!empty($request->mcc_name) && !empty($request->mpp_name) && !empty($from) && empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mcc_code', $request->mcc_name)->where('msd.mpp_code',$request->mpp_name)->where('msd.mpp_code',$request->mpp_name)->where('msd.is_active', 1)->whereBetween(DB::raw('date(msd.created_at)'),[$from,$currentDate])->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mccCode'] = $request->mcc_name;
					$data['mppCode'] = $request->mpp_name;
					$data['fromDate'] = $from;
                   
				}elseif(!empty($request->mcc_name) && !empty($request->mpp_name) && !empty($from) && !empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mcc_code', $request->mcc_name)->where('msd.mpp_code',$request->mpp_name)->where('msd.mpp_code',$request->mpp_name)->where('msd.is_active', 1)->whereBetween(DB::raw('date(msd.created_at)'),[$from,$to])->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mccCode'] = $request->mcc_name;
					$data['mppCode'] = $request->mpp_name;
					$data['fromDate'] = $from;
					$data['toDate'] = $to;

}elseif(!empty($request->mcc_name) && empty($request->mpp_name) && !empty($from) && !empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mcc_code', $request->mcc_name)->where('msd.mpp_code',$request->mpp_name)->where('msd.is_active', 1)->whereBetween(DB::raw('date(msd.created_at)'),[$from,$to])->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mppCode'] = $request->mpp_name;
					$data['fromDate'] = $from;
					$data['toDate'] = $to;
				}elseif(empty($request->mcc_name) && !empty($request->mpp_name) && empty($from) && empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mpp_code',$request->mpp_name)->where('msd.is_active', 1)->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mppCode'] = $request->mpp_name;
					
				}elseif(empty($request->mcc_name) && !empty($request->mpp_name) && !empty($from) && empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approval_status', 'msd.remark', 'msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mpp_code',$request->mpp_name)->where('msd.is_active', 1)->whereBetween(DB::raw('date(msd.created_at)'),[$from,$currentDate])->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mppCode'] = $request->mpp_name;
					$data['fromDate'] = $from;
					
				}elseif(empty($request->mcc_name) && !empty($request->mpp_name) && !empty($from) && !empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.final_approval_status', 'msd.remark', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.mpp_code',$request->mpp_name)->where('msd.is_active', 1)->whereBetween(DB::raw('date(msd.created_at)'),[$from,$to])->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['mppCode'] = $request->mpp_name;
					$data['fromDate'] = $from;
					$data['toDate'] = $to;

}elseif(empty($request->mcc_name) && empty($request->mpp_name) && !empty($from) && empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approval_status', 'msd.remark', 'msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.is_active', 1)->whereBetween(DB::raw('date(msd.created_at)'),[$from,$currentDate])->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                        if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['fromDate'] = $from;

}elseif(empty($request->mcc_name) && empty($request->mpp_name) && !empty($from) && !empty($to)){

$form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approval_status', 'msd.remark', 'msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.is_active', 1)->whereBetween(DB::raw('date(msd.created_at)'),[$from,$to])->orderBy('msd.id','desc');
                    if(\Auth::user()->role_id == '2'){
                         if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
					$data['fromDate'] = $from;
					$data['toDate'] = $to;

}else{
                    $form_data = \DB::table('membership_details as msd')->select('msd.id', 'msd.form_no', 'msd.created_by_id', 'msd.mcc_name', 'msd.mpp_name', 'msd.approval_status', 'msd.ao_approval_status','msd.lo_approval_status','msd.fo_approval_status','msd.final_approval_status', 'msd.remark', 'msd.final_approved_by')->leftjoin('user_edit_permissions','user_edit_permissions.form_id','msd.id')->leftjoin('legal_edit_permissions','legal_edit_permissions.form_id','msd.id')->leftjoin('finance_edit_permissions','finance_edit_permissions.form_id','msd.id')->where('msd.is_active', 1)->orderBy('msd.id','desc');

if(\Auth::user()->role_id == '2'){
                        if(\Auth::user()->role_type=='1'){
                        $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('user_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('legal_edit_permissions.user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $form_data = $form_data->whereIn('msd.mcc_code',explode(',',\Auth::user()->assigned_mcc))->where('finance_edit_permissions.user_id', \Auth::user()->id)->get();
                        }
                    }else{
                        $form_data = $form_data->get();
                    }
                    
                }
                   
                    $finalArr = array();
                    $finalArrExt = array();
                    if(!$form_data->isEmpty()) {
                        
                        foreach($form_data as $key=>$form){
                            $allotted_data = collect();
                        if(\Auth::user()->role_type=='1'){
                            $allotted_data = \DB::table('user_edit_permissions as up')->select('up.form_id', 'up.module_id', 'up.user_id', 'up.update_status', 'up.update_status_details')->where('form_id', $form->id)->where('is_active', 1)->where('user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='2'){
                            $allotted_data = \DB::table('legal_edit_permissions as up')->select('up.form_id', 'up.module_id', 'up.user_id', 'up.update_status', 'up.update_status_details')->where('form_id', $form->id)->where('is_active', 1)->where('user_id', \Auth::user()->id)->get();
                        }elseif(\Auth::user()->role_type=='3'){
                            $allotted_data = \DB::table('finance_edit_permissions as up')->select('up.form_id', 'up.module_id', 'up.user_id', 'up.update_status', 'up.update_status_details')->where('form_id', $form->id)->where('is_active', 1)->where('user_id', \Auth::user()->id)->get();
                        }
                            
                            $finalArr['allotted_data'] = array();
                            if(!$allotted_data->isEmpty()) {
                                
                                $arr = array();
                                $i = 0;
                                foreach($allotted_data as $key=>$value) {
                                    $arr[$i]['module_id'] = $value->module_id;
                                    $arr[$i]['user_name'] = getModelById('User',$value->user_id)->name;
                                    $arr[$i]['update_status'] = $value->update_status;
                                    $i++;
                                }
                                $finalArr['allotted_data'] = $arr;
                            }
                                 
                            $finalArr['form_no'] = $form->form_no;
                            $finalArr['created_by'] = getModelById('User',$form->created_by_id)->name;
                            $finalArr['mcc_name'] = $form->mcc_name;
                            $finalArr['mpp_name'] = $form->mpp_name;
                            $finalArr['approval_status'] = $form->approval_status;
                            $finalArr['ao_approval_status'] = $form->ao_approval_status;
                            $finalArr['lo_approval_status'] = $form->lo_approval_status;
                            $finalArr['fo_approval_status'] = $form->fo_approval_status;
                            $finalArr['final_approval_status'] = $form->final_approval_status;
                            $finalArr['remark'] = $form->remark;
                            
                            $finalArr['final_approved_by'] = '';
                            if(!is_null($form->final_approved_by)) {
                                $finalArr['final_approved_by'] = getModelById('User',$form->final_approved_by)->name;    
                            }
                            
                            array_push($finalArrExt, $finalArr);
                        }
                        
                    }
                    $data['form'] = $finalArrExt;
                }
            }
	   	}
		return view('admin.report.user-form-report',$data);
	}

public function animal_form_report(Request $request)
    {

if ($request->isMethod('post')) {
                $request->validate([
                'mcc_name' => 'required',
            ]);

$data['page'] = 'Animal Form Report';
            $data['mcc'] = \DB::table('mcc')->where('is_active', 1)->get();
            $data['mpps'] = \DB::table('mpp')->where('is_active', 1)->get();
            
            // Check if mcc_name is present, if not set it to null or handle accordingly
            $mccCode = $request->has('mcc_name') ? $request->mcc_name : null;
            $mppCode = $request->has('mpp_name') ? $request->mpp_name : null;
            $fromDate = $request->has('from_date') ? $request->from_date : null;
            $toDate = $request->has('to_date') ? $request->to_date : null;
            $isOld = $request->has('is_old') ? $request->is_old : null;

// Set default data for the view
            $data['mccCode'] = $mccCode;
            $data['mppCode'] = 0; // Set mppCode to 0 if not provided
            $data['fromDate'] = $fromDate;
            $data['toDate'] = $toDate;
            $data['isOld'] = $isOld;

$data['animal_data'] = [];

// Check if the member is from old data (is_old == 1)
            if ($isOld != 1) {
                // Prepare the query to fetch member details from membership_details
                $memberDetailsQuery = \DB::table('membership_details')
                    ->select('aadhar_card')
                    ->where('is_active', 1);
                
                // Add mcc_code condition if present
                if ($mccCode) {
                    $memberDetailsQuery->where('mcc_code', $mccCode);
                }

// Add mpp_code condition if present
                if ($mppCode) {
                    $memberDetailsQuery->where('mpp_code', $mppCode);
                }

// Execute the query and fetch member details
                $member_details = $memberDetailsQuery->get();

// Loop through member details to get animal data
                foreach ($member_details as $member) {
                    $animal_details = \DB::table('animal_details')
                        ->select('member_aadhar', \DB::raw('COUNT(*) as total_animals'))
                        ->whereNotNull('virtual_tag_number')
                        ->where('member_aadhar', $member->aadhar_card)
                        ->where('is_active', 1);

// Apply date filtering only if both $fromDate and $toDate are provided
                    if (!empty($fromDate) && !empty($toDate)) {
                    $animal_details->whereBetween(DB::raw('date(created_at)'), [$fromDate, $toDate]);
                }

// Group by member_aadhar and get the results
                    $animal_details = $animal_details->groupBy('member_aadhar')->get();

foreach ($animal_details as $detail) {
                        $animal_detail_1 = \DB::table('animal_details')
                            ->whereNotNull('virtual_tag_number')
                            ->where('is_active', 1)
                            ->where('member_aadhar', $detail->member_aadhar)
                            ->get();

// Append the data for each member_aadhar
                        $data['animal_data'][] = [
                            'member_aadhar' => $detail->member_aadhar,
                            'total_animals' => $detail->total_animals,
                            'animal_details' => $animal_detail_1
                        ];
                    }
                }
            } else {
                // Prepare the query to fetch member details from membership_details
                $memberDetailsQuery = \DB::table('legacy_data')
                    ->select('aadhar_no')
                    ->where('is_active', 1);
                
                // Add mcc_code condition if present
                if ($mccCode) {
                    $memberDetailsQuery->where('mcc_code', $mccCode);
                }

// Add mpp_code condition if present
                if ($mppCode) {
                    $memberDetailsQuery->where('mpp_code', $mppCode);
                }

// Execute the query and fetch member details
                $member_details = $memberDetailsQuery->get();

// Loop through member details to get animal data
                foreach ($member_details as $member) {
                    $animal_details = \DB::table('animal_details')
                        ->select('member_aadhar', \DB::raw('COUNT(*) as total_animals'))
                        ->whereNotNull('virtual_tag_number')
                        ->where('member_aadhar', $member->aadhar_no)
                        ->where('is_active', 1);

// Group by member_aadhar and get the results
                    $animal_details = $animal_details->groupBy('member_aadhar')->get();

return view('admin.report.animal-form-report', $data);
        }

$data['page'] = 'Animal Form Report';
        $data['mcc'] = \DB::table('mcc')->where('is_active', 1)->get();
        $data['mpps'] = \DB::table('mpp')->where('is_active', 1)->get();

// Get the aggregated result
        $animal_details = \DB::table('animal_details')
            ->select('member_aadhar', \DB::raw('COUNT(*) as total_animals'))
            ->whereNotNull('virtual_tag_number')
            ->where('is_active', 1)
            ->groupBy('member_aadhar')
            ->get();

// Initialize animal_data as an empty array
        $data['animal_data'] = [];

foreach ($animal_details as $detail) {
            // Get detailed records for each member_aadhar
            $animal_detail_1 = \DB::table('animal_details')
                ->whereNotNull('virtual_tag_number')
                ->where('is_active', 1)
                ->where('member_aadhar', $detail->member_aadhar)
                ->get();

return view('admin.report.animal-form-report', $data);
    }
	
	public function getUserByPibuser(Request $request){
        $response = array();
        $user_id = \DB::table('membership_details')->where('created_by_id', $request->user_id)->where('is_active', 1)->get();
        if(count($user_id)>0){
        foreach($user_id as $k=>$user ){
            $pib  = \DB::table('user_edit_permissions')->where('user_id', $user->id)->where('is_active', 1)->get();
            if (count($pib)>0) {
                foreach($pib as $p=>$users){
                $pibuser  = \DB::table('users')->where('id', $users->user_id)->where('is_active', 1)->get();
                    $response['flag'] = true;
                    $response['userid'] = $user_id;
                    $response['pibuser'] = $pibuser ;
                }
            }else {
                    $response['flag'] = false;
                    $response['message'] = "PIB Officer Not Found !";
                }
        }
        }else{
                $response['flag'] = false;
                $response['message'] = "Form Not Create !";
        }
                
        return response()->json($response);

}
    public function userFormExcelReport(Request $request){
        return Excel::download(new UserFormExport($request), 'user_form_report.xlsx');
    }

public function animalFormExcelReport(Request $request){

// dd($request->all());
        $isOld = $request->is_old_member_export ?? null;

if (!empty($request->mcc_name_export) || !empty($request->mpp_name_export) || !empty($request->from_date_export) || !empty($request->to_date_export) || $isOld !== null) {
            $mccCode = $request->mcc_name_export ?? null;
            $mppCode = $request->mpp_name_export ?? null;

// Ensure dates are in the correct format
            $fromDate = !empty($request->from_date_export) ? Carbon::parse($request->from_date_export)->startOfDay() : null;
            $toDate = !empty($request->to_date_export) ? Carbon::parse($request->to_date_export)->endOfDay() : null;

$data['animal_data'] = [];

// Start building member query
            if ($isOld != 1) {
                $memberDetailsQuery = \DB::table('membership_details')
                ->select('aadhar_card')
                ->where('is_active', 1);
            }
            if ($isOld == 1) {
                $memberDetailsQuery = \DB::table('legacy_data')
                ->select('aadhar_no')
                ->where('is_active', 1);
            }

if ($mccCode) {
                $memberDetailsQuery->where('mcc_code', $mccCode);
            }

if ($mppCode) {
                $memberDetailsQuery->where('mpp_code', $mppCode);
            }

// Fetch member details only if $isOld is not 1
            if ($isOld != 1) {
                $member_details = $memberDetailsQuery->get();
                foreach ($member_details as $member) {
                    $animal_details = \DB::table('animal_details')
                        ->select('member_aadhar', \DB::raw('COUNT(*) as total_animals'))
                        ->whereNotNull('virtual_tag_number')
                        ->where('member_aadhar', $member->aadhar_card)
                        ->where('is_active', 1);

if ($fromDate && $toDate) {
                        $animal_details->whereBetween('created_at', [$fromDate, $toDate]);
                    }

$animal_details = $animal_details->groupBy('member_aadhar')->get();

$data['animal_data'][] = [
                            'member_aadhar' => $detail->member_aadhar,
                            'total_animals' => $detail->total_animals,
                            'animal_details' => $animal_detail_1,
                        ];
                    }
                }
            } else {
                $member_details = $memberDetailsQuery->get();
                foreach ($member_details as $member) {
                    $animal_details = \DB::table('animal_details')
                        ->select('member_aadhar', \DB::raw('COUNT(*) as total_animals'))
                        ->whereNotNull('virtual_tag_number')
                        ->where('member_aadhar', $member->aadhar_no)
                        ->where('is_active', 1);

if ($fromDate && $toDate) {
                        $animal_details->whereBetween('created_at', [$fromDate, $toDate]);
                    }

$animal_details = $animal_details->groupBy('member_aadhar')->get();

$data['animal_data'][] = [
                            'member_aadhar' => $detail->member_aadhar,
                            'total_animals' => $detail->total_animals,
                            'animal_details' => $animal_detail_1,
                        ];
                    }
                }
            }

return Excel::download(new AnimalFormExport($data['animal_data']), 'animal_form_report.xlsx');
        }

$animal_details = \DB::table('animal_details')
            ->select('member_aadhar', \DB::raw('COUNT(*) as total_animals'))
            ->whereNotNull('virtual_tag_number')
            ->where('is_active', 1)
            ->groupBy('member_aadhar')
            ->get();

// Initialize animal_data as an empty array
        $data['animal_data'] = [];

// Append data for each member_aadhar
            $data['animal_data'][] = [
                'member_aadhar' => $detail->member_aadhar,
                'total_animals' => $detail->total_animals,
                'animal_details' => $animal_detail_1
            ];
        }
        return Excel::download(new AnimalFormExport($data['animal_data']), 'animal_form_report.xlsx');
    }
    
    public function userPendingFormExcelReport(Request $request){
        return Excel::download(new UserPendingFormExport($request), 'user_pending_form_report.xlsx');
    }
    
    public function userRejectedFormExcelReport(Request $request){
        return Excel::download(new UserRejectedFormExport($request), 'user_rejected_form_report.xlsx');
    }
    
    public function userApprovedFormExcelReport(Request $request){
        return Excel::download(new UserApprovedFormExport($request), 'user_approved_form_report.xlsx');
    }

public function memberExcelReport(Request $request){
        return Excel::download(new memberExport($request), 'member_report.xlsx');
    }
	
}