<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use DB;
 
use Illuminate\Support\Facades\Session;

use function PHPUnit\Framework\isNull;

class BallotingMapDetail extends Model
{
    //
    protected $fillable = [
        'unit_type_id',
        'balloting_map_file_id',
        'unit_size_id',
    'sr_no',
    'plot_no',
    'area',
    'feature',
    'exact_odd',
    'sector',
    'road_category',
    'unit_block_id',
    'unit_floor_id',
    'unit_category_ids',
    'street',
    'type',
    'balloting_status'
    ];


    const custom = 4;
    const full_downpayment = 1;
    const partial_downpayment = 2;
    const random = 3;

    const not_ballot_yet = 0;
    const balloting_pending = 1;
    const balloting_complete = 2;
    public function customerProperty()
    {

         
        return $this->belongsTo('\App\CustomerProperty','customer_property_id','id');
    }
    public function Inventory()
    {

         
        return $this->belongsTo('\App\Inventory','inventory_id','id');
    }

    public function category(){
        return $this->hasMany('\App\BallonMapDetailCategory','ballon_map_detail_id','id');
    }

    public function map()
    {
        return $this->belongsTo('\App\BallotingMapFile','balloting_map_file_id','id');
    }

    public function startBalloting($request)
    {
        DB::beginTransaction();
       try {
            $progress = 0;
            $request->map_file;
            $map = BallotingMapFile::where('id', $request->map_file)->first();


            if ($request->add_preference == 'on') {
               
              
                $total_count = sizeof($request->select_property);

                
                foreach ($request->select_property as $customer_propert) {
                    $progress++;
                    $customer_propertie = CustomerProperty::where('id', $customer_propert)->first();
                  
                    $m = $map->mapDetail->where('unit_size_id', $customer_propertie->inventory->unit_size_id)
                        ->where('unit_type_id', $customer_propertie->inventory->unit_type_id)
                        ->where('unit_floor_id', $request->road_category)
                        ->where('unit_block_id', $request->sector)
                        ->where('unit_category_ids', 'like', $request->feature)
                        ->whereNull('customer_property_id')->first();

                        // dd($m,$request->add_preference,$request->select_property,$total_count,  $customer_propertie);





                    session(['balloting_progress' => ($progress / $total_count) * 100]);

                    if (!empty($m)) {
                        $m->customer_property_id = $customer_propertie->id;
                        $m->save();
                        $customer_pro = CustomerProperty::where('id', $customer_propertie->id)->first();
                        $customer_pro->plot_no = $m->plot_no;
                        $customer_pro->save();
                        $inventory = Inventory::where('id', $customer_pro->inventory_id)->first();
                        $inventory->unit_floor_id = $m->unit_floor_id;
                        $inventory->save();
                        $result = explode('+', $m->unit_category_ids);
                        if (!empty($result)) {
                            foreach ($result as $r) {
                                if ($r != null) {
                                    InventoryCategory::create(['inventory_id' => $inventory->id, 'unit_category_id' => $r]);
                                }
                            }



                        }

                    }

                }
                $map->status = 1;
                $map->save();
            }
           
            //cancel_date 
            //block_date
            //is_deleted
            // $unit_floor = UnitFloor::where([['is_deleted',false],['name','=','N/A']])->with('unitFloors')->first();
            
            // if ($request->pre_condition == BallotingMapDetail::full_downpayment) {
            //     $customer_properties = CustomerProperty::select('customer_properties.*')
            //         ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
            //         ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
                  
            //         ->select('customer_properties.*', DB::raw('SUM(customer_statements.received) as received_sum'), DB::raw('SUM(customer_statements.due) as due_amount_sum'))
            //         ->where('customer_statements.payment_head', CustomerStatement::down_payment)
            //         ->whereNull('customer_properties.plot_no')
            //         ->where('customer_properties.is_deleted', false)
            //         ->whereNull('customer_properties.block_date') 
            //         ->whereNull('customer_properties.cancel_date') 
            //         ->whereNull('inventories.unit_floor_id') 
            //         // ->whereNull('customer_properties.unit_floor_id') 
            //         // ->where('customer_properties.unit_floor_id',$unit_floor->id)
                    
            //         ->groupBy('customer_statements.customer_property_id')
            //         ->havingRaw('received_sum = due_amount_sum')
            //         ->get();
            //     $total_count = count($customer_properties);


            //     foreach ($customer_properties as $customer_propertie) {
            //         $progress++;


            //         session(['balloting_progress' => ($progress / $total_count) * 100]);

            //         $m = $map->mapDetail->where('unit_size_id', $customer_propertie->inventory->unit_size_id)->where('unit_type_id', $customer_propertie->inventory->unit_type_id)->whereNull('customer_property_id')->first();


            //         if (!empty($m)) {
            //             $m->customer_property_id = $customer_propertie->id;
            //             $m->save();
            //             $customer_pro = CustomerProperty::where('id', $customer_propertie->id)->first();
            //             $customer_pro->plot_no = $m->plot_no;
            //             $customer_pro->save();
            //             $inventory = Inventory::where('id', $customer_pro->inventory_id)->first();
            //             $inventory->unit_floor_id = $m->unit_floor_id;
            //             $inventory->save();
            //             $result = explode('+', $m->unit_category_ids);
            //             if (!empty($result)) {
            //                 foreach ($result as $r) {
            //                     if ($r != null) {
            //                         InventoryCategory::create(['inventory_id' => $inventory->id, 'unit_category_id' => $r]);
            //                     }
            //                 }
            //             }


            //         }

            //     }
            //     session(['balloting_progress' => 100]);
            //     $map->status = 1;
            //     $map->save();
            // }
            // if ($request->pre_condition == BallotingMapDetail::partial_downpayment) {
            //     $customer_properties = CustomerProperty::select('customer_properties.*')
            //     ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
            //     ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
            //     ->select('customer_properties.*', DB::raw('SUM(customer_statements.received) as received_sum'), DB::raw('SUM(customer_statements.due) as due_amount_sum'))
            //     ->where('customer_statements.payment_head', CustomerStatement::down_payment)
            //     ->whereNull('customer_properties.plot_no')
            //     ->where('customer_properties.is_deleted', false)
            //     ->whereNull('customer_properties.block_date') 
            //     ->whereNull('customer_properties.cancel_date')
            //     ->whereNull('inventories.unit_floor_id') 
            //     ->groupBy('customer_statements.customer_property_id')
            //     ->havingRaw('received_sum =  due_amount_sum  / 2')
            //     ->get();
            //     $total_count = count($customer_properties);
            //     foreach ($customer_properties as $customer_propertie) {
            //         $progress++;
            //         session(['balloting_progress' => ($progress / $total_count) * 100]);
            //         $m = $map->mapDetail->where('unit_size_id', $customer_propertie->inventory->unit_size_id)->where('unit_type_id', $customer_propertie->inventory->unit_type_id)->whereNull('customer_property_id')->first();

            //         if (!empty($m)) {
            //             $m->customer_property_id = $customer_propertie->id;
            //             $m->save();
            //             $customer_pro = CustomerProperty::where('id', $customer_propertie->id)->first();
            //             $customer_pro->plot_no = $m->plot_no;
            //             $customer_pro->save();
            //             $inventory = Inventory::where('id', $customer_pro->inventory_id)->first();
            //             $inventory->unit_floor_id = $m->unit_floor_id;
            //             $inventory->save();
            //             $result = explode('+', $m->unit_category_ids);
            //             if (!empty($result)) {
            //                 foreach ($result as $r) {
            //                     if ($r != null) {
            //                         InventoryCategory::create(['inventory_id' => $inventory->id, 'unit_category_id' => $r]);
            //                     }
            //                 }
            //             }


            //         }

            //     }
            //     session(['balloting_progress' => 100]);
            //     $map->status = 1;
            //     $map->save();
            // }
            if ($request->pre_condition == BallotingMapDetail::custom) {
                $filters = array();

                if ($request->downpayment_range_check == 1)
                    $filters['downpayment_range'] = $request->downpayment_range;
                if ($request->monthly_installment_range_check == 1)
                    $filters['monthly_installment_range'] = $request->monthly_installment_range;
                if ($request->possession_range_check == 1)
                    $filters['possession_range'] = $request->possession_range;
                if ($request->allotment_range_check == 1)
                    $filters['allotment_range'] = $request->allotment_range;
                if ($request->confirmation_range_check == 1)
                    $filters['confirmation_range'] = $request->confirmation_range;
                if ($request->fileprocessing_range_check == 1)
                    $filters['fileprocessing_range'] = $request->fileprocessing_range;
                if ($request->booking_range_check == 1)
                    $filters['booking_range'] = $request->booking_range;
                if ($request->registration_range_check == 1)
                    $filters['registration_range'] = $request->registration_range;
                if ($request->development_range_check == 1)
                    $filters['developmentrange'] = $request->developmentrange;
                if ($request->ballon_range_check == 1)
                    $filters['ballon_range'] = $request->ballon_range;
                if ($request->bulleting_range_check == 1)
                    $filters['bulleting_range'] = $request->bulleting_range;


                    $query =CustomerProperty::select('customer_properties.*', 
                    DB::raw('SUM(customer_statements.received) as received_sum'), 
                    DB::raw('SUM(customer_statements.due) as due_amount_sum'))
                    ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
                    ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
                    ->whereNull('customer_properties.plot_no')
                    ->where('customer_properties.is_deleted', false)
                    ->whereNull('customer_properties.block_date') 
                    ->whereNull('customer_properties.cancel_date') 
                    ->whereNull('inventories.unit_floor_id')
                    ->groupBy('customer_properties.id');


                foreach ($filters as $key => $val) {
                    if ($key == 'downpayment_range') {

                        $query->where('customer_statements.payment_head', CustomerStatement::down_payment)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                        


                    } elseif ($key == 'monthly_installment_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::installment)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                       
                    } elseif ($key == 'possession_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::possession)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                      
                    } elseif ($key == 'allotment_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::allotment)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                       
                    } elseif ($key == 'confirmation_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::confirmation)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                       
                    } elseif ($key == 'fileprocessing_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::file_processing)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                        
                    } elseif ($key == 'booking_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::booking)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                    } elseif ($key == 'registration_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::registration)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                      
                    } elseif ($key == 'developmentrange') {
                        $query->where('customer_statements.payment_head', CustomerStatement::development)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                       
                    } elseif ($key == 'ballon_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::ballon)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       
                      
                    } elseif ($key == 'bulleting_range') {

                        $query->where('customer_statements.payment_head', CustomerStatement::bulleting)
                        ->havingRaw('received_sum = due_amount_sum * ? / 100', [$val]);
                       

                     
                    }
                }

                $customer_properties = $query->get();
                $total_count = count($customer_properties);
                foreach ($customer_properties as $customer_propertie) {
                    $progress++;
                    session(['balloting_progress' => ($progress / $total_count) * 100]);
                    $m = $map->mapDetail->where('unit_size_id', $customer_propertie->inventory->unit_size_id)->where('unit_type_id', $customer_propertie->inventory->unit_type_id)->whereNull('customer_property_id')->first();

                    if(!empty($m)) {
                        $m->customer_property_id = $customer_propertie->id;
                        $m->save();
                        $customer_pro = CustomerProperty::where('id', $customer_propertie->id)->first();
                        $customer_pro->plot_no = $m->plot_no;
                        $customer_pro->save();
                        $inventory = Inventory::where('id', $customer_pro->inventory_id)->first();
                        $inventory->unit_floor_id = $m->unit_floor_id;
                        $inventory->save();
                        $result = explode('+', $m->unit_category_ids);
                        if (!empty($result)) {
                            foreach ($result as $r) {
                                if ($r != null) {
                                    InventoryCategory::create(['inventory_id' => $inventory->id, 'unit_category_id' => $r]);
                                }
                            }
                        }

                    }

                }
                session(['balloting_progress' => 100]);
                $map->status = 1;
                $map->save();
              
            }
            if($request->pre_condition == BallotingMapDetail::random) {
                $customer_properties = CustomerProperty::select('customer_properties.*')
                ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
                ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
                ->select('customer_properties.*', DB::raw('SUM(customer_statements.received) as received_sum'), DB::raw('SUM(customer_statements.due) as due_amount_sum'))
                ->where('customer_statements.payment_head', CustomerStatement::down_payment)
                ->whereNull('customer_properties.plot_no')
                ->where('customer_properties.is_deleted', false)
                ->whereNull('customer_properties.block_date') 
                ->whereNull('customer_properties.cancel_date') 
                ->whereNull('inventories.unit_floor_id')
                ->groupBy('customer_statements.customer_property_id')
                
                ->get();
                $total_count = count($customer_properties);
                foreach ($customer_properties as $customer_propertie) {
                    $progress++;
                    session(['balloting_progress' => ($progress / $total_count) * 100]);
                    $m = $map->mapDetail->where('unit_size_id', $customer_propertie->inventory->unit_size_id)->where('unit_type_id', $customer_propertie->inventory->unit_type_id)->whereNull('customer_property_id')->first();

                    if (!empty($m)) {
                        $m->customer_property_id = $customer_propertie->id;
                        $m->save();
                        $customer_pro = CustomerProperty::where('id', $customer_propertie->id)->first();
                        $customer_pro->plot_no = $m->plot_no;
                        $customer_pro->save();
                        $inventory = Inventory::where('id', $customer_pro->inventory_id)->first();
                        $inventory->unit_floor_id = $m->unit_floor_id;
                        $inventory->save();
                        $result = explode('+', $m->unit_category_ids);
                        if (!empty($result)) {
                            foreach ($result as $r) {
                                if ($r != null) {
                                    InventoryCategory::create(['inventory_id' => $inventory->id, 'unit_category_id' => $r]);
                                }
                            }
                        }

                    }

                }
                session(['balloting_progress' => 100]);
                $map->status = 1;
                $map->save();
            }
       }
        catch (\Exception $e) {

            // dd($e);
            DB::rollback();
            $val = [
                'status' => false,
                'id' => null
            ];
            return $val;
        }
        DB::commit();
        $val = [
            'status' => true,
            'id' =>  $map->id
        ];
        return $val;


    }
   
    public function startCreatedPreconditionInventory($request,$ids)
    {
        
        DB::beginTransaction();
     //  try {

            $progress = 0;
            $request->map_file;
            $map = BallotingMapFile::where('id', $request->map_file)->first();


            
           
            //cancel_date 
            //block_date
            //is_deleted
            // $unit_floor = UnitFloor::where([['is_deleted',false],['name','=','N/A']])->with('unitFloors')->first();
            
             
            if ($request->pre_condition == BallotingMapDetail::custom) {
               
                $filters = array();

                if ($request->downpayment_range_check == 1)
                    $filters['downpayment_range'] = $request->downpayment_range;
                if ($request->monthly_installment_range_check == 1)
                    $filters['monthly_installment_range'] = $request->monthly_installment_range;
                if ($request->possession_range_check == 1)
                    $filters['possession_range'] = $request->possession_range;
                if ($request->allotment_range_check == 1)
                    $filters['allotment_range'] = $request->allotment_range;
                if ($request->confirmation_range_check == 1)
                    $filters['confirmation_range'] = $request->confirmation_range;
                if ($request->fileprocessing_range_check == 1)
                    $filters['fileprocessing_range'] = $request->fileprocessing_range;
                if ($request->booking_range_check == 1)
                    $filters['booking_range'] = $request->booking_range;
                if ($request->registration_range_check == 1)
                    $filters['registration_range'] = $request->registration_range;
                if ($request->development_range_check == 1)
                    $filters['developmentrange'] = $request->developmentrange;
                if ($request->ballon_range_check == 1)
                    $filters['ballon_range'] = $request->ballon_range;
                if ($request->bulleting_range_check == 1)
                    $filters['bulleting_range'] = $request->bulleting_range;

            // if($request->overdue_amount == 'on'){
           
                if ($request->specific_size[0] == 'all') {
               
                $query = CustomerProperty::select(
                    'customer_properties.*',
                    DB::raw('SUM(customer_statements.received) as received_sum'),
                    DB::raw('SUM(customer_statements.due) as due_amount_sum')
                )
                    ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
                    ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
                    ->when($ids[0] !== 'all', function ($query) use ($ids) {
                        $query->whereHas('inventory', function ($q) use ($ids) {
                            $q->whereIn('multi_project_id', $ids);
                        });
                    })
                    ->where('customer_properties.is_deleted', false)
                    ->whereNull('customer_properties.block_date')
                    ->whereNull('customer_properties.cancel_date')
                    ->whereNull('inventories.unit_floor_id')
                    ->where('inventories.inventory_type', Inventory::unallocated)
                    ->where('inventories.status', Inventory::sold)
                    ->where('customer_properties.status', CustomerProperty::approved)
                    ->groupBy('customer_statements.customer_property_id')
                    ;
                
            } else {
                $query = CustomerProperty::select(
                    'customer_properties.*',
                    DB::raw('SUM(customer_statements.received) as received_sum'),
                    DB::raw('SUM(customer_statements.due) as due_amount_sum')
                )
                    ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
                    ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
                    ->when($ids[0] !== 'all', function ($query) use ($ids) {
                        $query->whereHas('inventory', function ($q) use ($ids) {
                            $q->whereIn('multi_project_id', $ids);
                        });
                    })
                    ->where('customer_properties.is_deleted', false)
                    ->whereNull('customer_properties.block_date')
                    ->whereNull('customer_properties.cancel_date')
                    ->whereNull('inventories.unit_floor_id')
                    ->whereIn('inventories.unit_size_id',$request->specific_size)
                    ->where('inventories.inventory_type', Inventory::unallocated)
                    ->where('inventories.status', Inventory::sold)
                    ->where('customer_properties.status', CustomerProperty::approved)
                    ->groupBy('customer_statements.customer_property_id')
                ;
            }

            // }  
            if ($request->total_amount_range_check == 1) {
            
                $query->
               

                havingRaw('received_sum >= due_amount_sum * ? / 100', [$request->total_amount_range]);

            } else {
                foreach ($filters as $key => $val) {
                   

                    if ($key == 'downpayment_range') {

                        $query->
                            where('customer_statements.payment_head', CustomerStatement::down_payment)

                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);



                    } elseif ($key == 'monthly_installment_range') {
                        $query
                            ->where('customer_statements.payment_head', CustomerStatement::installment)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'possession_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::possession)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'allotment_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::allotment)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'confirmation_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::confirmation)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'fileprocessing_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::file_processing)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'booking_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::booking)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);

                    } elseif ($key == 'registration_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::registration)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'developmentrange') {
                        $query->where('customer_statements.payment_head', CustomerStatement::development)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'ballon_range') {
                        $query->where('customer_statements.payment_head', CustomerStatement::ballon)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);


                    } elseif ($key == 'bulleting_range') {

                        $query->where('customer_statements.payment_head', CustomerStatement::bulleting)
                            ->havingRaw('received_sum >= due_amount_sum * ? / 100', [$val]);



                    }


                }
            }
    
            $customer_properties = $query
     
    ->get();
            return $customer_properties;
                 
              
            }


            if($request->pre_condition == BallotingMapDetail::random) {

            if ($request->specific_size[0] == 'all') {
                
                    $customer_properties =       CustomerProperty::select('customer_properties.*')
                ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
                ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
                // ->select('customer_properties.*', DB::raw('SUM(customer_statements.received) as received_sum'),
                //  DB::raw('SUM(customer_statements.due) as due_amount_sum'))
                ->when($ids[0] !== 'all', function ($query) use ($ids) {
                    $query->whereHas('inventory', function ($q) use ($ids) {
                        $q->whereIn('multi_project_id', $ids);
                    });
                })
                ->where('customer_properties.is_deleted', false)
                ->whereNull('customer_properties.block_date')
                ->where('customer_properties.status', CustomerProperty::approved)
                ->whereNull('customer_properties.cancel_date')
                ->whereNull('inventories.unit_floor_id')
                ->where('inventories.inventory_type', Inventory::unallocated)
                ->where('inventories.status', Inventory::sold)
                
                ->groupBy('customer_statements.customer_property_id')
                ->get();

                 


            } else {
                $customer_properties = CustomerProperty::select('customer_properties.*')
                ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
                ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
                // ->select('customer_properties.*', DB::raw('SUM(customer_statements.received) as received_sum'),
                //  DB::raw('SUM(customer_statements.due) as due_amount_sum'))
                ->when($ids[0] !== 'all', function ($query) use ($ids) {
                    $query->whereHas('inventory', function ($q) use ($ids) {
                        $q->whereIn('multi_project_id', $ids);
                    });
                })
                ->where('customer_properties.is_deleted', false)
                ->whereNull('customer_properties.block_date')
                ->where('customer_properties.status', CustomerProperty::approved)
                ->whereNull('customer_properties.cancel_date')
                ->whereNull('inventories.unit_floor_id')
                ->where('inventories.inventory_type', Inventory::unallocated)
                ->where('inventories.status', Inventory::sold)
                ->whereIn('inventories.unit_size_id',$request->specific_size)
                ->groupBy('customer_statements.customer_property_id')
                ->get();
            }
                return $customer_properties;
               
            }
    //   }
    //     catch (\Exception $e) {

    //         // dd($e);
    //         DB::rollback();
    //         $val = [
    //             'status' => false,
    //             'id' => null
    //         ];
    //         return $val;
    //     }
    //     DB::commit();
    //     $val = [
    //         'status' => true,
    //         'id' =>  $map->id
    //     ];
        // return $val;


    }
   
    public function startBallotingold($request)
    {
        $request->map_file;
        $map=   BallotingMapFile::where('id', $request->map_file)->first();

       
//      $customer_properties = CustomerProperty::select('customer_properties.*')
//      ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
//      ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
//      ->select('customer_properties.*',  DB::raw('SUM(customer_statements.received) as received_sum'), DB::raw('SUM(customer_statements.due) as due_amount_sum'))
//      ->where('customer_statements.payment_head', CustomerStatement::down_payment)
//      ->where('customer_properties.plot_no', '!=', null)
//      ->whereIn('inventories.unit_size_id', function($query) use ($balloting_map_details) {
//          $query->select('unit_size_id')
//                ->from('inventories')
//             //    ->whereIn('unit_type_id', $balloting_map_details->pluck('type'))
//             //    ->whereIn('unit_feature_id', $balloting_map_details->pluck('feature'))
//                ->whereIn('unit_size_id', $balloting_map_details->pluck('unit_size_id'))
//                ->orderBy('id')
//                ->chunk(1000, function($unit_sizes) use ($query, $balloting_map_details) {
//                    foreach ($unit_sizes as $unit_size) {
//                                 $matching_map_details = $balloting_map_details->where('unit_size_id', $unit_size);
//                     //    ->where('type', $unit_type)->where('feature', $unit_feature);
//                        if ($matching_map_details->count() > 0) {
//                            $query->orWhere(function($query) use ($unit_size, $matching_map_details) {
//                                $query->where('unit_size_id', $unit_size);
//                             //    foreach ($matching_map_details as $matching_map_detail) {
//                             //        $query->orWhere(function($query) use ($matching_map_detail) {
//                             //            $query->where('unit_type_id', $matching_map_detail->type)
//                             //                  ->where('unit_feature_id', $matching_map_detail->feature);
//                             //        });
//                             //    }
//                            });
//                        }
//                    }
//                });
//      })
//      ->groupBy('customer_statements.customer_property_id')
//      ->havingRaw('received_sum = due_amount_sum')
//      ->get();
 
//  foreach ($customer_properties as $customer_property) {
//      // Check if the conditions are true for this customer property
//      $unit_size_id = $customer_property->inventories->unit_size_id;
//     //  $unit_type_id = $customer_property->inventories->unit_type_id;
//     //  $unit_feature_id = $customer_property->inventories->unit_feature_id;
//             $matching_map_details = $balloting_map_details->where('area', $unit_size_id);
//     //  ->where('type', $unit_type_id)->where('feature', $unit_feature_id);
//      if ($matching_map_details->count() > 0) {
//          // Update the inventories and balloting_map_details records
//          $customer_property->inventories->update([
//              'status' => 'booked',
//              // add other columns to update as needed
//          ]);
//          foreach ($matching_map_details as $matching_map_detail) {
//              $matching_map_detail->update(['is_matched' => true]);
//          }
//      }
//  }
 











    




        
         


       






        $customer_properties = CustomerProperty::select('customer_properties.*')
        ->join('customer_statements', 'customer_statements.customer_property_id', '=', 'customer_properties.id')
        ->join('inventories', 'inventories.id', '=', 'customer_properties.inventory_id')
        ->select('customer_properties.*',  DB::raw('SUM(customer_statements.received) as received_sum'), DB::raw('SUM(customer_statements.due) as due_amount_sum'))
        ->where('customer_statements.payment_head', CustomerStatement::down_payment)
        ->where('customer_properties.plot_no',null)
        
        ->groupBy('customer_statements.customer_property_id')
        ->havingRaw('received_sum = due_amount_sum')
        ->get();

       
        foreach( $customer_properties as  $customer_propertie){

         $m=   $map->mapDetail->where('unit_size_id',$customer_propertie->inventory->unit_size_id)->where('customer_property_id',null)->first();
       
         if(!empty($m)){
            $m->customer_property_id = $customer_propertie->id;
            $m->save();
              $customer_pro=  CustomerProperty::where('id',$customer_propertie->id)->first();
                $customer_pro->plot_no = $m->plot_no;
                $customer_pro->save();
               
             
         }
       
        }



    // $map_detail=    $map->mapDetail;
    //     // dd($map_detail);
    //     foreach ($map_detail as $detail) {

           

    //         if( $customer_properties == )
    //         $customer_properties;
    //         // $plot_no;


    //         $detail->area;
    //         $detail->feature;
    //         $detail->exact_odd;
    //         $detail->sector;
    //         $detail->road_category;
             
        
    //     }


    //     dd($customer_properties);


    }


    public function startBallotingInventory($request)
    {
        DB::beginTransaction();
         try {
            $progress = 0;
            $request->map_file;
            $map = BallotingMapFile::where('id', $request->map_file)->first();

            // if ($request->add_preference == 'on') {
               
              
            //     $total_count = sizeof($request->select_property);

            //     $v = 0;
            //     foreach ($request->select_property as $customer_propert) {
            //         $v = $v + 1;
                  
            //         $inventory_data = Inventory::where('id', $customer_propert)->first();
            //         if (!empty($inventory_data)) {
            //             $progress++;
            //             session(['balloting_progress' => ($progress / $total_count) * 100]);
    
            //             $ballondetail = BallotingPreConditionFileDetail::where('inventory_id', $inventory_data->id)->where('balloting_pre_cond_file_id', $request->BallotingPreConditionFile_id)->first();
    
            //             if(empty($ballondetail)){
            //                 $ballondetail=    new BallotingPreConditionFileDetail;
            //                 $ballondetail->inventory_id = $inventory_data->id;
            //                 $ballondetail->unit_size_id = $inventory_data->size->id;
            //                 $ballondetail->balloting_pre_cond_file_id=$request->BallotingPreConditionFile_id;
            //                 $ballondetail->save();
            //             }
    
            //        $m=     BallotingMapDetail::where('balloting_map_file_id',$map->id)->
            //             where('unit_size_id', $inventory_data->unit_size_id)->
            //             where('unit_type_id', $inventory_data->unit_type_id)
                       
            //             ->whereNull('inventory_id')

            //             ->where('unit_floor_id', $request->road_category)
            //             ->where('unit_block_id', $request->sector_pre)
            //             ->whereNull('inventory_id')
            //             ->where('unit_category_ids', 'like', $request->feature)
            //             ->first();
                       
            //                 // $m = $map->mapDetail
            //                 // ->
            //                 // where('unit_size_id', $inventory_data->unit_size_id)->
            //                 // where('unit_type_id', $inventory_data->unit_type_id)
            //                 // ->whereNull('inventory_id')
                      
 
            //                 // ->where('unit_floor_id', $request->road_category)
            //                 // ->where('unit_block_id', $request->sector_pre)
            //                 // ->where('unit_category_ids', 'like', $request->feature)
            //                 // ->first();
            //             // //   if ($v == 2) {
            //             //     dd($inventory_data, $m,$inventory_data->unit_size_id,
            //             //     $inventory_data->unit_type_id, $request->road_category,$request->sector_pre, $request->feature,$inventory_data->type->name);
            //             // //   }
            //             if (!empty($m)) {
            //                 $m->inventory_id = $inventory_data->id;
            //                 $m->save();
            //                 $old_unit_number = $inventory_data->unit_number;
            //                 $inventory_data->unit_floor_id = $m->unit_floor_id;
            //                 $inventory_data->unit_number = $m->plot_no;
            //                 $inventory_data->old_unit_number = $old_unit_number;
            //                 $inventory_data->inventory_type = Inventory::allocated;
            //                 $inventory_data->save();
            //                 $ballondetail = BallotingPreConditionFileDetail::where('inventory_id', $inventory_data->id)->where('balloting_pre_cond_file_id', $request->BallotingPreConditionFile_id)->first();
    
            //                 if (!empty($ballondetail)) {
    
            //                     $ballondetail->balloting_map_detail_id = $m->id;
            //                     $ballondetail->save();
    
            //                 } 
            //                 else {
            //                     $ballondetail=    new BallotingPreConditionFileDetail;
            //                     $ballondetail->inventory_id = $inventory_data->id;
            //                     $ballondetail->unit_size_id = $inventory_data->size->id;
            //                     $ballondetail->balloting_map_detail_id=$m->id;
            //                     $ballondetail->balloting_pre_cond_file_id=$request->BallotingPreConditionFile_id;
            //                     $ballondetail->save();
            //                 }
            //                 $result = explode('+', $m->unit_category_ids);
            //                 if (!empty($result)) {
            //                     foreach ($result as $r) {
            //                         if ($r != null) {
            //                             InventoryCategory::create(['inventory_id' => $inventory_data->id, 'unit_category_id' => $r]);
            //                         }
            //                     }
            //                 }
    
            //             }
            //         }
            //     }
               
            // }



                     







        if (!empty($request->inventory_id)) {
            $total_count = count($request->inventory_id);
            foreach ($request->inventory_id as $invent) {
                $inventory_data = Inventory::where('id', $invent)->first();
                if (!empty($inventory_data)) {
                    $progress++;
                    session(['balloting_progress' => ($progress / $total_count) * 100]);

                    $ballondetail = BallotingPreConditionFileDetail::where('inventory_id',
                     $inventory_data->id)->where('balloting_pre_cond_file_id', 
                     $request->BallotingPreConditionFile_id)->first();

                    if(empty($ballondetail)){
                        $ballondetail=    new BallotingPreConditionFileDetail;
                        $ballondetail->inventory_id = $inventory_data->id;
                        $ballondetail->unit_size_id = $inventory_data->size->id;
                        $ballondetail->balloting_pre_cond_file_id=$request->BallotingPreConditionFile_id;
                        $ballondetail->save();
                    }

                    if ($request->specific_block == 'on') {
                          
                        if (!empty($request->sector)) {

                            if ($request->has('unit_category_'.$inventory_data->id)) {
                                $array = $request->input('unit_category_'.$inventory_data->id, []) ;
         
                                  
                                   $m = $map->mapDetail()
                                   ->where('unit_block_id', $request->sector)
                                   ->where('unit_size_id', $inventory_data->unit_size_id)
                                   ->where('unit_type_id', $inventory_data->unit_type_id)
                                   ->whereHas('category', function ($query) use ($array) {
                                    $query->whereIn('category_id', $array);
                                })
                                ->whereDoesntHave('category', function ($query) use ($array) {
                                    $query->whereNotIn('category_id', $array);
                                }, '=', count($array))
                                //    ->whereHas('category', function ($query) use ($array) {
                                //     $query->whereIn('category_id', $array);})
                                   ->whereNull('inventory_id')->first();
                                  
           
                            }
                            else{
                            $m = $map->mapDetail->where('unit_block_id', $request->sector)->where('unit_size_id', $inventory_data->unit_size_id)->where('unit_type_id', $inventory_data->unit_type_id)->whereNull('inventory_id')->first();
                            }
                        } else {

                            if ($request->has('unit_category_'.$inventory_data->id)) {
                                $array = $request->input('unit_category_'.$inventory_data->id, []) ;
         
                                  
                                   $m = $map->mapDetail()
                                   ->where('unit_size_id', $inventory_data->unit_size_id)
                                   ->where('unit_type_id', $inventory_data->unit_type_id)
                                   ->whereHas('category', function ($query) use ($array) {
                                    $query->whereIn('category_id', $array);
                                })
                                ->whereDoesntHave('category', function ($query) use ($array) {
                                    $query->whereNotIn('category_id', $array);
                                }, '=', count($array))
                                 
                                //    ->whereHas('category', function ($query) use ($array) {
                                //     $query->whereIn('category_id', $array);})
                                   ->whereNull('inventory_id')->first();
                                }
                                    else {
                                    $m = $map->mapDetail->where('unit_size_id', $inventory_data->unit_size_id)->where('unit_type_id', $inventory_data->unit_type_id)->whereNull('inventory_id')->first();
                                }
                        }
                        } else {
                            if ($request->has('unit_category_' . $inventory_data->id)) {
                                $array = $request->input('unit_category_' . $inventory_data->id, []);

                                
                            $m = $map->mapDetail()->where('unit_size_id', $inventory_data->unit_size_id)->where('unit_type_id', $inventory_data->unit_type_id)
                            ->whereHas('category', function ($query) use ($array) {
                                $query->whereIn('category_id', $array);
                            })
                            ->whereDoesntHave('category', function ($query) use ($array) {
                                $query->whereNotIn('category_id', $array);
                            }, '=', count($array)) 
                            // ->whereHas('category', function ($query) use ($array) {
                                //     $query->whereIn('category_id', $array);})
                                
                                ->whereNull('inventory_id')->first();

                                
                            } else {
                                $m = $map->mapDetail->
                                where('unit_size_id', $inventory_data->unit_size_id)->
                                where('unit_type_id', $inventory_data->unit_type_id)->
                                whereNull('inventory_id')->first();
                            }
                        }
                        
                    if (!empty($m)) {
                        $m->inventory_id = $inventory_data->id;
                        $m->balloting_status = BallotingMapDetail::balloting_pending;
                        $m->save();
                        $old_unit_number = $inventory_data->unit_number;
                        $inventory_data->unit_floor_id = $m->unit_floor_id;
                        $inventory_data->unit_number = $m->plot_no;
                        $inventory_data->old_unit_number = $old_unit_number;
                        $inventory_data->inventory_type = Inventory::allocated;
                        $inventory_data->save();
                        $cnic = $inventory_data->customer_property->customer->cnic;
                        $customerData = [
                            'name' =>  $inventory_data->customer_property->customer->name,
                            'cnic' => $cnic,
                        ];
                    
                        Session::put('customer_'.now(). $cnic, $customerData);
                        $ballondetail = BallotingPreConditionFileDetail::where('inventory_id', $inventory_data->id)->where('balloting_pre_cond_file_id', $request->BallotingPreConditionFile_id)->first();

                        if (!empty($ballondetail)) {

                            $ballondetail->balloting_map_detail_id = $m->id;
                            $ballondetail->save();

                        } 
                        else {
                            $ballondetail=    new BallotingPreConditionFileDetail;
                            $ballondetail->inventory_id = $inventory_data->id;
                            $ballondetail->unit_size_id = $inventory_data->size->id;
                            $ballondetail->balloting_map_detail_id=$m->id;
                            $ballondetail->balloting_pre_cond_file_id=$request->BallotingPreConditionFile_id;
                            $ballondetail->save();
                        }
                        // $result = explode('+', $m->unit_category_ids);
                        // dd($m->category);
                        if (!empty($m->category)) {
                            foreach ($m->category as $r) {

                                
                                    InventoryCategory::create(['inventory_id' => $inventory_data->id, 'unit_category_id' => $r->category_id]);
                                
                            }
                        }
                       

                    }
                }
            }
        }
         
          
            session(['balloting_progress' => 100]);
            $map->status = 1;
            $map->save();
        }
        catch (\Exception $e) {

            // dd($e);
            DB::rollback();
            $val = [
                'status' => false,
                'id' => null
            ];
            return $val;
        }
        DB::commit();
        $val = [
            'status' => true,
            'id' =>  $map->id
        ];
        return $val;


    }
}
