<?php

namespace App\Http\Controllers;

use DB;
use Hash;
use File;
use App\Models\Gender;
use App\Models\Account;
use App\Models\Country;
use App\Models\BloodGroup;
use App\Models\MasterType;
use App\Models\LmsSection;
use App\Models\LmsClasses;
use Illuminate\Support\Arr;
use App\Models\AccountBank;
use App\Models\AccountImage;
use App\Models\StudentHouse;
use Illuminate\Http\Request;
use App\Models\AccountTitle;
use App\Models\MaritalStatus;
use App\Models\CompanyAddress;
use App\Models\AccountLicense;
use App\Models\AccountPayment;
use App\Models\AccountAddress;
use App\Models\AccountContact;
use App\Models\MasterCategory;
use App\Exports\StudentsExport;
use App\Imports\ImportStudent;
use App\Models\LmsClassSection;
use App\Models\AccountingGroup;
use App\Models\StudentAdmission;
use Yajra\DataTables\DataTables;
use App\Exports\StatementsExport;
use App\Models\AccountAttachment;
use Spatie\Permission\Models\Role;
use App\Models\TaxRegisterCategory;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Controller;
use App\Models\AccountRelativesDetail;
use App\Models\FinancialYear;
use App\Models\Language;
use App\Models\Student_Bulk_Temps;
use App\Models\TcRegister;

class StudentsController extends Controller
{
    function __construct()
    {
        $this->middleware('permission:students-list|students-create|students-edit|students-delete', ['only' => ['index', 'store']]);
    }
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $data = Account::select([
            'accounts.*',
            'accounting_groups.name as group_name',
            'account_addresses.address_line1 as address',
            'countries.name as country',
            'states.name as state',
            'cities.name as city',
            'account_contacts.phone_no',
            'account_contacts.email',
            'account_contacts.account_gender_id',
            'account_contacts.date_of_birth',
            'genders.name as gender',
            'account_categories.category_name AS category',
        ])
            ->leftjoin('account_contacts', 'account_contacts.account_id', 'accounts.id')
            ->leftjoin('accounting_groups', 'accounting_groups.id', 'accounts.accounting_group_id')
            ->leftjoin('account_addresses', 'account_addresses.account_id', 'accounts.id')
            ->leftjoin('countries', 'countries.id', 'account_addresses.country_id')
            ->leftjoin('states', 'states.id', 'account_addresses.state_id')
            ->leftjoin('cities', 'cities.id', 'account_addresses.city_id')
            ->leftjoin('account_categories', 'account_categories.id', 'accounts.account_category_id')
            ->leftjoin('genders', 'genders.id', 'account_contacts.account_gender_id')
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id');

        if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');

            $data = $data->where('account_types.type_name', 'like', '%Student%')
                ->when(!empty($search_query), function ($query) use ($search_query) {
                    return $query->where('accounts.name', 'like', '%' . $search_query . '%')
                        ->orWhere('accounts.code', 'like', '%' . $search_query . '%');
                })
                ->paginate($sort_by);

