<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Exports\ClaimExports;
use Maatwebsite\Excel\Facades\Excel;
use DB;


class ClaimReportController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        //
    }

    public function reportSummary() 
    {
        return view('pages.claims.reportSummary');
    }

    public function getReportSummary(Request $request)
    {
        $report_type = $request->report_type;
        if($report_type == 1) {
            // All report
            // $data = DB::table('claims_document_status')->selectRaw("claim_category AS type, COUNT(claim_category) AS total")
            // // ->whereRaw("DATE(created_at) BETWEEN '$from_date' AND '$to_date'")
            // ->groupByRaw("claim_category")
            // ->orderBy('claim_category','asc')
            // ->get();
            $data = DB::table("claims_document_status")
                    ->selectRaw(
                        "claim_category AS type, COUNT(claim_category) AS total, SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending, SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing, SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done, SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected, SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                    )
                    ->groupByRaw("claim_category")
                    ->orderBy('claim_category','asc')
                    ->get();
            $html = "";
            $count = 1;
            if(count($data) == 0) {
                $html .= '';
            } 
            else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->type."</td>";
                    $html .= "<td><a href='summarydetails/$a->type/1'>".$a->pending."</a></td>";
                    $html .= "<td><a href='summarydetails/$a->type/2'>".$a->processing."</a></td>";
                    $html .= "<td><a href='summarydetails/$a->type/4'>".$a->rejected."</a></td>";
                    $html .= "<td><a href='summarydetails/$a->type/3'>".$a->done."</a></td>";
                    $html .= "<td><a href='summarydetails/$a->type/5'>".$a->partial_amount_paid."</a></td>";
                    $html .= "<td><a href='summarydetails/$a->type/All'>".$a->total."</a></td>";
                    $html .= "</tr>";
                    $count++;
                }
                // echo $html;
            }
            echo $html;
            exit;
        }
        if($report_type == 2) {
            // for date wise
            $from_date = $request->from_date;
            $to_date = $request->to_date;
            
            // $data = DB::table('claims_document_status')->selectRaw("DATE(created_at) as create_date, COUNT(DATE(created_at)) as total")
            // ->whereRaw("DATE(created_at) BETWEEN '$from_date' AND '$to_date'")
            // ->groupByRaw("DATE(created_at)")
            // ->orderBy("create_date","desc")
            // ->get();

            $data = DB::table("claims_document_status")
                    ->selectRaw(
                        "DATE(created_at) AS type, COUNT(claim_category) AS total,
                        SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                        SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                        SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                        SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                        SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                    )
                    ->whereRaw("DATE(created_at) BETWEEN '$from_date' AND '$to_date'")
                    ->groupByRaw("DATE(created_at)")
                    ->orderBy("type","desc")
                    ->get();
            // echo $data;exit;
            $html = "";
            $count = 1;
            if(count($data) == 0) {
                $html .= '';
            } 
            else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->type."</td>";
                    $html .= "<td>".$a->pending."</td>";
                    $html .= "<td>".$a->processing."</td>";
                    $html .= "<td>".$a->rejected."</td>";
                    $html .= "<td>".$a->done."</td>";
                    $html .= "<td>".$a->partial_amount_paid."</td>";
                    $html .= "<td>".$a->total."</td>";
                    $html .= "</tr>";
                    $count++;
                }
                // echo $html;
            }
            echo $html;
            exit;
        }
        if($report_type == 3) {
            // for month wise
            $from_month = $request->from_month;
            $to_month = $request->to_month;
            // $data = DB::table('claims_document_status')->selectRaw("DATE_FORMAT(created_at, '%M-%Y') AS month1, COUNT(claim_category) AS total")
            // ->whereRaw("DATE_FORMAT(created_at,'%Y-%m') BETWEEN '$from_month' AND '$to_month'")
            // ->groupBy("month1")
            // ->orderBy("month1",'asc')
            // ->get();
            
            $data = DB::table("claims_document_status")
                    ->selectRaw(
                        "DATE_FORMAT(created_at, '%M-%Y') AS type, COUNT(claim_category) AS total,
                        SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                        SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                        SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                        SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                        SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                    )
                    ->whereRaw("DATE_FORMAT(created_at,'%Y-%m') BETWEEN '$from_month' AND '$to_month'")
                    ->groupBy("type")
                    ->orderBy("type",'asc')
                    ->get();

            $html = "";
            $count = 1;
            if(count($data) == 0) {
                $html .= '';
            } 
            else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->type."</td>";
                    $html .= "<td>".$a->pending."</td>";
                    $html .= "<td>".$a->processing."</td>";
                    $html .= "<td>".$a->rejected."</td>";
                    $html .= "<td>".$a->done."</td>";
                    $html .= "<td>".$a->partial_amount_paid."</td>";
                    $html .= "<td>".$a->total."</td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }
        if($report_type == 4) {
            // Pending Wise Report
            $claim_for = "";
            $claim_status = 1;
            if($request->category == 2) {
                $claim_for = "EPF";
            } 
            if($request->category == 3) {
                $claim_for = "ESI";
            } 
            if($request->category == 4) {
                $claim_for = "WC";
            } 
            if($request->category == 5) {
                $claim_for = "GPA";
            } 
            if(!empty($claim_for)) {
                // $data = DB::table('claims_document_status')->selectRaw("claim_category AS type, COUNT(claim_category) AS total")
                // ->whereRaw("claim_category = '$claim_for' AND claim_status = '$claim_status'")
                // ->groupBy("claim_category")
                // ->get();
                
                $data = DB::table("claims_document_status")
                ->selectRaw(
                    "claim_category AS type, COUNT(claim_category) AS total,
                    SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                    SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                    SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                    SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                    SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                )
                ->whereRaw("claim_category = '$claim_for' AND claim_status = '$claim_status'")
                ->groupBy("type")
                // ->orderBy("type",'asc')
                ->get();
                
            } else {
                // $data = DB::table('claims_document_status')->selectRaw("claim_category AS type, COUNT(claim_category) AS total")
                // ->whereRaw("claim_status = '$claim_status'")
                // ->groupBy("claim_category")
                // ->get();

                $data = DB::table("claims_document_status")
                ->selectRaw(
                    "claim_category AS type, COUNT(claim_category) AS total,
                    SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                    SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                    SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                    SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                    SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                )
                ->whereRaw("claim_status = '$claim_status'")
                ->groupBy("type")
                // ->orderBy("type",'asc')
                ->get();
            }
           
            $html = "";
            $count = 1;
            if(count($data) == 0) {
                $html .= '';
            } 
            else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->type."</td>";
                    $html .= "<td>".$a->pending."</td>";
                    $html .= "<td>".$a->processing."</td>";
                    $html .= "<td>".$a->rejected."</td>";
                    $html .= "<td>".$a->done."</td>";
                    $html .= "<td>".$a->partial_amount_paid."</td>";
                    $html .= "<td>".$a->total."</td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }

        if($report_type == 5) {
            // Processing Wise Report
            $claim_for = "";
            $claim_status = 2;
            if($request->category == 2) {
                $claim_for = "EPF";
            } 
            if($request->category == 3) {
                $claim_for = "ESI";
            } 
            if($request->category == 4) {
                $claim_for = "WC";
            } 
            if($request->category == 5) {
                $claim_for = "GPA";
            } 
            if(!empty($claim_for)) {
                // $data = DB::table('claims_document_status')->selectRaw("claim_category AS type, COUNT(claim_category) AS total")
                // ->whereRaw("claim_category = '$claim_for' AND claim_status = '$claim_status'")
                // ->groupBy("claim_category")
                // ->get();

                $data = DB::table("claims_document_status")
                ->selectRaw(
                    "claim_category AS type, COUNT(claim_category) AS total,
                    SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                    SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                    SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                    SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                    SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                )
                ->whereRaw("claim_category = '$claim_for' AND claim_status = '$claim_status'")
                ->groupBy("type")
                // ->orderBy("type",'asc')
                ->get();
            } else {
                // $data = DB::table('claims_document_status')->selectRaw("claim_category AS type, COUNT(claim_category) AS total")
                // ->whereRaw("claim_status = '$claim_status'")
                // ->groupBy("claim_category")
                // ->get();
                $data = DB::table("claims_document_status")
                ->selectRaw(
                    "claim_category AS type, COUNT(claim_category) AS total,
                    SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                    SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                    SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                    SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                    SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                )
                ->whereRaw("claim_status = '$claim_status'")
                ->groupBy("type")
                // ->orderBy("type",'asc')
                ->get();
            }
           
            $html = "";
            $count = 1;
            if(count($data) == 0) {
                $html .= '';
            } 
            else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->type."</td>";
                    $html .= "<td>".$a->pending."</td>";
                    $html .= "<td>".$a->processing."</td>";
                    $html .= "<td>".$a->rejected."</td>";
                    $html .= "<td>".$a->done."</td>";
                    $html .= "<td>".$a->partial_amount_paid."</td>";
                    $html .= "<td>".$a->total."</td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }
        if($report_type == 6) {
            // Close/Done Report
            $claim_for = "";
            $claim_status = 3;
            if($request->category == 2) {
                $claim_for = "EPF";
            } 
            if($request->category == 3) {
                $claim_for = "ESI";
            } 
            if($request->category == 4) {
                $claim_for = "WC";
            } 
            if($request->category == 5) {
                $claim_for = "GPA";
            } 
            if(!empty($claim_for)) {
                // $data = DB::table('claims_document_status')->selectRaw("claim_category AS type, COUNT(claim_category) AS total")
                // ->whereRaw("claim_category = '$claim_for' AND claim_status = '$claim_status'")
                // ->groupBy("claim_category")
                // ->get();
                $data = DB::table("claims_document_status")
                ->selectRaw(
                    "claim_category AS type, COUNT(claim_category) AS total,
                    SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                    SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                    SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                    SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                    SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                )
                ->whereRaw("claim_category = '$claim_for' AND claim_status = '$claim_status'")
                ->groupBy("type")
                // ->orderBy("type",'asc')
                ->get();
            } else {
                // $data = DB::table('claims_document_status')->selectRaw("claim_category AS type, COUNT(claim_category) AS total")
                // ->whereRaw("claim_status = '$claim_status'")
                // ->groupBy("claim_category")
                // ->get();

                $data = DB::table("claims_document_status")
                ->selectRaw(
                    "claim_category AS type, COUNT(claim_category) AS total,
                    SUM(CASE WHEN claim_status = 1 THEN 1 ELSE 0 END) AS pending,
                    SUM(CASE WHEN claim_status = 2 THEN 1 ELSE 0 END) AS processing,
                    SUM(CASE WHEN claim_status = 3 THEN 1 ELSE 0 END) AS done,
                    SUM(CASE WHEN claim_status = 4 THEN 1 ELSE 0 END) AS rejected,
                    SUM(CASE WHEN claim_status = 5 THEN 1 ELSE 0 END) AS partial_amount_paid"
                )
                ->whereRaw("claim_status = '$claim_status'")
                ->groupBy("type")
                // ->orderBy("type",'asc')
                ->get();
            }
           
            $html = "";
            $count = 1;
            if(count($data) == 0) {
                $html .= '';
            } 
            else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->type."</td>";
                    $html .= "<td>".$a->pending."</td>";
                    $html .= "<td>".$a->processing."</td>";
                    $html .= "<td>".$a->rejected."</td>";
                    $html .= "<td>".$a->done."</td>";
                    $html .= "<td>".$a->partial_amount_paid."</td>";
                    $html .= "<td>".$a->total."</td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }

    }

    public function detailedClaimReport() 
    {
        return view('pages.claims.detailedReportSummary');
    }

    public function getdetailedClaimReport(Request $request)    
    {
        $report_type = $request->report_type;
        if($report_type == 1) {
            // All Report
            $data = DB::table('emp_claim_master')->selectRaw("*")
            ->orderBy('claim_id','desc')
            ->get();
            foreach ($data as $key => $value) {
                $claims_for = DB::table('claims_document_status')
                    ->selectRaw("claim_category,claim_status,claim_by,
                        CASE
                            WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                            WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                            WHEN claims_document_status.claim_status = 3 THEN 'Done'
                            WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                            WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
                        END AS claim_text_status
                        ")
                    ->where('claim_id', $value->claim_id)->get();
                $value->claim_applied_for = $claims_for;
            }
            $html = "";
            $count = 1;

            if(count($data) == 0) {
                $html .= '';
            } else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->claim_master_id."</td>";
                    $html .= "<td>".$a->emp_id."</td>";
                    $html .= "<td>".$a->emp_name."</td>";
                    $html .= "<td>".$a->emp_mobile."</td>";
                    if($a->claim_type == '1') {
                        $html .= "<td>Death</td>";
                    }
                    if($a->claim_type == '2') {
                        $html .= "<td>Accident</td>";
                    }
                    $span = "";
                    $claim_by = "";
                    if($a->claim_applied_for->count() > 0) {
                        foreach($a->claim_applied_for as $items) {
                            $span .= '<span class="">'. $items->claim_category.' ('.$items->claim_text_status.' )</span><br>';
                            $claim_by = $items->claim_by;
                        }
                    } else {
                        $span .= '<span class="">EPF (Not Applied)</span><br>
                        <span class="">ESI (Not Applied)</span><br>
                        <span class="">WC (Not Applied)</span><br>
                        <span class="">GPA (Not Applied)</span>';
                    }

                    $html .= "<td style='width:130px;'>".$span."</td>";
                    $html .= "<td>".$claim_by."</td>";
                    $html .= "<td>".$a->accident_info."</td>";

                    $link = route('view.claim.details',$a->claim_id);
                    $html .= "<td>
                                <a href='".$link."' class='btn btn-sm btn-secondary' target='_blank'>
                                    <i class='fa fa-eye'> View</i>
                                </a>
                            </td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }
        if($report_type == 2) {
            // Date wise Report
            $from_date = $request->from_date;
            $to_date = $request->to_date;
           
            $data = DB::table('emp_claim_master')->selectRaw("*")
            ->whereRaw("DATE(created_at) BETWEEN '$from_date' AND '$to_date'")
            ->get();
            foreach ($data as $key => $value) {
                $claims_for = DB::table('claims_document_status')
                    ->selectRaw("claim_category,claim_status,claim_by,
                        CASE
                            WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                            WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                            WHEN claims_document_status.claim_status = 3 THEN 'Done'
                            WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                            WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
                        END AS claim_text_status
                        ")
                    ->where('claim_id', $value->claim_id)->get();
                $value->claim_applied_for = $claims_for;
            }
            $html = "";
            $count = 1;

            if(count($data) == 0) {
                $html .= '';
            } else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->claim_master_id."</td>";
                    $html .= "<td>".$a->emp_id."</td>";
                    $html .= "<td>".$a->emp_name."</td>";
                    $html .= "<td>".$a->emp_mobile."</td>";
                    if($a->claim_type == '1') {
                        $html .= "<td>Death</td>";
                    }
                    if($a->claim_type == '2') {
                        $html .= "<td>Accident</td>";
                    }
                    $span = "";
                    $claim_by = "";
                    if($a->claim_applied_for->count() > 0) {
                        foreach($a->claim_applied_for as $items) {
                            $span .= '<span class="">'. $items->claim_category.' ('.$items->claim_text_status.' )</span><br>';
                            $claim_by = $items->claim_by;
                        }
                    } else {
                        $span .= '<span class="">EPF (Not Applied)</span><br>
                        <span class="">ESI (Not Applied)</span><br>
                        <span class="">WC (Not Applied)</span><br>
                        <span class="">GPA (Not Applied)</span>';
                    }

                    $html .= "<td style='width:130px;'>".$span."</td>";
                    $html .= "<td>".$claim_by."</td>";
                    $html .= "<td>".$a->accident_info."</td>";

                    $link = route('view.claim.details',$a->claim_id);
                    $html .= "<td>
                                <a href='".$link."' class='btn btn-sm btn-secondary' target='_blank'>
                                    <i class='fa fa-eye'> View</i>
                                </a>
                            </td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }

        if($report_type == 3) {
            // Month wise Report
            $from_month = $request->from_month;
            $to_month = $request->to_month;
           
            $data = DB::table('emp_claim_master')->selectRaw("*")
            ->whereRaw("DATE_FORMAT(created_at,'%Y-%m') BETWEEN '$from_month' AND '$to_month'")
            ->get();
            foreach ($data as $key => $value) {
                $claims_for = DB::table('claims_document_status')
                    ->selectRaw("claim_category,claim_status,claim_by,
                        CASE
                            WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                            WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                            WHEN claims_document_status.claim_status = 3 THEN 'Done'
                            WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                            WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
                        END AS claim_text_status
                        ")
                    ->where('claim_id', $value->claim_id)->get();
                $value->claim_applied_for = $claims_for;
            }
            $html = "";
            $count = 1;

            if(count($data) == 0) {
                $html .= '';
            } else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->claim_master_id."</td>";
                    $html .= "<td>".$a->emp_id."</td>";
                    $html .= "<td>".$a->emp_name."</td>";
                    $html .= "<td>".$a->emp_mobile."</td>";
                    if($a->claim_type == '1') {
                        $html .= "<td>Death</td>";
                    }
                    if($a->claim_type == '2') {
                        $html .= "<td>Accident</td>";
                    }
                    $span = "";
                    $claim_by = "";
                    if($a->claim_applied_for->count() > 0) {
                        foreach($a->claim_applied_for as $items) {
                            $span .= '<span class="">'. $items->claim_category.' ('.$items->claim_text_status.' )</span><br>';
                            $claim_by = $items->claim_by;
                        }
                    } else {
                        $span .= '<span class="">EPF (Not Applied)</span><br>
                        <span class="">ESI (Not Applied)</span><br>
                        <span class="">WC (Not Applied)</span><br>
                        <span class="">GPA (Not Applied)</span>';
                    }
                    $html .= "<td style='width:130px;'>".$span."</td>";
                    $html .= "<td>".$claim_by."</td>";
                    $html .= "<td>".$a->accident_info."</td>";

                    $link = route('view.claim.details',$a->claim_id);
                    $html .= "<td>
                                <a href='".$link."' class='btn btn-sm btn-secondary' target='_blank'>
                                    <i class='fa fa-eye'> View</i>
                                </a>
                            </td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }
        
        if($report_type == 4) {
            // Pending wise Report
            $claim_for = "";
            $claim_status = 1;
            if($request->category == 2) {
                $claim_for = "EPF";
            } 
            if($request->category == 3) {
                $claim_for = "ESI";
            } 
            if($request->category == 4) {
                $claim_for = "WC";
            } 
            if($request->category == 5) {
                $claim_for = "GPA";
            } 
            if(!empty($claim_for)) {
                $data = DB::table('emp_claim_master')->selectRaw("*")
                ->leftJoin("claims_document_status","claims_document_status.claim_id","=","emp_claim_master.claim_id")
                ->whereRaw("claims_document_status.claim_category = '$claim_for' AND claims_document_status.claim_status = '$claim_status'")
                ->get();
            } else {
                $data = DB::table('emp_claim_master')->selectRaw("*")
                ->leftJoin("claims_document_status","claims_document_status.claim_id","=","emp_claim_master.claim_id")
                ->whereRaw("claims_document_status.claim_status = '$claim_status'")
                ->get();
            }
          
            foreach ($data as $key => $value) {
                $claims_for = DB::table('claims_document_status')
                    ->selectRaw("claim_category,claim_status,claim_by,
                        CASE
                            WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                            WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                            WHEN claims_document_status.claim_status = 3 THEN 'Done'
                            WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                            WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
                        END AS claim_text_status
                        ")
                    ->where('claim_id', $value->claim_id)->get();
                $value->claim_applied_for = $claims_for;
            }
            $html = "";
            $count = 1;

            if(count($data) == 0) {
                $html .= '';
            } else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->claim_master_id."</td>";
                    $html .= "<td>".$a->emp_id."</td>";
                    $html .= "<td>".$a->emp_name."</td>";
                    $html .= "<td>".$a->emp_mobile."</td>";
                    if($a->claim_type == '1') {
                        $html .= "<td>Death</td>";
                    }
                    if($a->claim_type == '2') {
                        $html .= "<td>Accident</td>";
                    }
                    $span = "";
                    $claim_by = "";
                    if($a->claim_applied_for->count() > 0) {
                        foreach($a->claim_applied_for as $items) {
                            $span .= '<span class="">'. $items->claim_category.' ('.$items->claim_text_status.' )</span><br>';
                            $claim_by = $items->claim_by;
                        }
                    } else {
                        $span .= '<span class="">EPF (Not Applied)</span><br>
                        <span class="">ESI (Not Applied)</span><br>
                        <span class="">WC (Not Applied)</span><br>
                        <span class="">GPA (Not Applied)</span>';
                    }

                    $html .= "<td style='width:130px;'>".$span."</td>";
                    $html .= "<td>".$claim_by."</td>";
                    $html .= "<td>".$a->accident_info."</td>";

                    $link = route('view.claim.details',$a->claim_id);
                    $html .= "<td>
                                <a href='".$link."' class='btn btn-sm btn-secondary' target='_blank'>
                                    <i class='fa fa-eye'> View</i>
                                </a>
                            </td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }
        if($report_type == 5) {
            // Processing wise Report
            $claim_for = "";
            $claim_status = 2;
            if($request->category == 2) {
                $claim_for = "EPF";
            } 
            if($request->category == 3) {
                $claim_for = "ESI";
            } 
            if($request->category == 4) {
                $claim_for = "WC";
            } 
            if($request->category == 5) {
                $claim_for = "GPA";
            } 
            if(!empty($claim_for)) {
                $data = DB::table('emp_claim_master')->selectRaw("*")
                ->leftJoin("claims_document_status","claims_document_status.claim_id","=","emp_claim_master.claim_id")
                ->whereRaw("claims_document_status.claim_category = '$claim_for' AND claims_document_status.claim_status = '$claim_status'")
                ->get();
            } else {
                $data = DB::table('emp_claim_master')->selectRaw("*")
                ->leftJoin("claims_document_status","claims_document_status.claim_id","=","emp_claim_master.claim_id")
                ->whereRaw("claims_document_status.claim_status = '$claim_status'")
                ->get();
            }
          
            foreach ($data as $key => $value) {
                $claims_for = DB::table('claims_document_status')
                    ->selectRaw("claim_category,claim_status,claim_by,
                        CASE
                            WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                            WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                            WHEN claims_document_status.claim_status = 3 THEN 'Done'
                            WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                            WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
                        END AS claim_text_status
                        ")
                    ->where('claim_id', $value->claim_id)->get();
                $value->claim_applied_for = $claims_for;
            }
            $html = "";
            $count = 1;

            if(count($data) == 0) {
                $html .= '';
            } else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->claim_master_id."</td>";
                    $html .= "<td>".$a->emp_id."</td>";
                    $html .= "<td>".$a->emp_name."</td>";
                    $html .= "<td>".$a->emp_mobile."</td>";
                    if($a->claim_type == '1') {
                        $html .= "<td>Death</td>";
                    }
                    if($a->claim_type == '2') {
                        $html .= "<td>Accident</td>";
                    }
                    $span = "";
                    $claim_by = "";
                    if($a->claim_applied_for->count() > 0) {
                        foreach($a->claim_applied_for as $items) {
                            $span .= '<span class="">'. $items->claim_category.' ('.$items->claim_text_status.' )</span><br>';
                            $claim_by = $items->claim_by;
                        }
                    } else {
                        $span .= '<span class="">EPF (Not Applied)</span><br>
                        <span class="">ESI (Not Applied)</span><br>
                        <span class="">WC (Not Applied)</span><br>
                        <span class="">GPA (Not Applied)</span>';
                    }

                    $html .= "<td style='width:130px;'>".$span."</td>";
                    $html .= "<td>".$claim_by."</td>";
                    $html .= "<td>".$a->accident_info."</td>";

                    $link = route('view.claim.details',$a->claim_id);
                    $html .= "<td>
                                <a href='".$link."' class='btn btn-sm btn-secondary' target='_blank'>
                                    <i class='fa fa-eye'> View</i>
                                </a>
                            </td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }
        if($report_type == 6) {
            // Close/Done wise Report
            $claim_for = "";
            $claim_status = 3;
            if($request->category == 2) {
                $claim_for = "EPF";
            } 
            if($request->category == 3) {
                $claim_for = "ESI";
            } 
            if($request->category == 4) {
                $claim_for = "WC";
            } 
            if($request->category == 5) {
                $claim_for = "GPA";
            } 
            if(!empty($claim_for)) {
                $data = DB::table('emp_claim_master')->selectRaw("*")
                ->leftJoin("claims_document_status","claims_document_status.claim_id","=","emp_claim_master.claim_id")
                ->whereRaw("claims_document_status.claim_category = '$claim_for' AND claims_document_status.claim_status = '$claim_status'")
                ->get();
            } else {
                $data = DB::table('emp_claim_master')->selectRaw("*")
                ->leftJoin("claims_document_status","claims_document_status.claim_id","=","emp_claim_master.claim_id")
                ->whereRaw("claims_document_status.claim_status = '$claim_status'")
                ->get();
            }
          
            foreach ($data as $key => $value) {
                $claims_for = DB::table('claims_document_status')
                    ->selectRaw("claim_category,claim_status,claim_by,
                        CASE
                            WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                            WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                            WHEN claims_document_status.claim_status = 3 THEN 'Done'
                            WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                            WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
                        END AS claim_text_status
                        ")
                    ->where('claim_id', $value->claim_id)->get();
                $value->claim_applied_for = $claims_for;
            }
            $html = "";
            $count = 1;

            if(count($data) == 0) {
                $html .= '';
            } else {
                foreach($data as $a) {
                    $html .= "<tr>";
                    $html .= "<td>".$count."</td>";
                    $html .= "<td>".$a->claim_master_id."</td>";
                    $html .= "<td>".$a->emp_id."</td>";
                    $html .= "<td>".$a->emp_name."</td>";
                    $html .= "<td>".$a->emp_mobile."</td>";
                    if($a->claim_type == '1') {
                        $html .= "<td>Death</td>";
                    }
                    if($a->claim_type == '2') {
                        $html .= "<td>Accident</td>";
                    }
                    $span = "";
                    $claim_by = "";
                    if($a->claim_applied_for->count() > 0) {
                        foreach($a->claim_applied_for as $items) {
                            $span .= '<span class="">'. $items->claim_category.' ('.$items->claim_text_status.' )</span><br>';
                            $claim_by = $items->claim_by;
                        }
                    } else {
                        $span .= '<span class="">EPF (Not Applied)</span><br>
                        <span class="">ESI (Not Applied)</span><br>
                        <span class="">WC (Not Applied)</span><br>
                        <span class="">GPA (Not Applied)</span>';
                    }

                    $html .= "<td style='width:130px;'>".$span."</td>";
                    $html .= "<td>".$claim_by."</td>";
                    $html .= "<td>".$a->accident_info."</td>";

                    $link = route('view.claim.details',$a->claim_id);
                    $html .= "<td>
                                <a href='".$link."' class='btn btn-sm btn-secondary' target='_blank'>
                                    <i class='fa fa-eye'> View</i>
                                </a>
                            </td>";
                    $html .= "</tr>";
                    $count++;
                }
            }
            echo $html;
        }
    }
    

    public function downloadClaimExcel(Request $request) 
    {
        return Excel::download(new ClaimExports($request->all()), 'Claims-Export.xlsx');
    }
    
    
    
    
    public function reportSummaryDetails($type,$status) 
    {
        $report_type = $type;
        $status = $status;

        if($status == 'All'){
            $claims = DB::table('claims_document_status')
                ->selectRaw("emp_claim_master.emp_id,emp_claim_master.claim_type,emp_claim_master.claim_master_id,emp_claim_master.emp_name,emp_claim_master.emp_mobile,emp_claim_master.aadhar_number,claims_document_status.*,
                CASE
                        WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                        WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                        WHEN claims_document_status.claim_status = 3 THEN 'Done'
                        WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                        WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
                END AS claim_text_status
                ")
                ->join("emp_claim_master","claims_document_status.claim_id","=","emp_claim_master.claim_id")
                ->where('claim_category', $report_type)
                ->get();
        }else{
            $claims = DB::table('claims_document_status')
            ->selectRaw("emp_claim_master.emp_id,emp_claim_master.claim_type,emp_claim_master.claim_master_id,emp_claim_master.emp_name,emp_claim_master.emp_mobile,emp_claim_master.aadhar_number,claims_document_status.*,
            CASE
                    WHEN claims_document_status.claim_status = 1 THEN 'Pending'
                    WHEN claims_document_status.claim_status = 2 THEN 'Processing'
                    WHEN claims_document_status.claim_status = 3 THEN 'Done'
                    WHEN claims_document_status.claim_status = 4 THEN 'Rejected'
                    WHEN claims_document_status.claim_status = 5 THEN 'Partial Amount Paid'
            END AS claim_text_status
            ")
            ->join("emp_claim_master","claims_document_status.claim_id","=","emp_claim_master.claim_id")
            ->where('claim_category', $report_type)
            ->where('claim_status', $status)
            ->get();
        }
                // prx($claims);
                // exit;

        return view('pages.claims.reportSummaryDetails', compact('claims'));
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}
