000

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Illuminate\support\Facades\DB;

class ExportHouseMemberList extends \PhpOffice\PhpSpreadsheet\Cell\StringValueBinder implements FromCollection,WithHeadings
,WithColumnFormatting, WithColumnWidths
{
    
     public function columnWidths(): array
    {
        return [
            'N' => 30,
        ];
    }
    /**
    * @return \Illuminate\Support\Collection
    */
    protected $from_date;
    protected $to_date;
    protected $mc_fn;
    
     function __construct($from_date,$to_date,$mc_fn) {
            // $this->mcc_code = $mcc_code;
            $this->from_date = $from_date;
            $this->to_date = $to_date;
            $this->mc_fn = $mc_fn;
     }
     
    public function collection()
    {
    // if(\Auth::user()->role_id=='1'){
    // $response= \App\MembershipDetail::where('is_active', 1)->where('final_approval_status','Approved')->get();
    // }else{
    //     $response= \App\MembershipDetail::where('created_by_id', \Auth::user()->id)->where('is_active', 1)->where('final_approval_status','Approved')->get();
    // }
    if(\Auth::user()->role_id=='1'){
        // if(!empty($this->from_date) && !empty($this->to_date)){
        // $response= \App\MembershipDetail::where('is_active', 1)->where('final_approval_status','Approved')->whereBetween(DB::raw('date(membership_details.updated_at)'),[$this->from_date,$this->to_date])->get();
        // }elseif(!empty($this->from_date) && empty($this->to_date)){
        //         $response= \App\MembershipDetail::where('is_active', 1)->where('final_approval_status','Approved')->whereBetween(DB::raw('date(membership_details.updated_at)'),[$this->from_date,date('Y-m-d')])->get();
        // }else{
        //         $response= \App\MembershipDetail::where('is_active', 1)->where('final_approval_status','Approved')->get();
        //     }
        $query = \App\MembershipDetail::query();
            if(!empty($this->from_date) && !empty($this->to_date)){
            	$query->whereBetween(DB::raw('date(membership_details.updated_at)'),[$this->from_date,$this->to_date]);
            }
            if(!empty($this->from_date) && empty($to)){
            	$query->whereBetween(DB::raw('date(membership_details.updated_at)'),[$this->from_date,date('Y-m-d')]);
            }
            if($this->mc_fn){
            if($this->mc_fn=='MemberCode'){
            	$query->where('membership_details.member_code','!=',null);
            }
            if($this->mc_fn=='NonMemberCode'){
            	$query->where('membership_details.member_code',null);
            }
            if($this->mc_fn=='FolioNumber'){
            	$query->where('membership_details.folio_number','!=',null);
            }
            if($this->mc_fn=='NonFolioNumber'){
            	$query->where('membership_details.folio_number',null);
            }
            }
            $response = $query->select('membership_details.*','master.mcc_code as mcccode','master.mpp_code as mppcode','master.village_code','master.hamlet_code')->join('master', function($join){
                $join->on('master.mpp_code', 'membership_details.mpp_code');
                $join->on('master.mcc_code', 'membership_details.mcc_code');
            })->where('membership_details.is_active', 1)->where('master.is_active', 1)->where('membership_details.final_approval_status','Approved')->get();
    }else{
        $response= \App\MembershipDetail::select('membership_details.*','master.mpp_name as mppname','master.mpp_code as mppcode','master.village_code','master.hamlet_code')->join('master', function($join){
                $join->on('master.mpp_code', 'membership_details.mpp_code');
                $join->on('master.mcc_code', 'membership_details.mcc_code');
            })->where('membership_details.created_by_id', \Auth::user()->id)->where('membership_details.is_active', 1)->where('master.is_active', 1)->where('membership_details.final_approval_status','Approved')->get();
    }
        $n=0;
        $array=[];
       foreach($response as $key=>$val){
        // $array[$n]['sr_no']=$key+1;
        $array1 = explode(",", $val->relation1);
        $array2 = explode(",", $val->relation2);
        $array3 = explode(",", $val->relation3);
        $array4 = explode(",", $val->relation4);
        $array5 = explode(",", $val->relation5);
        $array6 = explode(",", $val->relation6);
        $new=array();
        if($array1[0]!=''){
            $new[]=$array1;
        }
        if($array2[0]!=''){
            $new[]=$array2;
        }
        if($array3[0]!=''){
            $new[]=$array3;
        }
        if($array4[0]!=''){
            $new[]=$array4;
        }
        if($array5[0]!=''){
            $new[]=$array5;
        }
        if($array6[0]!=''){
            $new[]=$array6;
        }
        // dd($new);
        for ($i = 0; $i < count($new); $i++) {
        $array[$n]['bmc_code']=$val->mcccode;
        $array[$n]['mpp_code']=$val->mppcode;
        $array[$n]['member_code']=$val->member_code;
        if(isset($new[$i][0])){
        $array[$n]['family_member_name']=trim(strtoupper($new[$i][0]));
        $array[$n]['gender']=trim(strtoupper($new[$i][1]));
        $array[$n]['relation']=trim(strtoupper($new[$i][3]));
        }
        if(trim(strtoupper($val->nominee_name))==trim(strtoupper($new[$i][0]))){
            $array[$n]['is_nominee']='TRUE';
        }else{
            $array[$n]['is_nominee']='FALSE';
        }
        // $time = strtotime($val->dob);
        // $newformat = date('Y-m-d',$time);
        // $app_age= date_diff(date_create($newformat), date_create('today'))->y;
        if(trim($new[$i][2])<18){
            $array[$n]['is_minor']='TRUE';
        }else{
            $array[$n]['is_minor']='FALSE';
        }
        // $array[$n]['is_member']=$val->shg_member;
        $array[$n]['is_member']='FALSE';
        if(trim(strtoupper($val->nominee_name))==trim(strtoupper($new[$i][0])) && trim($new[$i][2])<18){
        $array[$n]['guardian_name']=trim(strtoupper($val->guardian_name));
        $array[$n]['age']=$new[$i][2];
        }else{
        $array[$n]['guardian_name']='';
        $array[$n]['age']='';
        }
        
        // $array[$n]['age']=date('d.m.Y',strtotime($val->age));
        // if(isset($new[$i][0])){
        // $array[$n]['age']=$new[$i][2];
        // }else{
        // $array[$n]['age']='';
        // }
        $n++;
       }
    }
    //   return $array; 
      //return $data;
        return collect($array);
    }
    
    public function columnFormats(): array
    {
        return [
            'D' => '0',
        ];
    }
    
    public function headings(): array
     { 
        return [
            // 'Sr. No.',
            // 'Form No.',
            'bmc_code',
            'mpp_code',
            'member_code',
            'family_member_name',
            'gender',
            'relation',
            'is_nominee',
            'is_minor',
            'is_member',
            'guardian_name',
            'age',
        ];
}
        
}