            return view('students.table', compact('data'));
        } else {
            $data = $data->where('account_types.type_name', 'like', '%Student%')->paginate(10);

            return view('students.index', compact('data'));
        }
    }

    public function list(Request $request)
    {
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        $genders            = Gender::select(['id', 'name'])->get();
        $maritalStatus      = MaritalStatus::select(['id', 'name'])->get();
        $bloodGroup         = BloodGroup::select(['id', 'group'])->get();
        $account_types      = MasterType::where('account_types.type_code', 'like', '%STUDENT%')->get();
        $account_categories = MasterCategory::select(['id', 'category_name'])->get();
        $accounting_groups  = AccountingGroup::select(['id', 'name'])->whereNull('deleted_at')->get();
        $account_title      = AccountTitle::select(['id', 'name', 'value'])->where('status', '1')->get();
        $country            = Country::select(['id', 'name'])->where('status', '1')->get();
        $company_id         = \Auth::user()->company_id ?? '';

        $company_address = \Session::get('company_data')['companies_addresses'];

        $tax_register_categories = TaxRegisterCategory::select(['id', 'name'])->whereNull('deleted_at')->get();

        $countryCode = $company_address->country_code ?? 'us';
        $houses      = StudentHouse::all();
        $classes     = LmsClasses::all();
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        return view('students.create', compact([
            'classes',
            'houses',
            'country',
            'company_address',
            'countryCode',
            'account_types',
            'account_categories',
            'tax_register_categories',
            'genders',
            'maritalStatus',
            'bloodGroup',
            'account_title',
            'accounting_groups',
            'companyDateFormate'
        ]));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        $rules = [
            'tax_register_category_id' => 'required',
            'account_type_id'          => 'required',
            'account_category_id'      => 'required',
            // 'accounting_group_id'   => 'required',
            'name'                     => 'required',
        ];

        $type = MasterType::findOrFail($request->account_type_id);

        if (isset($type) && strtolower($type->type_code) != 'student') {
            $rules['accounting_group_id'] = 'required';
        } else {
            $rules['unique_register_no'] = 'nullable|unique:accounts,unique_register_no';
            $rules['class_id']           = 'required';
            // $rules['section_id']         = 'required';
        }

        $validator = \Validator::make($request->all(), $rules);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $data = $request->except([
            '_token',
            '_method',
            'country_id',
            'state_id',
            'cityid',
            'city_id',
            'phone_no',
            'whatsapp_no',
            'address_line1',
            'email',
            'contact',
            'date_of_birth',
            'account_gender_id',
            'blood_group_id',
            'marital_status',
            'admission_date',
            'admission_type',
            'class_id',
            'section_id',
            'any_known_allergies',
            'house_group_id',
        ]);

        $data['company_id'] = auth()->user()->company_id;

        $account = Account::create($data);


        if (isset($type) && strtolower($type->type_code) == 'general') {
            $accounting_groups = AccountingGroup::find($request->accounting_group_id);
            $count  = 1;
            $prefix = "AC";
            if($accounting_groups) {
                $count  = $accounting_groups->count != '' ? $accounting_groups->count + 1 : 1;
                $prefix = $accounting_groups->prefix != '' ? $accounting_groups->prefix : "AC";

                $accounting_groups->update(['count' => $count]);
            }

            $enquiry_id = $this->generateCode($count, $prefix);

            $account->update(['code' => $enquiry_id]);
        }else {
            $count  = 1;
            $prefix = "AC";

            if($type) {
                $count  = $type->count != '' ? $type->count + 1 : 1;
                $prefix = $type->prefix != '' ? $type->prefix : "AC";

                $type->update(['count' => $count]);
            }

            $enquiry_id = $this->generateCode($count, $prefix);

            $account->update(['code' => $enquiry_id]);
        }
        if (isset($type) && strtolower($type->type_code) == 'student') {
            StudentAdmission::create([
                'student_id'     => $account->id,
                'admission_date' => date('Y-m-d', strtotime($request->admission_date)),
                'admission_type' => $request->admission_type,
                'class_id'       => $request->class_id,
                'section_id'     => $request->section_id,
            ]);
        }

        $address = AccountAddress::insert([
            'account_id'    => $account->id,
            'country_id'    => $request->country_id,
            'state_id'      => $request->state_id,
            'city_id'       => $request->city_id,
            'address_line1' => $request->address_line1,
            'is_default'    => '1'
        ]);

        $contact = AccountContact::insert([
            'account_id'          => $account->id,
            'phone_no'            => $request->phone_no,
            'whatsapp_no'         => $request->whatsapp_no,
            'email'               => $request->email ?? '',
            'name'                => $request->name ?? '',
            'contact_person'      => $request->name ?? '',
            'date_of_birth'       => $request->date_of_birth != '' ? date('Y-m-d', strtotime($request->date_of_birth)) : '',
            'account_gender_id'   => $request->account_gender_id ?? '',
            'blood_group_id'      => $request->blood_group_id ?? '',
            'marital_status'      => $request->marital_status ?? '',
            'any_known_allergies' => $request->any_known_allergies ?? '',
            'country_id'          => $request->country_id,
            'state_id'            => $request->state_id,
            'city_id'             => $request->city_id,
            'house_group_id'      => $request->house_group_id,
        ]);

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Student has been created successfully',
            ]);
        }
        return redirect()->back()
            ->with('success', 'Student has been created successfully.');
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        $account = Account::select([
            'accounts.*',
            'account_types.type_name',
            'account_categories.category_name',
            'accounting_groups.name as group_name',
            'account_images.image',
            'countries.name as country',
            'states.name as state',
            'cities.name as city',
            'account_contacts.date_of_birth',
            'marital_statuses.name as marital_status',
            'genders.name as gender',
            'blood_groups.group as blood_group',
            'account_contacts.guardian_name',
            'account_contacts.anniversary_date',
            'account_contacts.any_known_allergies',
        ])
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
            ->leftjoin('account_categories', 'account_categories.id', 'accounts.account_category_id')
            ->leftjoin('accounting_groups', 'accounting_groups.id', 'accounts.accounting_group_id')
            ->leftjoin('account_images', 'account_images.account_id', 'accounts.id')
            ->leftjoin('account_contacts', 'account_contacts.account_id', 'accounts.id')
            ->leftjoin('marital_statuses', 'marital_statuses.id', 'account_contacts.marital_status')
            ->leftjoin('countries', 'countries.id', 'account_contacts.country_id')
            ->leftjoin('states', 'states.id', 'account_contacts.state_id')
            ->leftjoin('cities', 'cities.id', 'account_contacts.city_id')
            ->leftjoin('genders', 'genders.id', 'account_contacts.account_gender_id')
            ->leftjoin('blood_groups', 'blood_groups.id', 'account_contacts.blood_group_id')
            ->where('accounts.id', $id)->first();


        return view('students.show', compact('account'));
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        $account                 = Account::with('account_contact')->find($id);
        $admission               = StudentAdmission::whereStudentId($id)->first();
        $account_types           = MasterType::select(['id', 'type_name'])->get();
        $account_categories      = MasterCategory::select(['id', 'category_name'])->get();
        $accounting_groups       = AccountingGroup::select(['id', 'name'])->whereNull('deleted_at')->get();
        $tax_register_categories = TaxRegisterCategory::select(['id', 'name'])->whereNull('deleted_at')->get();
        $houses                  = StudentHouse::all();
        $classes                 = LmsClasses::all();
        $genders                 = Gender::select(['id', 'name'])->get();
        $sections = LmsClassSection::select('lms_class_sections.*', 'lms_sections.section')
            ->leftJoin('lms_sections', 'lms_class_sections.academics_section_id', 'lms_sections.id')
            ->whereClassId($admission->class_id)
            ->groupBy('lms_class_sections.id')
            ->get();
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());

        return view('students.edit', compact([
            'admission',
            'sections',
            'classes',
            'houses',
            'account',
            'account_types',
            'account_categories',
            'tax_register_categories',
            'accounting_groups',
            'genders',
            'companyDateFormate'
        ]));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        $rules = [
            // 'accounting_group_id' => 'required',
            'name'                => 'required',
        ];

        $acc = Account::findOrFail($id);
        $type = MasterType::findOrFail($acc->account_type_id);

        if (isset($type) && strtolower($type->type_name) != 'student') {
            $rules['accounting_group_id'] = 'required';
        } else {
            $rules['unique_register_no'] = 'nullable|unique:accounts,unique_register_no,' . $id;
            $rules['class_id']           = 'required';
            // $rules['section_id']         = 'required';
        }

        $validator = \Validator::make($request->all(), $rules);

        if ($validator->fails()) {
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $data = $request->except([
            '_token',
            'admission_date',
            'admission_type',
            'class_id',
            'section_id',
            '_method',
            'house_group_id',
            'date_of_birth',
            'account_gender_id'
        ]);

        $account = Account::find($id);

        if (!$account) {
            return redirect()->route('students.index')
                ->with('error', 'Student not found!');
        }

        $account->update($data);
        StudentAdmission::whereStudentId($id)->update([
            'admission_date' => $request->admission_date!= '' ? date('Y-m-d', strtotime($request->admission_date)) : null,
            'admission_type' => $request->admission_type,
            'class_id'       => $request->class_id,
            'section_id'     => $request->section_id,
        ]);
        AccountContact::whereAccountId($id)
        ->update([
            'house_group_id'    => $request->house_group_id,
            'date_of_birth'     => $request->date_of_birth != '' ? date('Y-m-d', strtotime($request->date_of_birth)) : null,
            'account_gender_id' => $request->account_gender_id
          ]);

        return redirect()->route('students.index')
            ->with('success', 'Student has been updated successfully.');
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy(Request $request)
    {
        $account = Account::find($request->id);

        // Find and delete all images of this account
        $account_images = AccountImage::select('image', 'id')->where('account_id', $request->id)->get();

        foreach ($account_images as $value) {
            deleteImage($value->image);

            AccountImage::where('id', $value->id)->delete();
        }

        // Find and delete all attachments of this account
        $account_attachment = AccountAttachment::select('file_name', 'id')->where('account_id', $request->id)->get();

        foreach ($account_attachment as $value) {
            deleteImage($value->file_name);

            AccountAttachment::where('id', $value->id)->delete();
        }

        // Find and delete all Licenses
        AccountLicense::where('account_id', $request->id)->delete();

        // Find and delete all Banks
        AccountBank::where('account_id', $request->id)->delete();

        // Find and delete all Contacts
        AccountContact::where('account_id', $request->id)->delete();

        // Find and delete all Addresses
        AccountAddress::where('account_id', $request->id)->delete();

        // Find and delete all Payments
        AccountPayment::where('account_id', $request->id)->delete();

        $account = $account->delete();

        $Redirect = 'accounts';

        return response()->json([
            'success' => true,
            'message' => ['Deleted successfully'],
            'data'    => [
                'redirect' => $Redirect,
            ]
        ]);
    }


    public function changeStatus(Request $request)
    {
        if ($request->ajax()) {
            $data   = array('status' => $request->status);
            $Update = StudentHouse::where('id', '=', $request->id)->update($data);

            if ($Update) {
                return response()->json([
                    'success' => true,
                    'message' => ['House status successfully change'],
                    'data' => [
                        'redirect' => '/student-house/',
                        'reload' => true,
                    ]
                ]);
            } else {
                return response()->json([
                    'success' => false,
                    'message' => ['Error for change status'],
                    'data' => [
                        'redirect' => '',
                    ]
                ]);
            }
        }
    }

    public function export(Request $request)
    {
        return Excel::download(new StudentsExport(request()), 'students.xlsx');
    }

    public function import()
    {
        return view('students.import');
    }


    public function importStore(Request $request)
    {
        $extension = File::extension($request->file->getClientOriginalName());
        $extension = strtolower($extension);

        if ($extension == 'xlsx' || $extension == 'csv') {
            $file   = $request->file('file')->getRealPath();
            $data = Excel::toArray(new ImportStudent, $request->file('file'));

            if (isset($data[0])) {
                $data = $data[0];
                unset($data[0]);
            }

            $collection = [];

            foreach ($data as $key => $record) {
                if ($record[0] == '' && $record[1] == '' && $record[2] == '' && $record[3] == '' && $record[4] == '' && $record[5] == '' && $record[6] == '' && $record[7] == '' && $record[8] == '' && $record[9] == '' && $record[10] == '') {
                    continue;
                }

                if ($record[0] == '') {
                    $message = 'Admission no is required.';

                    return redirect()->back()
                        ->with('error', $message);
                } elseif ($record[1] == '') {
                    $message = 'Name is required.';

                    return redirect()->back()
                        ->with('error', $message);
                }


                $find = Student_Bulk_Temps::where('admission_no', $record[0])->exists();

                if (!$find) {
                    $account = Student_Bulk_Temps::create([
                        'admission_no'        => $record[0],
                        'student_name'        => generateStringFormate($record[1]),
                        'gander'              => $record[2],
                        'dob'                 => $record[3],
                        'caste'               => $record[4],
                        'mobile'              => $record[5],
                        'email'               => $record[6],
                        'blood_group'         => $record[7],
                        'address'             => generateStringFormate($record[8]),
                        'house'               => $record[9],
                        'father_name'         => generateStringFormate($record[10]),
                        'mother_name'         => generateStringFormate($record[11]),
                        'gaurdian_name'       => generateStringFormate($record[12]),
                        'date_of_adminssion'  => $record[13],
                        'academic_year'       => $record[14],
                        'class'               => $record[15],
                        'section'             => $record[16],
                        'merital_status'      => $record[17],
                        'language'            => generateStringFormate($record[18]),
                        'roll_no'             => $record[19],
                        'tc_issue_class'      => $record[20],
                        'date_of_tc'          => $record[21],
                        'issue_status'        => $record[22],

                    ]);
                }
            }

            return redirect()->route('student-bulk-list')
                ->with('success', 'Students are imported successfully.');
        } else {
            return redirect()->back()
                ->with('error', 'Please upload valid Excel file format.');
        }
    }

    public function bulklist(Request $request)
    {
        $studentTemDatas = Student_Bulk_Temps::select([
            'id', 'admission_no', 'student_name', 'dob', 'caste',
            'mobile', 'date_of_adminssion', 'academic_year', 'status', 'error_message'
        ]);
        $sort_by    = $request->get('sortby') ?? 10;
        $sort_type  = $request->get('sorttype');
        $query      = $request->get('query');
        if (!empty($query) && !empty($sort_by) && $query != " ") {
            $studentTemDatas->where('student_name', 'like', '%' . $query . '%');
        }
        $studentTemData = $studentTemDatas->orderBy('status', 'desc')->paginate($sort_by);

        if ($request->ajax()) {
            return view('students.bulktable', compact('studentTemData'));
        }
        return view('students.bulklist', compact('studentTemData'));
    }

    public function bulktostudent(Request $request)
    {

        $financialYears = FinancialYear::select('financialyearid')->latest('financialyearid')->where('status', '1')->first();
        if (empty($financialYears)) {
            return response()->json([
                'success' => false,
                'message' => ['Financial Year is Not Generated'],
                'data' => [
                    'redirect' => '',
                ]
            ]);
        }

        $studentType     = MasterType::where('type_name', 'LIKE', '%Student%')
            ->first();
        $msg = '';
        $msg1 = '';
        $msg2 = '';
        foreach ($request->bulk_id as $key => $val) {
            $TempData = Student_Bulk_Temps::find($val);
            $getClasses = LmsClasses::where(['class_name' => generateStringFormate($TempData->class)])->first();
            if ($getClasses) {
                $classId = $getClasses->id;
                $classstatus = 2;
            } else {
                $classId = '';
                $classstatus = 0;
                $msg1 = "Class is Invalid";
            }


            /*****************Section Is get and insert ******************/
            $getSection = LmsSection::where('section', generateStringFormate($TempData->section))->first();
            if ($getSection) {
                $sectionId = $getSection->id;
                $sectionstatus = 2;
            } else {
                $sectionId = '';
                $sectionstatus = 0;
                $msg2 = "Section is Invalid";
            }
            if ($classstatus == 2 && $sectionstatus == 2)
            {

                if ($TempData->blood_group) {
                    $bloodGroupId    = BloodGroup::whereGroup($TempData->blood_group)->first();
                } else {
                    $bloodGroupId   = '';
                }
                if ($TempData->gander) {
                    $genderId        = Gender::whereName($TempData->gander)->first();
                } else {
                    $genderId        = '';
                }
                if ($TempData->merital_status) {
                    $maritalStatusId = MaritalStatus::whereName($TempData->merital_status)->first();
                } else {
                    $maritalStatusId = '';
                }

                $houseData = StudentHouse::where('name', generateStringFormate($TempData->house))->first();
                if ($houseData) {
                    $houseGroupId = $houseData->id;
                } else {
                    if($TempData->house!='')
                    {
                        $houseData = StudentHouse::create(['name', generateStringFormate($TempData->house)]);
                        $houseGroupId = $houseData->id;
                    }
                    else
                    {
                        $houseGroupId='';
                    }

                }
                $CategoryData=MasterCategory::where('category_name',trim($TempData->caste))->first();
                if($CategoryData){
                    $categoryId= $CategoryData->id;
                } else{
                    $categoryId = '';
                }

                $LanguageData=Language::where('name',trim($TempData->language))->first();
                if($LanguageData){
                    $LanguageId= $LanguageData->id;
                } else{
                    $LanguageId = '';
                }


                $find = Account::where('unique_register_no', $TempData->admission_no)->exists();

                if (!$find) {
                    $account = Account::create([
                        'unique_register_no'  => $TempData->admission_no,
                        'name'                => generateStringFormate($TempData->student_name),
                        'print_name'          => generateStringFormate($TempData->student_name),
                        'account_category_id' => $categoryId,
                        'account_type_id'     => $studentType->id,
                        'company_id'          => \Auth::user()->company_id ?? 0,

                    ]);

                    $address = AccountAddress::insert([
                        'account_id'    => $account->id,
                        'country_id'    => '',
                        'state_id'      => '',
                        'city_id'       => '',
                        'address_line1' => $TempData->address,
                        'is_default'    => '1'
                    ]);

                    $contact = AccountContact::insert([
                        'account_id'          => $account->id,
                        'phone_no'            => $TempData->mobile,
                        'whatsapp_no'         => '',
                        'email'               => $TempData->email,
                        'name'                => generateStringFormate($TempData->student_name),
                        'date_of_birth'       => $TempData->dob != '' ? date('Y-m-d', strtotime($TempData->dob)) : '',
                        'account_gender_id'   => $genderId,
                        'blood_group_id'      => $bloodGroupId,
                        'marital_status'      => $maritalStatusId,
                        'any_known_allergies' => '',
                        'country_id'          => '',
                        'state_id'            => '',
                        'city_id'             => '',
                        'house_group_id'      => $houseGroupId,
                        'language_id'         => $LanguageId,
                    ]);
                    /*******************Parents Details Insert******************************/
                    /*************************Fathers Details *********************/
                    if ($TempData->father_name != '') {
                        $findfathers = AccountRelativesDetail::where(['account_id' => $account->id, 'relation_type' => 'father'])->first();
                        if (!$findfathers) {
                            AccountRelativesDetail::create([
                                'account_id'            => $account->id,
                                'relation_type'         => 'father',
                                'relation_name'         => generateStringFormate($TempData->father_name),
                                'relation_phone'        => ' ',
                                'relation_occupation'   => ' ',
                                'priority'              => ' '
                            ]);
                        } else {
                            $findfathers->update(['relation_name' => generateStringFormate($TempData->father_name)]);
                        }
                    }

                    /*************************Mothers Details *********************/
                    if ($TempData->mother_name != '') {
                        $findMother = AccountRelativesDetail::where(['account_id' => $account->id, 'relation_type' => 'mother'])->first();
                        if (!$findMother) {
                            AccountRelativesDetail::create([
                                'account_id'            => $account->id,
                                'relation_type'         => 'mother',
                                'relation_name'         => generateStringFormate($TempData->mother_name),
                                'relation_phone'        => ' ',
                                'relation_occupation'   => ' ',
                                'priority'              => ' '
                            ]);
                        } else {
                            $findMother->update(['relation_name' => generateStringFormate($TempData->mother_name)]);
                        }
                    }

                    /*************************Guardian Details *********************/
                    if ($TempData->gaurdian_name != '') {
                        $findGuardian = AccountRelativesDetail::where(['account_id' => $account->id, 'relation_type' => 'guardian'])->first();
                        if (!$findGuardian) {
                            AccountRelativesDetail::create([
                                'account_id'            => $account->id,
                                'relation_type'         => 'guardian',
                                'relation_name'         => generateStringFormate($TempData->gaurdian_name),
                                'relation_phone'        => ' ',
                                'relation_occupation'   => ' ',
                                'priority'              => ' '
                            ]);
                        } else {
                            $findGuardian->update(['relation_name' => generateStringFormate($TempData->gaurdian_name)]);
                        }
                    }
                    /*************************************Student Admission Section *************************/




                    $FinancialYear = FinancialYear::where('financial_year_name', $TempData->academic_year)->first();

                    StudentAdmission::create([
                        'student_id'      =>  $account->id,
                        'financial_id'    =>  $FinancialYear->financialyearid ?? 0,
                        'class_id'        =>  $classId,
                        'section_id'      =>  $sectionId,
                        'admission_date'  =>  date('Y-m-d', strtotime($TempData->date_of_adminssion)),
                        'admission_type'  =>  'Direct',
                        'admission_fee'   =>  0,
                        'roll_no'         =>  $TempData->roll_no
                    ]);


                    $getClasses = LmsClasses::where(['class_name' => generateStringFormate($TempData->tc_issue_class)])->first();
                    if ($getClasses) {
                        $classId = $getClasses->id;
                    } else {
                        $classId = '';
                    }

                    if ($TempData->status=='T') {
                        $tcStatus = 'Yes';
                    } else {
                        $tcStatus = 'NO';
                    }

                    TcRegister::create([
                        'account_id'  => $account->id,
                        'date_of_tc'  => $TempData->date_of_tc,
                        'class_id'    => $classId,
                        'status'      => $tcStatus,
                        ]);
                }
                $TempData->update(['status' => 2]);
            }
            else
            {
                $msg = $msg1 . ',' . $msg2;
                $TempData->update(['status' => 0, 'error_message' => $msg]);
            }
        }
        Student_Bulk_Temps::where('status', 2)->delete();
        $Redirect = '/student-bulk-list';
        return response()->json([
            'success' => true,
            'message' => ['Student Upload Store successfully'],
            'data' => [
                'redirect' => $Redirect,
            ]
        ]);
    }



    public function statements(Request $request)
    {
        if (isset($request->type) && $request->type == 'excel') {
            return Excel::download(new StatementsExport(request()), 'statements.xlsx');
        }

        $houses     = StudentHouse::all();
        $classes    = LmsClasses::all();
        $sections   = LmsSection::all();
        $genders    = Gender::select(['id', 'name'])->get();
        $categories = MasterCategory::select(['id', 'category_name'])->get();

        $data = Account::select([
            'accounts.*',
            'accounting_groups.name as group_name',
            'account_addresses.address_line1 as address',
            'countries.name as country',
            'states.name as state',
            'cities.name as city',
            'account_contacts.phone_no',
            'account_contacts.email',
            'account_contacts.account_gender_id',
            'account_contacts.date_of_birth',
            'genders.name as gender',
            'lms_classes.class_name',
            'lms_sections.section',
            'student_houses.name as house_name',
            'student_admissions.student_id',
            'account_categories.category_name AS category',
        ])
            ->leftjoin('account_contacts', 'account_contacts.account_id', 'accounts.id')
            ->leftjoin('accounting_groups', 'accounting_groups.id', 'accounts.accounting_group_id')
            ->leftjoin('account_addresses', 'account_addresses.account_id', 'accounts.id')
            ->leftjoin('countries', 'countries.id', 'account_addresses.country_id')
            ->leftjoin('states', 'states.id', 'account_addresses.state_id')
            ->leftjoin('cities', 'cities.id', 'account_addresses.city_id')
            ->leftjoin('account_categories', 'account_categories.id', 'accounts.account_category_id')
            ->leftjoin('genders', 'genders.id', 'account_contacts.account_gender_id')
            ->leftjoin('account_types', 'account_types.id', 'accounts.account_type_id')
            ->leftjoin('student_houses', 'student_houses.id', 'accounts.house_group_id')
            ->leftjoin('student_admissions', 'accounts.id', 'student_admissions.student_id')
            ->leftjoin('lms_classes', 'lms_classes.id', 'student_admissions.class_id')
            ->leftjoin('lms_sections', 'lms_sections.id', 'student_admissions.section_id');

        if (isset($request->gender) && $request->gender != '') {
            $data->where('account_contacts.account_gender_id', $request->gender);
        }

        if (isset($request->house) && $request->house != '') {
            $data->where('accounts.house_group_id', $request->house);
        }

        if (isset($request->class) && $request->class != '') {
            $data->where('student_admissions.class_id', $request->class);
        }

        if (isset($request->section) && $request->section != '') {
            $data->where('student_admissions.section_id', $request->section);
        }

        if (isset($request->category) && $request->category != '') {
            $data->where('accounts.account_category_id', $request->category);
        }

        if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');

            $data = $data->where('account_types.type_name', 'like', '%Student%')
                ->when(!empty($search_query), function ($query) use ($search_query) {
                    return $query->where('accounts.name', 'like', '%' . $search_query . '%')
                        ->orWhere('accounts.code', 'like', '%' . $search_query . '%');
                })
                ->paginate($sort_by);

            return view('students.statement-table', compact('data'));
        } else {

            $data = $data->where('account_types.type_name', 'like', '%Student%')->paginate(10);

            return view('students.statements', compact(
                'data',
                'genders',
                'houses',
                'classes',
                'sections',
                'categories'
            ));
        }
    }

    public function bulkedit($id,Request $record)
    {
        $TempData=Student_Bulk_Temps::find($id);
        return view('students.editbulk',compact('TempData'));
    }
    public function bulkupdate($id,Request $request)
    {
        $TempData=Student_Bulk_Temps::find($id);
        $data=$request->all();
        $data['dob']=date('Y-m-d',strtotime($request->dob));
        $data['date_of_adminssion']=date('Y-m-d',strtotime($request->date_of_adminssion));
        $TempData->update($data);
        $Redirect = '/student-bulk-list';
        return response()->json([
            'success' => true,
            'message' => ['Student Upload Store successfully'],
            'data' => [
                'redirect' => $Redirect,
            ]
        ]);

    }
    public function check_error(Request $request)
    {
        if (isset($request->class)) {
            $classes = LmsClasses::where('class_name', trim($request->class))->first();
            if ($classes) {
              return response()->json([
                'success' => true,
                'message' => ['This is Avaible'],
              ]);
            } else {
              return response()->json([
                'success' => false,
                'message' => ['This Class is Not Avaible'],
              ]);
            }
          } elseif (isset($request->section)) {
            $section = LmsSection::where('section', trim($request->section))->first();
            if ($section) {
              return response()->json([
                'success' => true,
                'message' => ['This is Avaible'],
              ]);
            } else {
              return response()->json([
                'success' => false,
                'message' => ['This Section is Not Avaible'],
              ]);
            }
          }
    }
    function bulkdelete(Request $request)
    {
        $bookItem = Student_Bulk_Temps::find($request->id);
        $bookItem->delete();
        $Redirect = '';
        return response()->json([
          'success' => true,
          'message' => ['Deleted successfully'],
          'data' => [
            'redirect' => $Redirect,
            'reload' => true,
          ]
        ]);
    }
}
