<?php
namespace App\Http\Controllers;
use Illuminate\Support\Collection;
use Validator;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Hash;
use Auth;
use DatePeriod;
use DateTime;
use DateInterval;
use PDF;
use DB;
use App\Models\User;
use App\Models\Settings\Organisation;
use App\Models\Facility\Facility;
use App\Models\Facility\ShowTimings;
use App\Models\Sales\Refund;
use App\Models\Sales\Ticket;
use App\Models\Facility\Layout;
use App\Models\Facility\Seats;
use App\Models\Sales\Transaction;
use App\Models\Settings\UserRoles;
use App\Models\Mail\MailLog;
use App\Models\Mail\SMSLog;
use App\Models\Sales\Invoice;
use App\Models\Sales\TicketSeat;
use App\Models\Sales\PaymentType;
use App\Models\Sales\PaymentMethod;
use App\Models\Sales\TicketCancelApprove;
use App\Models\Sales\CancelTicket;

use Request as req;

class ReportController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */

    public function get_facility($organisation_id)
    {
        if($organisation_id=="All")
        {
            $facilities=Facility::all();
            return $facilities->pluck('facility_name', 'id');
        }
        else
        {
            $facilities=Facility::where('organisation_id',$organisation_id)->get();
            return $facilities->pluck('facility_name', 'id');
        }
    }

    public function get_user_org($organisation_id)
    {
        if($organisation_id=="All")
        {
            $users=User::where('status',1)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
            return $users->pluck('name', 'id');
        }
        else
        {
            $users=User::where('organisation_id',$organisation_id)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
            return $users->pluck('name', 'id');
        }
    }

    //////////facility report////////////////////////////
    public function facility_report()
    {
        $page_title = 'Facility Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $organisation_id = "";
        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $facilities = Facility::where('organisation_id',$organisation_id)->where('active_flag',1)->with('organisation')->orderBy('order_by','asc')->get();  
            $organisations = Organisation::where('id',$organisation_id)->get();
        }
        else
        {
            $facilities = Facility::where('active_flag',1)->with('organisation')->orderBy('order_by','asc')->get();  
            $organisations = Organisation::all();
        }
        return view('Reports/facility_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id','facilities'));
    }
    public function facility_report_list(Request $request)
    {
        $page_title = 'Facility Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $organisation_id = $request->organisation_id;
        $facility_id = $request->facility_id;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_array = [];
        $ticket_date_arr =[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $facility_list =[];
        $ticket_count=[];
        $facility_name = [];
        $adult_count = [];
        $child_count=[];
        $totalamount= [];
        $minimum_ticket_num = [];
        $maximum_ticket_num = [];
        $ticket_number=[];
        $fac_id ="";
        //dd($daterange);
        $facilities ="";
        $org_arr="";
        if(($organisation_id=="All") && ($facility_id=="All"))
        {
            foreach($daterange as $date)
            {
                $ticket_date = $date->format("Y-m-d");
                $ticket_date_arr[] = $ticket_date;
                $n=0;
                $facility_list[$ticket_date] = [];
                $ticket_fac_arr[$ticket_date] = Ticket::where('status',1)->whereDate('show_date',$ticket_date)->select('facility_id')->groupBy('facility_id')->get('facility_id');
                foreach($ticket_fac_arr[$ticket_date] as $ticket_arr)
                {
                    $fac_id = $ticket_arr->facility_id;
                    $ticket_facility_arr = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->first();
                    if(isset($ticket_facility_arr))
                    {
                        $facility_list[$ticket_date][] = $fac_id;
                        $adult_count[$fac_id] = Ticket::where('facility_id',$fac_id)->where('status',1)->whereDate('show_date',$ticket_date)->sum('noof_adult');
                        $child_count[$fac_id] = Ticket::where('facility_id',$fac_id)->where('status',1)->whereDate('show_date',$ticket_date)->sum('noof_child');
                        $ticket_count[$fac_id] = Ticket::where('facility_id',$fac_id)->where('status',1)->whereDate('show_date',$ticket_date)->count();
                        $totalamount[$fac_id] = Ticket::where('facility_id',$fac_id)->where('status',1)->whereDate('show_date',$ticket_date)->sum('amount');

                        $minimum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->where('status',1)->whereDate('show_date',$ticket_date)->min('ticket_no');
                        $maximum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->where('status',1)->whereDate('show_date',$ticket_date)->max('ticket_no');

                        $ticket_number[$fac_id] = $minimum_ticket_num[$fac_id]." - ".$maximum_ticket_num[$fac_id];

                        if($ticket_count[$fac_id]!=0)
                        {
                            $n++;
                            if(isset($ticket_facility_arr->facility_id))
                                $facility_name[$fac_id] = $ticket_facility_arr->facility->facility_name;
                            $ticket_array[$ticket_date][$fac_id]['facility_name'] = $facility_name[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['nooftickets'] = $ticket_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofadults'] = $adult_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofchildren'] = $child_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['totalamount'] = $totalamount[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['ticketnumber'] = $ticket_number[$fac_id];
                        }
                    }
                }
            }
        }
        if(($organisation_id!="All") && ($facility_id!="All"))
        {
            $org_arr = Organisation::where('id',$organisation_id)->first();
            $facilities = Facility::where('id',$facility_id)->first();
            $ticket_array =[];
            foreach($daterange as $date)
            {
                $ticket_date = $date->format("Y-m-d");
                $ticket_date_arr[] = $ticket_date;
                $n=0;
                $facility_list[$ticket_date] = [];
                $ticket_fac_arr[$ticket_date] = Ticket::where('facility_id',$facility_id)->where('status',1)->whereDate('show_date',$ticket_date)->select('facility_id')->groupBy('facility_id')->get('facility_id');
                foreach($ticket_fac_arr[$ticket_date] as $ticket_arr)
                {
                    $fac_id = $ticket_arr->facility_id;
                    $ticket_facility_arr = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->first();
                    if(isset($ticket_facility_arr))
                    {                        
                        $facility_list[$ticket_date][] = $fac_id;
                        $ticket_array[$ticket_date][$fac_id] =[];
                        $adult_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_adult');
                        $child_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_child');
                        $ticket_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->count();
                        $totalamount[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('amount');
                        
                        $minimum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->min('ticket_no');
                        $maximum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->max('ticket_no');

                        $ticket_number[$fac_id] = $minimum_ticket_num[$fac_id]." - ".$maximum_ticket_num[$fac_id];
                        
                        if($ticket_count[$fac_id]!=0)
                        {
                            $n++;
                            if(isset($ticket_facility_arr->facility_id))
                                $facility_name[$fac_id] = $ticket_facility_arr->facility->facility_name;
                            $ticket_array[$ticket_date][$fac_id]['facility_name'] = $facility_name[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['nooftickets'] = $ticket_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofadults'] = $adult_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofchildren'] = $child_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['totalamount'] = $totalamount[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['ticketnumber'] = $ticket_number[$fac_id];
                        }
                    }
                }
            }
        }
        if(($organisation_id=="All") && ($facility_id!="All"))
        {
            foreach($daterange as $date)
            {
                $ticket_date = $date->format("Y-m-d");
                $ticket_date_arr[] = $ticket_date;
                $n=0;
                $facility_list[$ticket_date] = [];
                $ticket_fac_arr[$ticket_date] = Ticket::where('facility_id',$facility_id)->where('status',1)->whereDate('show_date',$ticket_date)->select('facility_id')->groupBy('facility_id')->get('facility_id');
                foreach($ticket_fac_arr[$ticket_date] as $ticket_arr)
                {
                    $fac_id = $ticket_arr->facility_id;
                    $ticket_facility_arr = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->get();
                    if(isset($ticket_facility_arr))
                    {
                        $facility_list[$ticket_date][] = $fac_id;
                        $adult_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->sum('noof_adult');
                        $child_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->sum('noof_child');
                        $ticket_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->count();
                        $totalamount[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->sum('amount');
                        $minimum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->min('ticket_no');
                        $maximum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->max('ticket_no');

                        $ticket_number[$fac_id] = $minimum_ticket_num[$fac_id]." - ".$maximum_ticket_num[$fac_id];
                        if($ticket_count[$fac_id]!=0)
                        {
                            $n++;
                            if(isset($ticket_facility_arr->facility_id))
                                $facility_name[$fac_id] = $ticket_facility_arr->facility->facility_name;
                            $ticket_array[$ticket_date][$fac_id]['facility_name'] = $facility_name[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['nooftickets'] = $ticket_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofadults'] = $adult_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofchildren'] = $child_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['totalamount'] = $totalamount[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['ticketnumber'] = $ticket_number[$fac_id];
                        }
                    }
                }
            }
        }
        
        if(($organisation_id!="All") && ($facility_id=="All"))
        {
            $org_arr = Organisation::where('id',$organisation_id)->first();
            foreach($daterange as $date)
            {
                $ticket_date = $date->format("Y-m-d");
                $ticket_date_arr[] = $ticket_date;
                $n=0;
                $facility_list[$ticket_date] = [];
                $ticket_fac_arr[$ticket_date] = Ticket::where('status',1)->whereDate('show_date',$ticket_date)->select('facility_id')->groupBy('facility_id')->get('facility_id');
                foreach($ticket_fac_arr[$ticket_date] as $ticket_arr)
                {
                    $fac_id = $ticket_arr->facility_id;
                    $ticket_facility_arr = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->first();
                    if(isset($ticket_facility_arr))
                    {
                        $facility_list[$ticket_date][] = $fac_id;
                        $adult_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_adult');
                        $child_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_child');
                        $ticket_count[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->count();
                        $totalamount[$fac_id] = Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('amount');
                        $minimum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->min('ticket_no');
                        $maximum_ticket_num[$fac_id] =  Ticket::where('facility_id',$fac_id)->with('facility')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->max('ticket_no');

                        $ticket_number[$fac_id] = $minimum_ticket_num[$fac_id]." - ".$maximum_ticket_num[$fac_id];
                        if($ticket_count[$fac_id]!=0)
                        {
                            if(isset($ticket_facility_arr->facility_id))
                                $facility_name[$fac_id] = $ticket_facility_arr->facility->facility_name;
                            $n++;
                            $ticket_array[$ticket_date][$fac_id]['facility_name'] = $facility_name[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['nooftickets'] = $ticket_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofadults'] = $adult_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['noofchildren'] = $child_count[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['totalamount'] = $totalamount[$fac_id];
                            $ticket_array[$ticket_date][$fac_id]['ticketnumber'] = $ticket_number[$fac_id];
                        }
                    }
                }
            }
        }
        
        //dd($ticket_array);
        return view('Reports/facility_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','ticket_array','ticket_date_arr','date_from','date_to','facility_list','org_arr','facilities','ticket_number'));
    }

    //////////facility detailed report////////////////////////////
    public function detailed_report()
    {
        $page_title = 'Facility Detailed Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
        }
        else
        {
            $organisations = Organisation::all();
        }
        return view('Reports/detailed_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id'));
    }
    public function detailed_report_list(Request $request)
    {
        $page_title = 'Facility Detailed Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_date_arr =[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $organisation_id = $request->organisation_id;
        if($organisation_id!="All")
        {
            $org_arr = Organisation::where('id',$organisation_id)->first();
            if(isset($org_arr))
            {
                $org_name = $org_arr->organisation_name."(".$org_arr->city.")";
            }
        }
        else
        {
            $org_name = "All";
        }
        $n=0;
        $daterange = new DatePeriod($begin, $interval ,$end);
        foreach($daterange as $date)
        {
            $ticket_date = $date->format("Y/m/d");
            $ticket_date_arr[] = $ticket_date;
            $ticket_query[$ticket_date] = Ticket::with('facility','invoice')->where('status',1)->whereDate('show_date',$ticket_date)->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
        }
        //dd($ticket_query);
        return view('Reports/detailed_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','ticket_date_arr','date_from','date_to','ticket_query','org_name'));
    }

    //////////financial report////////////////////////////
    public function financial_report()
    {
        $page_title = 'Financial Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
            $users = User::where('status',1)->where('organisation_id',$organisation_id)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        else
        {
            $organisations = Organisation::all();
            $users = User::where('status',1)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        return view('Reports/financial_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','users','organisation_id','organisations'));
    }
    public function financial_report_list(Request $request)
    {
        $page_title = 'Financial Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_date_arr =[];
        $spl_ticket_date_arr=[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $user_id = $request->user_id;
        $organisation_id = $request->organisation_id;
        //dd($daterange);
        $n=0;
        
        if($user_id!="All")
        {
            $ticket_query= [];
            $ticket_array=[];
            $spl_ticket_query = [];
            $spl_ticket_array = [];
            $flg =[];
            $splflg = [];
            $facilities = Facility::where('active_flag',1)->get();
            foreach($facilities as $facility)
            {
                $facility_id = $facility->id;
                //echo "facility_id=$facility_id...<br>";
                //$ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where('status',1)->whereBetween(DB::raw('DATE(show_date)'), array($date_from, $date_to))->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',0); })->whereHas('invoice', function($inv) use($user_id){ $inv->where('user_id',$user_id)->where('status',1); })->get();
                $ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',0); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                $n =0;
                $from_time = [];
                $to_time = [];
                $adult_count = [];
                $child_count = [];
                $totalamount = [];
                $show_date = [];
                
                foreach($ticket_query[$facility_id] as $tickets)
                {
                    $flg[$facility_id] = 1;
                    //echo "flg = ".$flg[$facility_id]."..<br>";
                    $n++;                    
                    $show_date[$facility_id] = $tickets->show_date;
                    //echo "show_date=$show_date...facility_id=$facility_id....ticket_date=$ticket_date....<br>";
                    $from_time[$facility_id] = $tickets->showtime->from_time;
                    $to_time[$facility_id] = $tickets->showtime->to_time;
                    $adult_count[$facility_id] = $tickets->noof_adult;
                    $child_count[$facility_id] = $tickets->noof_child;
                    $totalamount[$facility_id] = $tickets->amount;
                    if($adult_count[$facility_id])
                    {
                        $ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date[$facility_id]));
                        $ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                        $ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                        $ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                        $ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                    }
                }
                ////special shows
                $spl_ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',1); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                $n =0;
                $from_time = [];
                $to_time = [];
                $adult_count = [];
                $child_count = [];
                $totalamount = [];
                $show_date = [];
                foreach($spl_ticket_query[$facility_id] as $spl_tickets)
                {
                    $splflg[$facility_id] = 1;
                    //echo "inside..<br>";
                    $n++;
                    $show_date[$facility_id] = $spl_tickets->show_date;
                    //echo "show_date=$show_date...facility_id=$facility_id....ticket_date=$ticket_date....<br>";
                    $from_time[$facility_id] = $spl_tickets->showtime->from_time;
                    $to_time[$facility_id] = $spl_tickets->showtime->to_time;
                    $adult_count[$facility_id] = $spl_tickets->noof_adult;
                    $child_count[$facility_id] = $spl_tickets->noof_child;
                    $totalamount[$facility_id] = $spl_tickets->amount;
                    if($adult_count[$facility_id])
                    {
                        $spl_ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date[$facility_id]));
                        $spl_ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                        $spl_ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                        $spl_ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                        $spl_ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                    }
                }


            }
        }
        if($user_id=="All")
        {
            $ticket_query= [];
            $ticket_array=[];
            $spl_ticket_query = [];
            $spl_ticket_array = [];
            $flg =[];
            $splflg = [];
            $users = User::where('status',1)->get();
            foreach($users as $user)
            {
                $user_id = $user->id;
                $facilities = Facility::where('active_flag',1)->get();
                foreach($facilities as $facility)
                {
                    $facility_id = $facility->id;
                    $ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',0); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                    $n =0;
                    $from_time = [];
                    $to_time = [];
                    $adult_count = [];
                    $child_count = [];
                    $totalamount = [];
                    $show_date = [];
                    if(isset($ticket_query[$facility_id]))
                    {
                        foreach($ticket_query[$facility_id] as $tickets)
                        {
                            $flg[$facility_id] = 1;
                            $n++;                    
                            $show_date = $tickets->show_date;
                            $from_time [$facility_id]= $tickets->showtime->from_time;
                            $to_time[$facility_id] = $tickets->showtime->to_time;
                            $adult_count[$facility_id] = $tickets->noof_adult;
                            $child_count[$facility_id] = $tickets->noof_child;
                            $totalamount[$facility_id] = $tickets->amount;
                            
                            if($adult_count[$facility_id])
                            {
                                $ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date));
                                $ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                                $ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                                $ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                                $ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                            }
                        }
                    }
                    ////special shows
                    $spl_ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',1); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                    $n =0;
                    $from_time = [];
                    $to_time = [];
                    $adult_count = [];
                    $child_count = [];
                    $totalamount = [];
                    $show_date = [];
                    foreach($spl_ticket_query[$facility_id] as $spl_tickets)
                    {
                        $splflg[$facility_id] = 1;
                        $n++;
                        $show_date[$facility_id] = $spl_tickets->show_date;
                        $from_time[$facility_id] = $spl_tickets->showtime->from_time;
                        $to_time[$facility_id] = $spl_tickets->showtime->to_time;
                        $adult_count[$facility_id] = $spl_tickets->noof_adult;
                        $child_count[$facility_id] = $spl_tickets->noof_child;
                        $totalamount[$facility_id] = $spl_tickets->amount;
                        if($adult_count[$facility_id])
                        {
                            $spl_ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date[$facility_id]));
                            $spl_ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                            $spl_ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                            $spl_ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                            $spl_ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                        }
                    }                    
                }
            }
        }
        //dd($ticket_array);
        return view('Reports/financial_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','ticket_date_arr','date_from','date_to','ticket_array','facilities','ticket_query','spl_ticket_date_arr','spl_ticket_array','flg','user_id','splflg','organisation_id'));
    }

    public function financial_report_pdf(Request $request)
    {
        $page_title = 'Financial Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_date_arr =[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $user_id = $request->user_id;
        $organisation_id = $request->organisation_id;
        if($organisation_id!="")
        {
            $org_arr = Organisation::where('id',$organisation_id)->first();
            if(isset($org_arr))
            {
                $org_name = $org_arr->organisation_name."(".$org_arr->city.")";
            }
        }
        else
        {
            $org_name = "All Organisations";
        }
        if($user_id!="All")
        {
            $users = User::where('id',$user_id)->first();
            if(isset($users))
            {
                $username = "Counter User : ".$users->name;
            }
        }
        else
        {
            $username = "All Counter Users";
        }

        $n=0;
        if($user_id!="All")
        {
            $ticket_query= [];
            $ticket_array=[];
            $spl_ticket_query = [];
            $spl_ticket_array = [];
            $flg =[];
            $splflg = [];
            $facilities = Facility::where('active_flag',1)->get();
            foreach($facilities as $facility)
            {
                $facility_id = $facility->id;
                $ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',0); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                $n =0;
                $from_time = [];
                $to_time = [];
                $adult_count = [];
                $child_count = [];
                $totalamount = [];
                $show_date = [];
                foreach($ticket_query[$facility_id] as $tickets)
                {
                    $flg[$facility_id] = 1;
                    //echo "flg = ".$flg[$facility_id]."..<br>";
                    $n++;                    
                    $show_date[$facility_id] = $tickets->show_date;
                    //echo "show_date=$show_date...facility_id=$facility_id....ticket_date=$ticket_date....<br>";
                    $from_time[$facility_id] = $tickets->showtime->from_time;
                    $to_time[$facility_id] = $tickets->showtime->to_time;
                    $adult_count[$facility_id] = $tickets->noof_adult;
                    $child_count[$facility_id] = $tickets->noof_child;
                    $totalamount[$facility_id] = $tickets->amount;
                    if($adult_count[$facility_id])
                    {
                        $ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date[$facility_id]));
                        $ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                        $ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                        $ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                        $ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                    }
                }
                ////special shows
                $spl_ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',1); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                $n =0;
                $from_time = [];
                $to_time = [];
                $adult_count = [];
                $child_count = [];
                $totalamount = [];
                $show_date = [];
                foreach($spl_ticket_query[$facility_id] as $spl_tickets)
                {
                    $splflg[$facility_id] = 1;
                    //echo "inside..<br>";
                    $n++;
                    $show_date[$facility_id] = $spl_tickets->show_date;
                    //echo "show_date=$show_date...facility_id=$facility_id....ticket_date=$ticket_date....<br>";
                    $from_time[$facility_id] = $spl_tickets->showtime->from_time;
                    $to_time[$facility_id] = $spl_tickets->showtime->to_time;
                    $adult_count[$facility_id] = $spl_tickets->noof_adult;
                    $child_count[$facility_id] = $spl_tickets->noof_child;
                    $totalamount[$facility_id] = $spl_tickets->amount;
                    if($adult_count[$facility_id])
                    {
                        $spl_ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date[$facility_id]));
                        $spl_ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                        $spl_ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                        $spl_ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                        $spl_ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                    }
                }
            }
        }
        if($user_id=="All")
        {
            $ticket_query= [];
            $ticket_array=[];
            $spl_ticket_query = [];
            $spl_ticket_array = [];
            $flg =[];
            $splflg = [];
            $users = User::where('status',1)->get();
            foreach($users as $user)
            {
                $user_id = $user->id;
                $facilities = Facility::where('active_flag',1)->get();
                foreach($facilities as $facility)
                {
                    $facility_id = $facility->id;
                    $ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',0); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                    $n =0;
                    $from_time = [];
                    $to_time = [];
                    $adult_count = [];
                    $child_count = [];
                    $totalamount = [];
                    $show_date = [];
                    if(isset($ticket_query[$facility_id]))
                    {
                        foreach($ticket_query[$facility_id] as $tickets)
                        {
                            $flg[$facility_id] = 1;
                            $n++;                    
                            $show_date = $tickets->show_date;
                            $from_time [$facility_id]= $tickets->showtime->from_time;
                            $to_time[$facility_id] = $tickets->showtime->to_time;
                            $adult_count[$facility_id] = $tickets->noof_adult;
                            $child_count[$facility_id] = $tickets->noof_child;
                            $totalamount[$facility_id] = $tickets->amount;
                            if($adult_count[$facility_id])
                            {
                                $ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date));
                                $ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                                $ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                                $ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                                $ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                            }
                        }
                    }
                    ////special shows
                    $spl_ticket_query[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('facility','showtime','invoice')->whereHas('showtime',function($showt) { $showt->where('show_flag',1); })->whereHas('invoice', function($inv) use($user_id,$date_from,$date_to){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->get();
                    $n =0;
                    $from_time = [];
                    $to_time = [];
                    $adult_count = [];
                    $child_count = [];
                    $totalamount = [];
                    $show_date = [];
                    foreach($spl_ticket_query[$facility_id] as $spl_tickets)
                    {
                        $splflg[$facility_id] = 1;
                        $n++;
                        $show_date[$facility_id] = $spl_tickets->show_date;
                        $from_time[$facility_id] = $spl_tickets->showtime->from_time;
                        $to_time[$facility_id] = $spl_tickets->showtime->to_time;
                        $adult_count[$facility_id] = $spl_tickets->noof_adult;
                        $child_count[$facility_id] = $spl_tickets->noof_child;
                        $totalamount[$facility_id] = $spl_tickets->amount;
                        if($adult_count[$facility_id])
                        {
                            $spl_ticket_array[$facility_id][$n]['date'] = date("d-M-Y", strtotime($show_date[$facility_id]));
                            $spl_ticket_array[$facility_id][$n]['showtime'] = $from_time[$facility_id]." to ".$to_time[$facility_id];
                            $spl_ticket_array[$facility_id][$n]['noofadults'] = $adult_count[$facility_id];
                            $spl_ticket_array[$facility_id][$n]['noofchildren'] = $child_count[$facility_id];
                            $spl_ticket_array[$facility_id][$n]['amount'] = $totalamount[$facility_id];
                        }
                    }                    
                }
            }
        }
        //dd($ticket_date_arr);
        //return view('Reports/financial_report_pdf', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','ticket_date_arr','date_from','date_to','ticket_array','facilities','ticket_query','spl_ticket_array','splflg','flg','user_id'));
        $pdf = PDF::loadView('Reports.financial_report_pdf', compact('ticket_date_arr','date_from','date_to','ticket_array','facilities','ticket_query','spl_ticket_array','splflg','flg','user_id','org_name','username'));
    
        return $pdf->stream('financialreport.pdf');
    }

    ////daily log report//////////////////////////
    public function daily_log()
    {
        $page_title = 'Counter Collection Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $user_organisation_id = "";
        $organisation_id="";
        $id = Auth::user()->id; 
        $user_organisation_id = Auth::user()->organisation_id;
        $role_guard_name =[];
        $userroles = UserRoles::where('user_id',$id)->with('role')->get();
        foreach($userroles as $user_role)
        {
            $role_guard_name[] = $user_role->role->guard_name;
        }
        $str = "counteruser";
        //echo "organisation_id=$user_organisation_id....<br>";
        //if(in_array($str, $role_guard_name))
        if($user_organisation_id!="")
        {
            $organisation_id = $user_organisation_id;
            $users = User::where('status',1)->where('organisation_id',$organisation_id)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
            $organisations = Organisation::where('id',$organisation_id)->get();
            $facilities = Facility::where('organisation_id',$organisation_id)->where('active_flag',1)->orderBy('order_by','asc')->with('organisation')->get();
        }
        else
        {
            $organisations = Organisation::all();
            $users = User::where('status',1)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
            $facilities = Facility::where('active_flag',1)->orderBy('order_by','asc')->with('organisation')->get();
        }
        //dd($users);
        $payment_methods = PaymentMethod::all();
        return view('Reports/daily_log', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','users','id','payment_methods','facilities','organisations','user_organisation_id'));
    }
    public function daily_log_list(Request $request)
    {
        $page_title = 'Counter Collection Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_from = date("Y-m-d H:i:s", strtotime($date_from));
        $date_to = $request->date_to;
        $date_to = date("Y-m-d H:i:s", strtotime($date_to));
        $payment_method = $request->payment_method;
        $facility_id = $request->facility_id;
        $method_name = "";
        if($payment_method!="All")
        {
            $payment_methods = PaymentMethod::where('id',$payment_method)->first();
            if(isset($payment_methods))
            {
                $method_name = "Payment Method : ".$payment_methods->method;
            }
        }
        elseif($payment_method=="")
        {
            $method_name = "All Payment Methods";
        }
        else
        {
            $method_name = "";
        }
        $user_id = $request->user_id;
        $organisation_id = $request->organisation_id;
        $fac_name = "";
        
        if($user_id!="All")
        {
            $users = User::where('id',$user_id)->first();
            if(isset($users))
            {
                $username = "Counter User : ".$users->name;
            }
        }
        else
        {
            $username = "All Counter Users";
        }
        
        if($facility_id!="All")
        {
            $facilities = Facility::where('id',$facility_id)->first();
            if(isset($facilities))
            {
                $fac_name = "Facility : ".$facilities->facility_name;
            }
        }
        elseif($facility_id=="All")
        {
            $fac_name = "All Facilities";
        }
        else
        {
            $fac_name = "";
        }
        if($organisation_id!="All")
        {
            $org_arr = Organisation::where('id',$organisation_id)->first();
            if(isset($org_arr))
            {
                $org_name = $org_arr->organisation_name."(".$org_arr->city.")";
            }
        }
        elseif($organisation_id=="")
        {
            $org_name = "All Organisations";
        }
        else
        {
            $org_name = "";
        }
        //echo "organisation_id=$organisation_id....facility_id=$facility_id...payment_method=$payment_method..<br>";
        //dd($facility_id);
        $invoice_array = [];
        $cancelled_array = [];
        $user_idarr =[];
        $ticket_arr = [];
        $counter_user ="";
        $invoice_date = "";
        if($user_id!="All")
        {
            $user_idarr[] = $user_id;
            $tickets = Ticket::where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice','facility')->whereHas('invoice', function($inv) use($date_from, $date_to, $user_id, $organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where('user_id',$user_id); });
            if($organisation_id!="All")
            {
                $tickets = $tickets->whereHas('facility', function($fac) use($organisation_id){ $fac->where('organisation_id',$organisation_id); });
            }
            if($payment_method!="" && $payment_method!="All")
            {
                $tickets = $tickets->whereHas('invoice', function($paymethod) use($payment_method){ $paymethod->where('payment_method',$payment_method); });
            }
            if($facility_id!="All" && $facility_id!="")
            {
                $tickets = $tickets->where('facility_id',$facility_id);
            }
            $tickets = $tickets->get();
            //dd($tickets);
            foreach($tickets as $ticket)
            {
                $ticket_id = $ticket->id;
                $ticket_arr[$user_id][] = $ticket_id;
                $invoice_id = $ticket->invoice->id;
                $invoice_date = $ticket->invoice->invoice_date;
                $invoice_date_new = date("d-M-Y", strtotime($invoice_date));
                $show_date = $ticket->invoice->show_date;
                $show_date = date("d-M-Y", strtotime($show_date));
                $invoice_month = date('m',strtotime($invoice_date));
                $invoice_year = date('Y',strtotime($invoice_date));
                $invoice_no = $ticket->invoice->invoice_no;
                $org_code = $ticket->invoice->organisation->short_code;
                if($invoice_month<4)
                    $invoice_year--;
                $nextyear = $invoice_year+1;
                $invoice_number = $org_code.'/'.$invoice_no.'/'.$invoice_year.'-'.$nextyear;

                $facility_id = $ticket->facility_id;
                $facility_name = $ticket->facility->facility_name;

                $ticket_no = $org_code.'/'.$ticket->facility->short_code.'/'.$ticket->ticket_no.'/'.$invoice_year.'-'.$nextyear;

                $noofadult = $ticket->noof_adult;
                $noofchild = $ticket->noof_child;
                $amount = $ticket->amount;

                $counter_user = $ticket->invoice->user->name;
                $payment_method = "";
                if($ticket->invoice->payment_method!=NULL)
                {
                    if($ticket->invoice->paymentmethod->method)
                    {
                        $payment_method = $ticket->invoice->paymentmethod->method;
                    }
                }
                if($invoice_number)
                {
                    $invoice_array[$user_id][$ticket_id]['counter_user'] = $counter_user;
                    $invoice_array[$user_id][$ticket_id]['invoice_number'] = $invoice_number;
                    $invoice_array[$user_id][$ticket_id]['invoice_date'] = $invoice_date_new;
                    $invoice_array[$user_id][$ticket_id]['show_date'] = $show_date;
                    $invoice_array[$user_id][$ticket_id]['ticket_no'] = $ticket_no;
                    $invoice_array[$user_id][$ticket_id]['facility_name'] = $facility_name;
                    $invoice_array[$user_id][$ticket_id]['noofadult'] = $noofadult;
                    $invoice_array[$user_id][$ticket_id]['noofchild'] = $noofchild;
                    $invoice_array[$user_id][$ticket_id]['amount'] = $amount;
                    $invoice_array[$user_id][$ticket_id]['payment_method'] = $payment_method;
                    $cancelled_array[$user_id][$ticket_id]['refund_amount'] = "";
                }
            } 

            /////ticket cancelled list////////////////
            $ticket_cancelledlist = TicketCancelApprove::where('approval_status',1)->where('user_id',$user_id)->whereBetween(DB::raw('DATE(cancel_date)'), array($date_from, $date_to))->with('ticket','showtime','user')->get();
            foreach($ticket_cancelledlist as $ticket_cancelled)
            {
                $ticket_id = $ticket_cancelled->id;
                $ticket_arr[$user_id][] = $ticket_id;
                $invoice_id = $ticket_cancelled->ticket->invoice->id;
                $invoice_date = $ticket_cancelled->ticket->invoice->invoice_date;
                $invoice_date_new = date("d-M-Y", strtotime($invoice_date));
                $show_date = $ticket_cancelled->show_date;
                $show_date = date("d-M-Y", strtotime($show_date));
                $invoice_month = date('m',strtotime($invoice_date));
                $invoice_year = date('Y',strtotime($invoice_date));
                $invoice_no = $ticket_cancelled->ticket->invoice->invoice_no;
                $org_code = $ticket_cancelled->ticket->invoice->organisation->short_code;
                if($invoice_month<4)
                    $invoice_year--;
                $nextyear = $invoice_year+1;
                $invoice_number = $org_code.'/'.$invoice_no.'/'.$invoice_year.'-'.$nextyear;

                $facility_id = $ticket_cancelled->ticket->facility_id;
                $facility_name = $ticket_cancelled->ticket->facility->facility_name;

                $ticket_no = $org_code.'/'.$ticket_cancelled->ticket->facility->short_code.'/'.$ticket_cancelled->ticket->ticket_no.'/'.$invoice_year.'-'.$nextyear;

                $noofadult = $ticket_cancelled->noof_adult;
                $noofchild = $ticket_cancelled->noof_child;
                $refund_amount = $ticket_cancelled->refund_amount;
                $cancelled_amount = $refund_amount;
                $refund_amount = '-'.$refund_amount;

                $counter_user = $ticket_cancelled->user->name;
                $payment_method = "";
                if($ticket_cancelled->ticket->invoice->payment_method!=NULL)
                {
                    if($ticket_cancelled->ticket->invoice->paymentmethod->method)
                    {
                        $payment_method = $ticket_cancelled->ticket->invoice->paymentmethod->method;
                    }
                }
                if($invoice_number)
                {
                    $cancelled_array[$user_id][$ticket_id]['counter_user'] = $counter_user;
                    $cancelled_array[$user_id][$ticket_id]['invoice_number'] = $invoice_number;
                    $cancelled_array[$user_id][$ticket_id]['invoice_date'] = $invoice_date_new;
                    $cancelled_array[$user_id][$ticket_id]['show_date'] = $show_date;
                    $cancelled_array[$user_id][$ticket_id]['ticket_no'] = $ticket_no;
                    $cancelled_array[$user_id][$ticket_id]['facility_name'] = $facility_name;
                    $cancelled_array[$user_id][$ticket_id]['noofadult'] = $noofadult;
                    $cancelled_array[$user_id][$ticket_id]['noofchild'] = $noofchild;
                    $cancelled_array[$user_id][$ticket_id]['amount'] = $refund_amount;
                    $cancelled_array[$user_id][$ticket_id]['payment_method'] = $payment_method;
                    $cancelled_array[$user_id][$ticket_id]['refund_amount'] = $cancelled_amount;
                }
            }
            /////////////////////////////////////////////
        }
        if($user_id=="All")
        {
            $users = User::where('status',1)->get();
            foreach($users as $user)
            {
                $user_id = $user->id;
                $user_idarr[] = $user_id;
            }
            $tickets = Ticket::where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice','facility')->whereHas('invoice', function($inv) use($date_from, $date_to, $user_id, $organisation_id){ $inv->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); });

            if(($organisation_id!="All") && ($organisation_id!=""))
            {
                $tickets = $tickets->whereHas('facility', function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); });
            }
            if(($payment_method!="") && ($payment_method!="All"))
            {
                $tickets = $tickets->whereHas('invoice', function($paymethod) use($payment_method) { $paymethod->where('payment_method',$payment_method); });
            }
            if(($facility_id!="All") && ($facility_id!=""))
            {
                $tickets = $tickets->where('facility_id',$facility_id);
            }
            $tickets = $tickets->get();
            //dd($tickets);
            foreach($tickets as $ticket)
            {
                $user_id = $ticket->invoice->user_id;
                $ticket_id = $ticket->id;
                $ticket_arr[$user_id][] = $ticket_id;
                $invoice_id = $ticket->invoice->id;
                $invoice_date = $ticket->invoice->invoice_date;
                $invoice_date_new = date("d-M-Y", strtotime($invoice_date));
                $show_date = $ticket->invoice->show_date;
                $show_date = date("d-M-Y", strtotime($show_date));
                $invoice_month = date('m',strtotime($invoice_date));
                $invoice_year = date('Y',strtotime($invoice_date));
                $invoice_no = $ticket->invoice->invoice_no;
                $org_code = $ticket->invoice->organisation->short_code;
                if($invoice_month<4)
                    $invoice_year--;
                $nextyear = $invoice_year+1;
                $invoice_number = $org_code.'/'.$invoice_no.'/'.$invoice_year.'-'.$nextyear;

                $facility_id = $ticket->facility_id;
                $facility_name = $ticket->facility->facility_name;

                $ticket_no = $org_code.'/'.$ticket->facility->short_code.'/'.$ticket->ticket_no.'/'.$invoice_year.'-'.$nextyear;

                $noofadult = $ticket->noof_adult;
                $noofchild = $ticket->noof_child;
                $amount = $ticket->amount;
                $payment_method = "";
                if($ticket->invoice->payment_method!=NULL)
                {
                    if($ticket->invoice->paymentmethod->method)
                    {
                        $payment_method = $ticket->invoice->paymentmethod->method;
                    }
                }
                if(isset($ticket->invoice->user->name))
                    $counter_user = $ticket->invoice->user->name;
                if($invoice_number)
                {
                    $invoice_array[$user_id][$ticket_id]['counter_user'] = $counter_user;
                    $invoice_array[$user_id][$ticket_id]['invoice_number'] = $invoice_number;
                    $invoice_array[$user_id][$ticket_id]['invoice_date'] = $invoice_date_new;
                    $invoice_array[$user_id][$ticket_id]['show_date'] = $show_date;
                    $invoice_array[$user_id][$ticket_id]['ticket_no'] = $ticket_no;
                    $invoice_array[$user_id][$ticket_id]['facility_name'] = $facility_name;
                    $invoice_array[$user_id][$ticket_id]['noofadult'] = $noofadult;
                    $invoice_array[$user_id][$ticket_id]['noofchild'] = $noofchild;
                    $invoice_array[$user_id][$ticket_id]['amount'] = $amount;
                    $invoice_array[$user_id][$ticket_id]['payment_method'] = $payment_method;
                    $cancelled_array[$user_id][$ticket_id]['refund_amount'] = "";
                }
            }

            /////ticket cancelled list////////////////
            $ticket_cancelledlist=TicketCancelApprove::where('approval_status',1)->whereBetween(DB::raw('DATE(cancel_date)'), array($date_from, $date_to))->with('ticket','showtime','user')->get();
            foreach($ticket_cancelledlist as $ticket_cancelled)
            {
                $ticket_id = $ticket_cancelled->id;
                $ticket_arr[$user_id][] = $ticket_id;
                $invoice_id = $ticket_cancelled->ticket->invoice->id;
                $invoice_date = $ticket_cancelled->ticket->invoice->invoice_date;
                $invoice_date_new = date("d-M-Y", strtotime($invoice_date));
                $show_date = $ticket_cancelled->show_date;
                $show_date = date("d-M-Y", strtotime($show_date));
                $invoice_month = date('m',strtotime($invoice_date));
                $invoice_year = date('Y',strtotime($invoice_date));
                $invoice_no = $ticket_cancelled->ticket->invoice->invoice_no;
                $org_code = $ticket_cancelled->ticket->invoice->organisation->short_code;
                if($invoice_month<4)
                    $invoice_year--;
                $nextyear = $invoice_year+1;
                $invoice_number = $org_code.'/'.$invoice_no.'/'.$invoice_year.'-'.$nextyear;

                $facility_id = $ticket_cancelled->ticket->facility_id;
                $facility_name = $ticket_cancelled->ticket->facility->facility_name;

                $ticket_no = $org_code.'/'.$ticket_cancelled->ticket->facility->short_code.'/'.$ticket_cancelled->ticket->ticket_no.'/'.$invoice_year.'-'.$nextyear;

                $noofadult = $ticket_cancelled->noof_adult;
                $noofchild = $ticket_cancelled->noof_child;
                $refund_amount = $ticket_cancelled->refund_amount;
                $cancelled_amount = $refund_amount;
                $refund_amount = '-'.$refund_amount;

                $counter_user = $ticket_cancelled->user->name;
                $payment_method = "";
                if($ticket_cancelled->ticket->invoice->payment_method!=NULL)
                {
                    if($ticket_cancelled->ticket->invoice->paymentmethod->method)
                    {
                        $payment_method = $ticket_cancelled->ticket->invoice->paymentmethod->method;
                    }
                }
                if($invoice_number)
                {
                    $cancelled_array[$user_id][$ticket_id]['counter_user'] = $counter_user;
                    $cancelled_array[$user_id][$ticket_id]['invoice_number'] = $invoice_number;
                    $cancelled_array[$user_id][$ticket_id]['invoice_date'] = $invoice_date_new;
                    $cancelled_array[$user_id][$ticket_id]['show_date'] = $show_date;
                    $cancelled_array[$user_id][$ticket_id]['ticket_no'] = $ticket_no;
                    $cancelled_array[$user_id][$ticket_id]['facility_name'] = $facility_name;
                    $cancelled_array[$user_id][$ticket_id]['noofadult'] = $noofadult;
                    $cancelled_array[$user_id][$ticket_id]['noofchild'] = $noofchild;
                    $cancelled_array[$user_id][$ticket_id]['amount'] = $refund_amount;
                    $cancelled_array[$user_id][$ticket_id]['payment_method'] = $payment_method;
                    $cancelled_array[$user_id][$ticket_id]['refund_amount'] = $cancelled_amount;
                }
            }
            /////////////////////////////////////////////
        }
        //dd($invoice_array);
        return view('Reports/daily_log_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','invoice_date','invoice_array','ticket_arr','user_idarr','payment_methods','date_from','date_to','cancelled_array','org_name','username','fac_name','method_name'));
    }

    //////////refund report////////////////////////////
    public function refund_report()
    {
        $page_title = 'Refund Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
    
        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
        }
        else
        {
            $organisations = Organisation::all();
        }
        return view('Reports/refund_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id'));
    }
    public function refund_report_list(Request $request)
    {
        $page_title = 'Refund Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_from = date("Y-m-d H:i:s", strtotime($date_from));
        $date_to = $request->date_to;
        $date_to = date("Y-m-d H:i:s", strtotime($date_to));
        $organisation_id = $request->organisation_id;
        $organisations = Organisation::where('id',$organisation_id)->first();
        $refunds = Refund::whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('ticket','invoice')->whereHas('invoice',function($inv) use($organisation_id) { $inv->where('organisation_id',$organisation_id); })->get();
    
        return view('Reports/refund_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','refunds','date_from','date_to','organisations'));
    }

    //////////mail log report////////////////////////////
    public function mail_log_report()
    {
        $page_title = 'Mail Log';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $id = Auth::user()->id; 
        $user_org = User::where('status',1)->where('id',$id)->first();
        if(isset($user_org))
        {
            $organisation_id = $user_org->organisation_id;
        }
        return view('Reports/mail_log_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisation_id'));
    }
    public function mail_log_list(Request $request)
    {
        $columns = array(
                            0=> 'id',
                            1=> 'mail_id',
                            2=> 'mail_content',
                            3=> 'subject',
                            4=> 'mail_time',
                             );

        $totalData = MailLog::count();
        $totalFiltered = $totalData; 

        $limit = $request->input('length');
        $start = $request->input('start');
        $order = $columns[$request->input('order.0.column')];
        $dir = $request->input('order.0.dir');
            
        if(empty($request->input('search.value')))
        {            
            $posts = MailLog::offset($start)
                         ->limit($limit)
                         ->orderBy('id',$dir)
                         ->get();
        }
        else {
            $search = $request->input('search.value'); 

            $posts =  MailLog::where('mail_id','LIKE',"%{$search}%")
                            ->orWhere('mail_content', 'LIKE',"%{$search}%")
                            ->orWhere('subject', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();

            $totalFiltered = MailLog::where('mail_id','LIKE',"%{$search}%")
                             ->orWhere('mail_content', 'LIKE',"%{$search}%")
                             ->orWhere('subject', 'LIKE',"%{$search}%")                             
                             ->count();
        }
        $data = array();
        if(!empty($posts))
        {
            $n=0;
            foreach ($posts as $post)
            {
                $n++;
                $nestedData['DT_RowIndex'] = $n;
                $nestedData['mail_id'] = $post->mail_id;
                $nestedData['mail_content'] = $post->mail_content;
                $nestedData['subject'] = $post->subject;
                $nestedData['mail_time'] = $post->mail_time;
                $data[] = $nestedData;
            }
        }
        $json_data = array(
                    "draw"            => intval($request->input('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );
        echo json_encode($json_data);  
    }

    //////////sms log report////////////////////////////
    public function sms_log_report()
    {
        $page_title = 'SMS Log';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        return view('Reports/sms_log_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class'));
    }
    public function sms_log_list(Request $request)
    {

        $columns = array(
                            0=> 'id',
                            1=> 'mobile_number',
                            2=> 'sms_content',
                            3=> 'sms_functionality',
                            4=> 'curl_output',
                            5=> 'curl_output_code',
                            6=> 'sms_time',
                             );

        $totalData = SMSLog::count();
        $totalFiltered = $totalData; 

        $limit = $request->input('length');
        $start = $request->input('start');
        $order = $columns[$request->input('order.0.column')];
        $dir = $request->input('order.0.dir');
            
        if(empty($request->input('search.value')))
        {            
            $posts = SMSLog::offset($start)
                         ->limit($limit)
                         ->orderBy('id',$dir)
                         ->get();
        }
        else {
            $search = $request->input('search.value'); 

            $posts =  SMSLog::where('mobile_number','LIKE',"%{$search}%")
                            ->orWhere('sms_content', 'LIKE',"%{$search}%")
                            ->orWhere('sms_functionality', 'LIKE',"%{$search}%")
                            ->offset($start)
                            ->limit($limit)
                            ->orderBy($order,$dir)
                            ->get();

            $totalFiltered = SMSLog::where('mobile_number','LIKE',"%{$search}%")
                             ->orWhere('sms_content', 'LIKE',"%{$search}%")
                             ->orWhere('sms_functionality', 'LIKE',"%{$search}%")                             
                             ->count();
        }
        $data = array();
        if(!empty($posts))
        {
            $n=0;
            foreach ($posts as $post)
            {
                $n++;
                $nestedData['DT_RowIndex'] = $n;
                $nestedData['mobile_number'] = $post->mobile_number;
                $nestedData['sms_content'] = $post->sms_content;
                $nestedData['sms_functionality'] = $post->sms_functionality;
                $nestedData['curl_output'] = $post->curl_output;
                $nestedData['curl_output_code'] = $post->curl_output_code;
                $nestedData['sms_time'] = $post->sms_time;
                $data[] = $nestedData;
            }
        }
        $json_data = array(
                    "draw"            => intval($request->input('draw')),  
                    "recordsTotal"    => intval($totalData),  
                    "recordsFiltered" => intval($totalFiltered), 
                    "data"            => $data   
                    );
        echo json_encode($json_data);  
    }

    //////////transaction report////////////////////////////
    public function transaction_report()
    {
        $page_title = 'Transaction Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
        }
        else
        {
            $organisations = Organisation::all();
        }
        return view('Reports/transaction_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id'));
    }
    public function transaction_list(Request $request)
    {
        $page_title = 'Transaction Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_from = date("Y-m-d H:i:s", strtotime($date_from));
        $date_to = $request->date_to;
        $date_to = date("Y-m-d H:i:s", strtotime($date_to));
        $organisation_id = $request->organisation_id;
        $organisations = Organisation::where('id',$organisation_id)->first();
        $transactions = Transaction::where('organisation_id',$organisation_id)->whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('organisation','publicuser')->orderBy('id','desc')->get();
    
        return view('Reports/transaction_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','transactions','date_from','date_to','organisations'));
    }
    public function transaction_view($id)
    {
        $page_title = 'Transaction Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $transaction = Transaction::where('id',$id)->with('organisation','publicuser')->orderBy('id','desc')->first();
        return view('Reports/transaction_view', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','transaction'));
    }
    public function transaction_status(Request $request)
    {
        $transaction_id = $request->transaction_id;
        $checkcount =0;
        $transactions = Transaction::where('id',$transaction_id)->get();
        //$transactions = Transaction::where('id',52)->get();  ///for testing
        foreach($transactions as $transaction)
        {
            $transaction_id = $transaction->id;
            $mid = $transaction->mid;
            $encykey = $transaction->encykey;
            $order_id = $transaction->order_id;
            //generate merchant request 
            include 'worldline/AWLMEAPI.php';
            //generate merchant request from Helper file
            //create an Object of the above included class
            $obj = new \AWLMEAPI();
            //create an object of Request Message
            $pgMeTrnRefNo ="";
            $resMsgDTO = $obj->getTransactionStatus($mid , $order_id , $pgMeTrnRefNo , $encykey);
            //dd($resMsgDTO);
            $trnsrefno = $resMsgDTO->getPgMeTrnRefNo();
            $trnsamount = $resMsgDTO->getTrnAmt();
            $trnsstatuscode = $resMsgDTO->getStatusCode();
            $trnsstatusdesc = $resMsgDTO->getStatusDesc();
            $trnsreqdate = $resMsgDTO->getTrnReqDate();
            $trnsresponsecode = $resMsgDTO->getResponseCode();
            $trns_rrn = $resMsgDTO->getRrn();
            $trnsauthzcode = $resMsgDTO->getAuthZCode();

            ///update transaction table
            $checkcount++;
            $transaction_checkcount_upd = Transaction::where('id',$transaction_id)->first();
            $transaction_checkcount_upd->checkcount = $checkcount;
            $transaction_checkcount_upd->save();
            /////////
            if($trnsstatuscode=="S")
            {
                ///update transaction table
                $transaction_upd = Transaction::where('id',$transaction_id)->first();
                $transaction_upd->status = 1;
                $transaction_upd->transaction_refno = $trnsrefno;
                $transaction_upd->status_code = $trnsstatuscode;
                $transaction_upd->status_desc = $trnsstatusdesc;
                $transaction_upd->transaction_req_date = $trnsreqdate;
                $transaction_upd->response_code = $trnsresponsecode;
                $transaction_upd->rrn = $trns_rrn;
                $transaction_upd->auth_zcode = $trnsauthzcode;
                $transaction_upd->save();
                ////update invoice table
                $invocies = Invoice::where('transaction_id',$transaction_id)->first();
                $invoice_id = $invoices->id;
                $invoices->status = 1;
                $invoices->save();
                ////update ticket table
                $ticekts = Ticket::where('invoice_id',$invoice_id)->get();
                foreach($ticekts as $ticket)
                {
                    $ticket_id = $ticket->id;
                    $ticket_upd = Ticket::where('id',$ticket_id)->first();
                    $ticket_upd->status = 1;
                    $ticket_upd->save();
                    ////update ticket seat table
                    $ticket_seats = TicketSeat::where('ticket_id',$ticket_id)->get();
                    foreach($ticket_seats as $ticket_seat)
                    {
                        $ticket_seat_id = $ticket_seat->id;
                        $ticket_seat_upd = TicketSeat::where('id',$ticket_seat_id)->first();
                        $ticket_seat_upd->status = 1;
                        $ticket_seat_upd->save();
                    }
                }
            }
            if($trnsstatuscode=="S")
            {
                ///update transaction table
                $transaction_upd = Transaction::where('id',$transaction_id)->first();
                $transaction_upd->status = 2;
                $transaction_upd->transaction_refno = $trnsrefno;
                $transaction_upd->status_code = $trnsstatuscode;
                $transaction_upd->status_desc = $trnsstatusdesc;
                $transaction_upd->transaction_req_date = $trnsreqdate;
                $transaction_upd->response_code = $trnsresponsecode;
                $transaction_upd->rrn = $trns_rrn;
                $transaction_upd->auth_zcode = $trnsauthzcode;
                $transaction_upd->save();
            }

        }
    }

    //////////online ticket report////////////////////////////
    public function online_ticket_report()
    {
        $page_title = 'Online Collection Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $facilities = Facility::where('organisation_id',$organisation_id)->where('active_flag',1)->with('organisation')->orderBy('order_by','asc')->get();  
            $organisations = Organisation::where('id',$organisation_id)->get();
        }
        else
        {
            $facilities = Facility::where('active_flag',1)->with('organisation')->orderBy('order_by','asc')->get();  
            $organisations = Organisation::all();
        }
        return view('Reports/online_ticket_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','facilities','organisations','organisation_id'));
    }
    public function online_ticket_list(Request $request)
    {
        $page_title = 'Online Collection Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_from = date("Y-m-d H:i:s", strtotime($date_from));
        $date_to = $request->date_to;
        $date_to = date("Y-m-d H:i:s", strtotime($date_to));
        $facility_id = $request->facility_id;
        $organisation_id = $request->organisation_id;
        $fac_name ="";
        if($facility_id!="")
        {
            $facilities = Facility::where('id',$facility_id)->first();
            if(isset($facilities))
            {
                $fac_name = "Facility : ".$facilities->facility_name;
            }
        }
        else
        {
            $fac_name = "All Facilities";
        }
        
        if($organisation_id!="")
        {
            $org_arr = Organisation::where('id',$organisation_id)->first();
            if(isset($org_arr))
            {
                $org_name = $org_arr->organisation_name."(".$org_arr->city.")";
            }
        }
        else
        {
            $org_name = "All Organisations";
        }
        
        $invoice_array = [];
        $ticket_arr = [];
        $cancelled_array = [];
        //echo "facility_id = $facility_id";
        $tickets = Ticket::where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice','facility')->whereHas('invoice', function($inv) use($date_from, $date_to, $organisation_id) { $inv->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->whereNotNull('public_user_id')->where('organisation_id',$organisation_id)->where('payment_type',1)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); }); });
        if($facility_id && $facility_id!='All')
        {
            $tickets = $tickets->where('facility_id',$facility_id);
        }
        $tickets = $tickets->get();
        //dd($tickets);
        foreach($tickets as $ticket)
        {
            $ticket_id = $ticket->id;
            $ticket_arr[] = $ticket_id;
            $invoice_id = $ticket->invoice->id;
            $invoice_date = $ticket->invoice->invoice_date;
            $invoice_date_new = date("d-M-Y", strtotime($invoice_date));
            $show_date = $ticket->invoice->show_date;
            $show_date = date("d-M-Y", strtotime($show_date));
            $invoice_month = date('m',strtotime($invoice_date));
            $invoice_year = date('Y',strtotime($invoice_date));
            $invoice_no = $ticket->invoice->invoice_no;
            $org_code = $ticket->invoice->organisation->short_code;
            if($invoice_month<4)
                $invoice_year--;
            $nextyear = $invoice_year+1;
            $invoice_number = $org_code.'/'.$invoice_no.'/'.$invoice_year.'-'.$nextyear;

            $facility_id = $ticket->facility_id;
            $facility_name = $ticket->facility->facility_name;

            $ticket_no = $org_code.'/'.$ticket->facility->short_code.'/'.$ticket->ticket_no.'/'.$invoice_year.'-'.$nextyear;

            $noofadult = $ticket->noof_adult;
            $noofchild = $ticket->noof_child;
            $amount = $ticket->amount;

            $counter_user = $ticket->invoice->publicuser->name;

            $counter_user.= "[".$ticket->invoice->publicuser->phone."]";

            if($invoice_number)
            {
                $invoice_array[$ticket_id]['counter_user'] = $counter_user;
                $invoice_array[$ticket_id]['invoice_number'] = $invoice_number;
                $invoice_array[$ticket_id]['invoice_date'] = $invoice_date_new;
                $invoice_array[$ticket_id]['show_date'] = $show_date;
                $invoice_array[$ticket_id]['ticket_no'] = $ticket_no;
                $invoice_array[$ticket_id]['facility_name'] = $facility_name;
                $invoice_array[$ticket_id]['noofadult'] = $noofadult;
                $invoice_array[$ticket_id]['noofchild'] = $noofchild;
                $invoice_array[$ticket_id]['amount'] = $amount;
            }
        }

        /////cancelled tickets ///////////////////////////////////////////
        $ticket_cancelledlist = Refund::where('refund_status',1)->whereNotNull('public_user_id')->whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('ticket','invoice','publicuser','user')->whereHas('invoice', function($inv) use($organisation_id) { $inv->where('organisation_id',$organisation_id); })->get();
        foreach($ticket_cancelledlist as $ticket_cancelled)
        {
            $ticket_id = $ticket_cancelled->ticket_id;
            $ticket_arr[] = $ticket_id;
            $invoice_id = $ticket_cancelled->invoice_id;
            $invoice_date = $ticket_cancelled->invoice->invoice_date;
            $invoice_date_new = date("d-M-Y", strtotime($invoice_date));
            $show_date = $ticket_cancelled->invoice->show_date;
            $show_date = date("d-M-Y", strtotime($show_date));
            $invoice_month = date('m',strtotime($invoice_date));
            $invoice_year = date('Y',strtotime($invoice_date));
            $invoice_no = $ticket_cancelled->invoice->invoice_no;
            $org_code = $ticket_cancelled->invoice->organisation->short_code;
            if($invoice_month<4)
                $invoice_year--;
            $nextyear = $invoice_year+1;
            $invoice_number = $org_code.'/'.$invoice_no.'/'.$invoice_year.'-'.$nextyear;

            $facility_id = $ticket_cancelled->ticket->facility_id;
            $facility_name = $ticket_cancelled->ticket->facility->facility_name;

            $ticket_no = $org_code.'/'.$ticket_cancelled->ticket->facility->short_code.'/'.$ticket_cancelled->ticket->ticket_no.'/'.$invoice_year.'-'.$nextyear;

            $noofadult = $ticket_cancelled->ticket->noof_adult;
            $noofchild = $ticket_cancelled->ticket->noof_child;
            $amount = $ticket_cancelled->ticket->amount;
            $refund_amount = $ticket_cancelled->refund_amount;
            $refund_amount = '-'.$refund_amount;
            $counter_user = $ticket_cancelled->publicuser->name;

            $counter_user.= "[".$ticket_cancelled->publicuser->phone."]";

            if($invoice_number)
            {
                $cancelled_array[$ticket_id]['counter_user'] = $counter_user;
                $cancelled_array[$ticket_id]['invoice_number'] = $invoice_number;
                $cancelled_array[$ticket_id]['invoice_date'] = $invoice_date_new;
                $cancelled_array[$ticket_id]['show_date'] = $show_date;
                $cancelled_array[$ticket_id]['ticket_no'] = $ticket_no;
                $cancelled_array[$ticket_id]['facility_name'] = $facility_name;
                $cancelled_array[$ticket_id]['noofadult'] = $noofadult;
                $cancelled_array[$ticket_id]['noofchild'] = $noofchild;
                $cancelled_array[$ticket_id]['amount'] = $refund_amount;
            }
        }
        //////////////////////////////////////////////////////////////////          
        //dd($cancelled_array);
        return view('Reports/online_ticket_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','date_from','date_to','invoice_array','ticket_arr','org_arr','org_name','fac_name'));
    }

    //////////consolidated report////////////////////////////
    public function consolidated_report()
    {
        $page_title = 'Consolidated Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
            $users = User::where('id',$user_id)->where('status',1)->get();
        }
        else
        {
            $organisations = Organisation::all();
            $users = User::where('status',1)->get();
        }
        return view('Reports/consolidated_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id','users'));
    }
    public function consolidated_report_list(Request $request)
    {
        $page_title = 'Consolidated Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_date_arr =[];
        $spl_ticket_date_arr=[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $organisation_id = $request->organisation_id;
        if($organisation_id!="")
        {
            $org_arr = Organisation::where('id',$organisation_id)->first();
            if(isset($org_arr))
            {
                $org_name = $org_arr->organisation_name."(".$org_arr->city.")";
            }
        }
        else
        {
            $org_name = "All Organisations";
        }
        //dd($daterange);
        $n=0;
        
        $facility_list =[];
        $ticket_count =[];
        $facility_name =[];
        $online_ticket_amount =[];
        $cash_ticket_amount= [];
        $card_ticket_amount = [];
        $upi_ticket_amount = [];
        $adult_count = [];
        $child_count = [];
        $totalamount =[];
        
        $facilities = Facility::where('active_flag',1)->get();
        foreach($facilities as $facility)
        {
            $facility_id = $facility->id;
            $online_ticket_amount[$facility_id] = 0;
            $cash_ticket_amount[$facility_id] = 0;
            $card_ticket_amount[$facility_id] = 0;
            $upi_ticket_amount[$facility_id] = 0;
            $refund_amount[$facility_id] = 0;
            $adult_count[$facility_id] = 0;
            $child_count[$facility_id] = 0;
            $totalamount[$facility_id] = 0;
            $facility_list[$facility_id] = $facility_id;
            $facility_name[$facility_id] = $facility->facility_name;

            $ticket_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->count();

            $online_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',1)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

            $cash_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',1)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

            $card_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

            $upi_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',3)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

            $refund_amount_offline[$facility_id] = Refund::where('refund_status',1)->whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('invoice','ticket')->whereHas('invoice', function($inv) use($organisation_id){ $inv->where('organisation_id', $organisation_id)->where('payment_type',2); })->whereHas('ticket', function($tick) use($facility_id){ $tick->where('facility_id', $facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); }); })->sum('refund_amount');

            $refund_amount_online[$facility_id] = Refund::where('refund_status',1)->whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('invoice','ticket')->whereHas('invoice', function($inv) use($organisation_id){ $inv->where('organisation_id', $organisation_id)->where('payment_type',1); })->whereHas('ticket', function($tick) use($facility_id){ $tick->where('facility_id', $facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); }); })->sum('refund_amount');

            //$refund_amount[$facility_id] = Refund::where('refund_status',1)->whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('invoice','ticket')->whereHas('invoice', function($inv) use($organisation_id){ $inv->where('organisation_id', $organisation_id); })->whereHas('ticket', function($tick) use($facility_id){ $tick->where('facility_id', $facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); }); })->sum('refund_amount');

            $adult_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('noof_adult');

            $child_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('noof_child');

            $totalamount[$facility_id] = ($online_ticket_amount[$facility_id]+$cash_ticket_amount[$facility_id]+$card_ticket_amount[$facility_id]+$upi_ticket_amount[$facility_id])-$refund_amount_offline[$facility_id]-$refund_amount_online[$facility_id];
        }
        return view('Reports/consolidated_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','ticket_date_arr','date_from','date_to','facility_list','facility_name','ticket_count','online_ticket_amount','cash_ticket_amount','card_ticket_amount','upi_ticket_amount','refund_amount_offline','refund_amount_online','adult_count','child_count','totalamount','org_name'));
    }    

    //////////consolidated report////////////////////////////
    public function counter_consolidated_report()
    {
        $page_title = 'Counter Consolidated Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $id = Auth::user()->id; 
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
            $users = User::where('status',1)->where('organisation_id',$organisation_id)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        else
        {
            $organisations = Organisation::all();
            $users = User::where('status',1)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        //dd($users);
        return view('Reports/counter_consolidated_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id','users','id'));
    }
    public function counter_consolidated_report_list(Request $request)
    {
        $page_title = 'Counter Consolidated Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_date_arr =[];
        $spl_ticket_date_arr=[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $organisation_id = $request->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->first();
            if(isset($organisations))
            {
                $org_name = $organisations->organisation_name."(".$organisations->city.")";
            }
        }
        else
        {
            $org_name = "All Organisation";
        }
        $user_id = $request->user_id;
        if($user_id=="All")
        {
            $user_name = "All Counter Users";
        }
        if($user_id!="All")
        {
            $users = User::where('id',$user_id)->first();
            if(isset($users))
            {
                $user_name = $users->name;
            }
        }
        //dd($user_id);
        $n=0;
        
        $facility_list =[];
        $ticket_count =[];
        $facility_name =[];
        $online_ticket_amount =[];
        $cash_ticket_amount= [];
        $card_ticket_amount = [];
        $upi_ticket_amount = [];
        $adult_count = [];
        $child_count = [];
        $totalamount =[];
        $invoice_from_db = "";
        $invoice_to_db = "";
        $invoice_from = "";
        $invoice_to = "";
        $invoices = "";
        $invoice_date ="";
        $invoice_year = "";
        $org_code = "";
        $invoice_month ="";
        $nextyear = "";
        $invoice_number_from ="";
        $invoice_number_to = "";

        //echo "date_from=$date_from....date_to=$date_to....<br>";
        if($user_id=="All")
        {
            $invoice_from_db = Invoice::whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->groupBy('invoice_no')->select('invoice_no')->orderBy('invoice_no','asc')->first();
            if(isset($invoice_from_db))
            {
                $invoice_from = $invoice_from_db->invoice_no;
            }

            $invoice_to_db = Invoice::whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->groupBy('invoice_no')->select('invoice_no')->orderBy('invoice_no','desc')->first();
            if(isset($invoice_to_db))
            {
                $invoice_to = $invoice_to_db->invoice_no;
            }

            $invoices = Invoice::whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to))->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->with('organisation')->get();
            foreach($invoices as $invoice)
            {
                $invoice_date = $invoice->invoice_date;
                $invoice_month = date('M',strtotime($invoice_date));
                $invoice_year = date('Y',strtotime($invoice_date));
                $org_code = $invoice->organisation->short_code;
                if($invoice_month<4)
                    $invoice_year--;
                $nextyear = $invoice_year+1;
                
            }
            $invoice_number_from = $org_code.'/'.$invoice_from.'/'.$invoice_year.'-'.$nextyear;
            $invoice_number_to = $org_code.'/'.$invoice_to.'/'.$invoice_year.'-'.$nextyear;

            $facilities = Facility::where('active_flag',1)->get();
            foreach($facilities as $facility)
            {
                $facility_id = $facility->id;
                $online_ticket_amount[$facility_id] = 0;
                $cash_ticket_amount[$facility_id] = 0;
                $card_ticket_amount[$facility_id] = 0;
                $upi_ticket_amount[$facility_id] = 0;
                $refund_amount[$facility_id] = 0;
                $adult_count[$facility_id] = 0;
                $child_count[$facility_id] = 0;
                $totalamount[$facility_id] = 0;
                $facility_list[$facility_id] = $facility_id;
                $facility_name[$facility_id] = $facility->facility_name;

                
                $ticket_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->count();

                $cash_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',1)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

                $card_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

                $upi_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',3)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

                $refund_amount[$facility_id] = Refund::where('refund_status',1)->whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('invoice','ticket')->whereHas('invoice', function($inv) use($organisation_id){ $inv->where('payment_type',2)->where('organisation_id', $organisation_id); })->whereHas('ticket', function($tick) use($facility_id){ $tick->where('facility_id', $facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); }); })->sum('refund_amount');

                $adult_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('noof_adult');

                $child_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id){ $inv->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('noof_child');

                $totalamount[$facility_id] = ($cash_ticket_amount[$facility_id]+$card_ticket_amount[$facility_id]+$upi_ticket_amount[$facility_id])-$refund_amount[$facility_id];
            }
        }
        if($user_id!="All")
        {
            $facilities = Facility::where('active_flag',1)->get();
            foreach($facilities as $facility)
            {
                $facility_id = $facility->id;
                $online_ticket_amount[$facility_id] = 0;
                $cash_ticket_amount[$facility_id] = 0;
                $card_ticket_amount[$facility_id] = 0;
                $upi_ticket_amount[$facility_id] = 0;
                $refund_amount[$facility_id] = 0;
                $adult_count[$facility_id] = 0;
                $child_count[$facility_id] = 0;
                $totalamount[$facility_id] = 0;
                $facility_list[$facility_id] = $facility_id;
                $facility_name[$facility_id] = $facility->facility_name;

                $ticket_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id,$user_id){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->count();

                $cash_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id,$user_id){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',1)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

                $card_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id,$user_id){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

                $upi_ticket_amount[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id,$user_id){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->where('payment_method',3)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('amount');

                $refund_amount[$facility_id] = Refund::where('refund_status',1)->where('user_id',$user_id)->whereBetween(DB::raw('DATE(created_at)'), array($date_from, $date_to))->with('invoice','ticket')->whereHas('invoice', function($inv) use($organisation_id){ $inv->where('payment_type',2)->where('organisation_id', $organisation_id); })->whereHas('ticket', function($tick) use($facility_id){ $tick->where('facility_id', $facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); }); })->sum('refund_amount');

                $adult_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id,$user_id){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('noof_adult');

                $child_count[$facility_id] = Ticket::where('facility_id',$facility_id)->where(function($st) { $st->where('status',1)->orwhere('status',2); })->with('invoice')->whereHas('invoice', function($inv) use($date_from,$date_to,$organisation_id,$user_id){ $inv->where('user_id',$user_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('organisation_id',$organisation_id)->where('payment_type',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to)); })->sum('noof_child');

                $totalamount[$facility_id] = ($cash_ticket_amount[$facility_id]+$card_ticket_amount[$facility_id]+$upi_ticket_amount[$facility_id])-$refund_amount[$facility_id];
            }
        }
        
        //dd($facility_list);
        return view('Reports/counter_consolidated_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','ticket_date_arr','date_from','date_to','facility_list','facility_name','ticket_count','online_ticket_amount','cash_ticket_amount','card_ticket_amount','upi_ticket_amount','refund_amount','adult_count','child_count','totalamount','user_name','org_name','invoice_number_from','invoice_number_to'));
    }

    //////////showwise report////////////////////////////
    public function show_wise_report()
    {
        $page_title = 'Show Wise Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;

        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $facilities = Facility::where('organisation_id',$organisation_id)->where('active_flag',1)->with('organisation')->orderBy('order_by','asc')->get();  
            $organisations = Organisation::where('id',$organisation_id)->get();
        }
        else
        {
            $facilities = Facility::where('active_flag',1)->with('organisation')->orderBy('order_by','asc')->get();  
            $organisations = Organisation::all();
        }
        return view('Reports/show_wise_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id','facilities'));
    }
    public function show_wise_report_list(Request $request)
    {
        $page_title = 'Showwise Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $ticket_date_arr =[];
        $spl_ticket_date_arr=[];
        $facility_id = $request->facility_id;
        $organisation_id = $request->organisation_id;
        //dd($daterange);
        $n=0;
        $regular_ticket_count= [];
        $regular_child_count=[];
        $special_adult_count = [];
        $special_child_count = [];
        $special_ticket_count =[];
        $regular_adult_count = [];
        $fixedseat_flag ="";
        $facilities = Facility::where('id',$facility_id)->where('active_flag',1)->first();
        if(isset(($facilities)))
        {
            $fixedseat_flag = $facilities->fixedseatflag;
        }
        if($fixedseat_flag==1)
        {
            //get layout
            $layouts[$facility_id] = Layout::where('facility_id', $facility_id)->first();
            if(isset($layouts[$facility_id]))
            {
                $rows = $layouts[$facility_id]->total_row;
                $layout_id = $layouts[$facility_id]->id;
                //get seats
                for($i=1;$i<=$rows;$i++)
                {
                    $seats[$facility_id][$i] = Seats::where('facility_id',$facility_id)->where('layout_id',$layout_id)->where('row_no',$i)->get();
                    $seatcount[$facility_id][$i] = Seats::where('facility_id',$facility_id)->where('layout_id',$layout_id)->where('row_no',$i)->count();
                }                
            }
        }
        $regular_showtimings = ShowTimings::where('facility_id',$facility_id)->where('show_flag',0)->where('active_flag',1)->with('facility')->get();
        foreach($regular_showtimings as $regular_showtiming)
        {
            $show_time_id = $regular_showtiming->id;
            $regular_adult_count[$show_time_id] = Ticket::where('facility_id',$facility_id)->where('show_time_id',$show_time_id)->where('status',1)->whereDate('show_date',$date_from)->with('facility','invoice')->whereHas('invoice', function($inv) { $inv->where('status',1); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_adult');
            $regular_child_count[$show_time_id] = Ticket::where('facility_id',$facility_id)->where('show_time_id',$show_time_id)->where('status',1)->whereDate('show_date',$date_from)->with('facility','invoice')->whereHas('invoice', function($inv) { $inv->where('status',1); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_child');
            $regular_ticket_count[$show_time_id] = Ticket::where('facility_id',$facility_id)->where('show_time_id',$show_time_id)->where('status',1)->whereDate('show_date',$date_from)->with('facility','invoice')->whereHas('invoice', function($inv) { $inv->where('status',1); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->count();
        }
        $special_showtimings = ShowTimings::where('facility_id',$facility_id)->where('show_flag',1)->where('active_flag',1)->with('facility')->get();
        foreach($special_showtimings as $special_showtiming)
        {
            $show_time_id = $special_showtiming->id;
            $special_adult_count[$show_time_id] = Ticket::where('facility_id',$facility_id)->where('show_time_id',$show_time_id)->where('status',1)->whereDate('show_date',$date_from)->with('facility','invoice')->whereHas('invoice', function($inv) { $inv->where('status',1); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_adult');
            $special_child_count[$show_time_id] = Ticket::where('facility_id',$facility_id)->where('show_time_id',$show_time_id)->where('status',1)->whereDate('show_date',$date_from)->with('facility','invoice')->whereHas('invoice', function($inv) { $inv->where('status',1); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->sum('noof_child');
            $special_ticket_count[$show_time_id] = Ticket::where('facility_id',$facility_id)->where('show_time_id',$show_time_id)->where('status',1)->whereDate('show_date',$date_from)->with('facility','invoice')->whereHas('invoice', function($inv) { $inv->where('status',1); })->whereHas('facility',function($fac) use($organisation_id) { $fac->where('organisation_id',$organisation_id); })->count();
        }

        
        //dd($ticket_array);
        return view('Reports/show_wise_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','regular_adult_count','date_from','regular_child_count','facilities','regular_ticket_count','special_adult_count','special_child_count','special_ticket_count','organisation_id','facility_id','regular_showtimings','special_showtimings','seats','seatcount'));
    }

    //////////card transaction report////////////////////////////
    public function card_transaction_report()
    {
        $page_title = 'Card Transaction Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
    
        $id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
            $users = User::where('status',1)->where('organisation_id',$organisation_id)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        else
        {
            $organisations = Organisation::all();
            $users = User::where('status',1)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        return view('Reports/card_transaction_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id','users','id'));
    }
    public function card_transaction_report_list(Request $request)
    {
        $page_title = 'Card Transaction Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_date_arr =[];
        $spl_ticket_date_arr=[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $organisation_id = $request->organisation_id;
        if($organisation_id)
        {
            $organisations = Organisation::where('id',$organisation_id)->first();
            if(isset($organisations))
            {
                $org_name = $organisations->organisation_name." ".$organisations->city;
            }
        }
        $user_id = $request->user_id;
        if($user_id=="All")
        {
            $user_name = "All";
        }
        if($user_id!="All")
        {
            $users = User::where('id',$user_id)->first();
            if(isset($users))
            {
                $user_name = $users->name;
            }
        }        
        $invoices = Invoice::where('organisation_id',$organisation_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('payment_type',2)->where('payment_method',2)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to));
        if($user_id!="All")
        {
            $invoices = $invoices->where('user_id',$user_id);
        }
        $invoices = $invoices->get();
        return view('Reports/card_transaction_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','date_from','date_to','invoices','user_name','org_name'));
    }

    //////////UPI transaction report////////////////////////////
    public function upi_transaction_report()
    {
        $page_title = 'UPI Transaction Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
    
        $id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
            $users = User::where('status',1)->where('organisation_id',$organisation_id)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        else
        {
            $organisations = Organisation::all();
            $users = User::where('status',1)->with('userrole')->whereHas('userrole',function($usrole) { $usrole->where('role_id',4); })->get();
        }
        return view('Reports/upi_transaction_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id','users','id'));
    }
    public function upi_transaction_report_list(Request $request)
    {
        $page_title = 'UPI Transaction Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $ticket_date_arr =[];
        $spl_ticket_date_arr=[];
        $begin = new DateTime($date_from);
        $end = new DateTime($date_to);
        $end = $end->modify( '+1 day' ); 
        $interval = new DateInterval('P1D');
        $daterange = new DatePeriod($begin, $interval ,$end);
        $organisation_id = $request->organisation_id;
        if($organisation_id)
        {
            $organisations = Organisation::where('id',$organisation_id)->first();
            if(isset($organisations))
            {
                $org_name = $organisations->organisation_name." ".$organisations->city;
            }
        }
        $user_id = $request->user_id;
        if($user_id=="All")
        {
            $user_name = "All";
        }
        if($user_id!="All")
        {
            $users = User::where('id',$user_id)->first();
            if(isset($users))
            {
                $user_name = $users->name;
            }
        }
        $invoices = Invoice::where('organisation_id',$organisation_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->where('payment_type',2)->where('payment_method',3)->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to));
        if($user_id!="All")
        {
            $invoices = $invoices->where('user_id',$user_id);
        }
        $invoices = $invoices->get();
        return view('Reports/upi_transaction_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','date_from','date_to','invoices','user_name','org_name'));
    }

    /////////////////////////////////////////////////////////////////////////////////
    //////////counter transaction report////////////////////////////
    public function counter_transaction_report()
    {
        $page_title = 'Counter Invoice Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;

        $user_id = Auth::user()->id;
        $organisation_id = Auth::user()->organisation_id;
        if($organisation_id!="")
        {
            $organisations = Organisation::where('id',$organisation_id)->get();
        }
        else
        {
            $organisations = Organisation::all();
        }
        $payment_methods = PaymentMethod::all();
        return view('Reports/counter_transaction_report', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisations','organisation_id','payment_methods'));
    }
    public function counter_transaction_report_list(Request $request)
    {
        $page_title = 'Counter Invoice Report';
        $page_description = 'Some description for the page';
        $logo = "images/logo.png";
        $logoText = "images/logo-text.png";
        $active="active";
        $event_class="schedule-event";
        $button_class="btn-primary";
        $action = __FUNCTION__;
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $payment_method = $request->payment_method;
        $organisation_id = $request->organisation_id;
        if($organisation_id)
        {
            $organisations = Organisation::where('id',$organisation_id)->first();
            if(isset($organisations))
            {
                $org_name = $organisations->organisation_name." ".$organisations->city;
            }
        }
        $n=0;
        ///////////////////////////////////////////////

            $invoices = Invoice::where('organisation_id',$organisation_id)->where(function($invst) { $invst->where('status',1)->orwhere('status',2); })->whereBetween(DB::raw('DATE(invoice_date)'), array($date_from, $date_to));
            if($payment_method)
            {
                $invoices = $invoices->where('payment_method',$payment_method);
            }
            $invoices = $invoices->with('paymentmethod')->get();


        ///////////////////////////////////////////////        
        //dd($ticket_array);
        return view('Reports/counter_transaction_report_list', compact('page_title','page_description','action','logo','logoText','active','event_class','button_class','organisation_id','payment_method','date_from','date_to','invoices','org_name'));
    }
    /////////////////////////////////////////////////////////////////////////////////////////////////////////////////

}