<?php

namespace App\Http\Controllers\Admin;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\User;
use App\Models\Project;
use App\Models\Task;
use App\Models\Group;
use App\Models\Note;
use Auth;
use App\Models\Attendance;
use App\Models\CompanyModel;
use App\Models\Invoice;
use App\Models\InvoiceItems;
use App\Models\AccountHead;
use Excel;
use DB;
class HomeController extends Controller
{

    public function __construct()
    {
        $this->middleware('auth');
    }
    
    public function index()
    {
        /*
        1=Created;
        2=in process;
        3=Completed;
        4=checking;
        5=bugfixing;
        6=done;
        */
        date_default_timezone_set("Asia/Dhaka");
        
        $user_id = Auth::user()->id;
        $data['title'] = "Admin dashboard";
        //todays completed task
        $data['all'] = User::where('utype',2)->get();
        $data['deadline_missed'] = Task::with(['user','proj_name'])->where('user_id','!=',0)->where('status','!=',6)->whereDate('dead_line', '<', date('Y-m-d'))->orderBy('created_time')->get();
        $data['deadline_today'] = Task::with(['user','proj_name'])->where('user_id','!=',0)->where('status','!=',6)->whereDate('dead_line', '=', date('Y-m-d'))->orderBy('created_time')->get();
        $data['done_today'] = Task::with(['user','proj_name'])->where('user_id','!=',0)->where('status','=',6)->whereDate('closing_time', '=', date('Y-m-d'))->orderBy('created_time')->get();
        $all_users=User::all()->pluck('name','id');
        $working_today = Task::where('user_id','!=',0)->whereDate('start_time', '=', date('Y-m-d'))->orwhereDate('closing_time', '=', date('Y-m-d'))->whereNotIn('status',[2,3,4,5])->orderBy('created_time')->pluck('user_id');
        
        $active_users=array();
        foreach($working_today as $row){
            if($row !=0){
                if (!in_array($row,$active_users))
                {
                    array_push($active_users,$row);
                }
            }
        }
        $inactive_users=array();
        foreach($all_users as $i=>$value){
            if (!in_array($i,$active_users))
            {
                array_push($inactive_users,$value);
            }
        }
        $data['inactive_users']=$inactive_users;
        $data['pending_task'] = Task::with(['user','proj_name'])->where('user_id','!=',0)->where('status','!=',6)->orderBy('created_time')->get();
        $data['complted_yesterday'] = Task::with(['user','proj_name'])->where('user_id','!=',0)->where('status',6)->whereDate('closing_time', '=', date('Y-m-d'))->orderBy('created_time')->get();
        $data['in_process_task'] = Task::with(['user','proj_name'])->where('user_id','!=',0)->whereIn('status',[2,3,4,5])->orderBy('created_time')->get();
        $data['all1'] = User::where('utype',3)->get();
        $data['notes'] = Note::where('user_id',$user_id)->orderBy('time')->get();
        
        /*
                Reports
        */
        $data['clients']=CompanyModel::count();
        $data['ttl_invoice']=
        
        Invoice::where(\DB::raw("MONTH(invoice_date)"),'=',\DB::raw('MONTH(NOW())'))->count();
        $data['ttl_due_invoice'] = Invoice::where(\DB::raw("MONTH(due_date)"),'<=',\DB::raw('MONTH(NOW())'))
        ->where('due_date','<',\DB::raw('NOW()'))
        ->where('status',0)->count();
      

       $sql_exp="SELECT MONTH(j.created_at) as month_name,SUM(j.amount) as total_amount FROM journal j  JOIN acc_head ahd  ON j.ref_id = ahd.id WHERE ahd.type_id = 2 GROUP BY YEAR(j.created_at),MONTH(j.created_at)";

       $expense_report_data=\DB::select($sql_exp);

       $exp_month=[];
       $exp_amount=[];
       if($expense_report_data)
       {
         foreach($expense_report_data as $row)
         {
            array_push($exp_month,$this->get_month_name($row->month_name));
            array_push($exp_amount,$row->total_amount);
         }
       }
        $data['exp_month']='['.implode(',',$exp_month).']';

        $data['exp_amount']='['.implode(',',$exp_amount).']';

        $sql_revenue="SELECT MONTH(j.created_at) as month_name,SUM(j.amount) as total_amount FROM journal j  JOIN acc_head ahd  ON j.ref_id = ahd.id WHERE ahd.type_id = 4 GROUP BY YEAR(j.created_at),MONTH(j.created_at)";

        $revenue_report_data=\DB::select($sql_revenue);
        $revenue_amount=[];
        if($expense_report_data)
        {
          foreach($revenue_report_data as $row)
          {
             array_push($revenue_amount,$row->total_amount);
          }
        }
        $data['revenue_amount']='['.implode(',',$revenue_amount).']';

        $all_due=Invoice::where('status','=',0)->pluck('id');
        $due=0;
        if(count($all_due)>0)
        {
            foreach($all_due as $due_id){
            
                $amounts=InvoiceItems::where('invoice_id','=',$due_id)->pluck('payable_amount');
                foreach($amounts as $amount)
                {

                    $due+=$amount;
                }
            }
        }
        
        $data['dues']   =   $due;

        
        /*
            Monthly Due
         */
        $all_month_due=Invoice::whereMonth('due_date','=',date('m'))->where('status','=',0)->pluck('id');

        $monthly_due=0;
        if(count($all_month_due)>0)
        {
            foreach($all_month_due as $due_id){
                
                $monthly_due_amounts=InvoiceItems::where('invoice_id','=',$due_id)->pluck('payable_amount');
                foreach($monthly_due_amounts as $monthly_due_amount)
                {
                    
                    $monthly_due+=$monthly_due_amount;
                }
            }
        }
        $data['all_month_due']   =   $monthly_due;

        /*
            Monthly Collection
         */
        $all_month_collected=Invoice::whereMonth('due_date','=',date('m'))->where('status','=',1)->pluck('id');
        $monthly_collected=0;
        if(count($all_month_collected)>0)
        {
            foreach($all_month_collected as $due_id){
            
                $monthly_collected_amounts=InvoiceItems::where('invoice_id','=',$due_id)->pluck('payable_amount');
                foreach($monthly_collected_amounts as $monthly_collected_amount)
                {
                    
                    $monthly_collected+=$monthly_collected_amount;
                }
            }
        }
        
        $data['monthly_collected']   =   $monthly_collected;

        /*$all_counselors  =   Invoice::with(['user'])->get();
        $users=array();
        $user_info_array=array();
        foreach($all_counselors as $u){

            if(!in_array($u->created_by, $users))
            {
                array_push($users,$u->created_by);
                $new_info=array('user_name'=>$u->user['name'],'user_id'=>$u->created_by);
                array_push($user_info_array, $new_info);
            }
        }
      // dd($user_info_array);
       
        
        //$counselor_wise_report=array();
        foreach($user_info_array as $counselor)
        {
            $total_amount=0;
            $collected_amount=0;
            $due_amount=0;
            $counselor_name                     =   $counselor['user_name'];
            $counselor_invoice_ids              =   Invoice::whereMonth('created_at','=',date('m'))->where('created_by','=',$counselor['user_id'])->pluck('id');
            $counselor_collected_invoice_ids    =   Invoice::whereMonth('created_at','=',date('m'))->where('created_by','=',$counselor['user_id'])->where('status','=',1)->pluck('id');

            $counselor_due_invoice_ids          =   Invoice::whereMonth('created_at','=',date('m'))->where('created_by','=',$counselor['user_id'])->where('status','=',0)->pluck('id');

            foreach($counselor_invoice_ids as $row)
            {
                $all_billings=InvoiceItems::where('invoice_id','=',$row)->pluck('payable_amount');
                foreach ($all_billings as  $value) {
                   $total_amount  +=$value;
                }
            }

            foreach($counselor_collected_invoice_ids as $row1)
            {
                $collected_billings=InvoiceItems::where('invoice_id','=',$row1)->pluck('payable_amount');
                foreach ($collected_billings as  $value1) {
                   $collected_amount  +=$value1;
                }
            }

            foreach($counselor_due_invoice_ids as $row2)
            {
                $due_billings=InvoiceItems::where('invoice_id','=',$row2)->pluck('payable_amount');
                foreach ($due_billings as  $value2) {
                   $due_amount  +=$value2;
                }
            }

            $counselor_wise_report[]=array(
                    'name'=>$counselor_name,
                    'total'=>$total_amount,
                    'collected'=>$collected_amount,
                    'due'=>$due_amount
                );

        }
        $data['counselor_wise_report'] =   $counselor_wise_report;
        */
        
    /*
        Balance Information Goes Here
    */
    $banks=AccountHead::where('parent_head','=',4)->get();
    if(count($banks)>0)
    {
        foreach($banks as $bank)
        {
            $bank_name      =   $bank->name;
            $debit_accounts     =   Journal::where('debit_account','=',$bank->id)->get();   
            $credit_accounts    =   Journal::where('credit_account','=',$bank->id)->get();
            $balance        =   0;
            foreach($debit_accounts as $debit)
            {
                $balance    =   (float)($bank->opening_balance + $debit->amount);
            }
            foreach($credit_accounts as $credit)
            {
                $balance    =   (float)($bank->opening_balance - $credit->amount);
            }
            $total_balances[]   =   array('bank_name'=>$bank_name,'balance'=>$balance);
        }
        $data['banks']          =   $total_balances;
    }
    else
    {
        $data['banks']          =   '';
    }
        return view('admin.admin_dashboard',$data);
    }


    /**
     * get month name by month number
     * 
     * @param  integer $month_number
     * 
     * @return string              
     */
    function get_month_name($month_number)
    {
        $month_name='';
        switch ($month_number) {
            case '1':
                $month_name='January';
                break;

            case '2':
                $month_name='February';
                break;


            case '3':
                $month_name='March';
                break;

            case '4':
                $month_name='April';
                break;

            case '5':
                $month_name='May';
                break;

            case '6':
                $month_name='June';
                break;

            case '7':
                $month_name='July';
                break;

            case '8':
                $month_name='August';
                break;

            case '9':
                $month_name='September';
                break;

            case '10':
                $month_name='October';
                break;

            case '11':
                $month_name='November';
                break;
            case '12':
                $month_name='December';
                break;
            
            default:
                # code...
                break;
        }

        return $month_name;
    }
    public function check_user()
    {
        $utype = Auth::user()->utype;

        if($utype == 1)
        {
            return redirect('admin/dashboard');
        }
        elseif($utype == 2)
        {
            return redirect('user/user_projects');
        }
        elseif($utype == 3)
        {
            return redirect('client/client_dashboard');
        }
        else{
            return redirect('/logout');
        }
    }
    public function user_profile($user_id)
    {
        $user  =   User::find($user_id);
        return view('admin.view_resume',compact('user'));
    }

    public function user_attendace()
    {
        date_default_timezone_set("Asia/Dhaka");
        $uid=Auth::user()->id;
        $utype = Auth::user()->utype;
        if($utype == 1 || $uid==44)
        {
            $all  =   User::all();

            $entry_time =   Attendance::whereDate('attendance_time', '=', date('Y-m-d'))->where('status','=',0)->orderBy('attendance_time','desc')->groupBy('attendance_time')->get();
            $exit_time =   Attendance::whereDate('attendance_time', '=', date('Y-m-d',strtotime('yesterday')))->where('status','=',1)->orderBy('attendance_time','desc')->groupBy('attendance_time')->get();
            $title  =   "User Attendace";
            $all_dates  =   array();
            $dates      =   Attendance::pluck('attendance_time');
            $all_users  =   array();
            $users      =   Attendance::pluck('user_id');
            
            $a=strtotime('9:30 am');
            $c=date('H:i:s a', $a);
            $exact_time= $c;
            foreach($users as $user)
            {
                $new_val    =   $user;
                if(!in_array($new_val,$all_users))
                {
                    array_push($all_users, $new_val);
                }
            }
            $late_array = array();
            
            foreach($all_users as $d)
            {
            if($d !=6 && $d !=10){
                    $late_count=0;
                    
                    $test_entry_time=Attendance::select('attendance_time','user_name')->where('user_id','=',$d)->where('status','=',0)->whereMonth('attendance_time', '>=', date('m'))->get();
                    
                    $absent_count=Attendance::where('user_id','=',$d)->whereMonth('attendance_time', '=', date('m'))->where('absent_count','=',1)->count();
                    if (!empty($test_entry_time) && !empty($absent_count)) {
                        foreach ($test_entry_time as $value1) {
                            $u_name=$value1->user_name;
                            $et=date('H:i:s a',strtotime($value1->attendance_time));
                            if($et>$exact_time)
                            {
                                 $late_count++;
                            }
                        }
                        $new_array=array('user_name'=>$u_name,'late_count'=>$late_count,'absent_count'=>$absent_count);
                        array_push($late_array, $new_array);
                    }
             }  
            }
                 return view('admin.attendace.list',compact('all','title','entry_time','exit_time','all_dates','late_array'));
            }
    }
    public function attendace_sheet_entry(Request $request)
    {
        date_default_timezone_set("Asia/Dhaka");
        if($request->file('import_file') !=''){
            $path = $request->file('import_file')->getRealPath();
            $data = Excel::load($path, function($reader) {})->get();
            if(!empty($data) && $data->count()){

                foreach ($data->toArray() as $key => $value) {
                    if( (int)$value['user_id'] == 1)
                    {
                        $user_name  =   'Reza';
                    } 
                    else if((int)$value['user_id'] == 2)
                    {
                        $user_name  =   'Mamun';
                    }
                    else if((int)$value['user_id'] == 3)
                    {
                        $user_name  =   'Golam Sayed';
                    }
                    else if((int)$value['user_id'] == 4)
                    {
                        $user_name  =   'Akib';
                    }
                    else if((int)$value['user_id'] == 5)
                    {
                        $user_name  =   'Masum';
                    }
                    else if((int)$value['user_id'] == 6)
                    {
                        $user_name  =   'Helal';
                    }
                    else if((int)$value['user_id'] == 7)
                    {
                        $user_name  =   'Zeba';
                    }
                    else if((int)$value['user_id'] == 8)
                    {
                        $user_name  =   'Shabuj';
                    }
                    else if((int)$value['user_id'] == 9)
                    {
                        $user_name  =   'Hasib';
                    }
                    else if((int)$value['user_id'] == 10)
                    {
                        $user_name  =   'Tipu';
                    }
                    else if((int)$value['user_id'] == 11)
                    {
                        $user_name  =   'Niloy';
                    }
                    else if((int)$value['user_id'] == 12)
                    {
                        $user_name  =   'Nishat';
                    }
                    else if((int)$value['user_id'] == 13)
                    {
                        $user_name  =   'Jamil';
                    }
                    else if((int)$value['user_id'] == 14)
                    {
                        $user_name  =   'Shamim';
                    }
                    else if((int)$value['user_id'] == 15)
                    {
                        $user_name  =   'Payer';
                    } 
                    if($value['attendance_time'] !='')
                    {
                        $att_time   =   date('Y-m-d H:i:s',strtotime($value['attendance_time']));
                    } else 
                    {
                        $att_time = "0000-00-00 00:00:00";
                    }
                    $absent_count=(int)$value['absent_count'];
                    if($user_name  !=   'Hasib'){
              $insert[] = ['user_id'=>(int)$value['user_id'],'user_name' => $user_name, 'attendance_time' =>$att_time,'status'=>(int)$value['status'],'absent_count'=>$absent_count];
                    }
                    
                }
                
                if(!empty($insert)){
                    Attendance::insert($insert);
                    return redirect()->action('Admin\HomeController@user_attendace')->with('status', 'Data Added!');
                }

            }

        }

        return back()->with('error','Please Check your file, Something is wrong there.');
    }

    public function attendace_report()
    {
        $utype = Auth::user()->utype;
        if($utype == 1)
        {
            $title          =   "User Attendace Report";
            $all_attendance =    Attendance::all();
            return view('admin.attendace.report',compact('all_attendance','title'));
        }
    }

    public function get_report_by_user(Request $request)
    {
        $from   =   $request->start_date;
        $to     =   $request->end_date;
        $user   =   $request->user;
        if( $user == 1)
        {
            $user_name  =   'Reza';
        } 
        else if($user == 2)
        {
            $user_name  =   'Mamun';
        }
        else if($user == 3)
        {
            $user_name  =   'Golam Sayed';
        }
        else if($user == 4)
        {
            $user_name  =   'Akib';
        }
        else if($user == 5)
        {
            $user_name  =   'Masum';
        }
        else if($user == 6)
        {
            $user_name  =   'Helal';
        }
        else if($user == 7)
        {
            $user_name  =   'Zeba';
        }
        else if($user == 8)
        {
            $user_name  =   'Shabuj';
        }
        else if($user == 9)
        {
            $user_name  =   'Hasib';
        }
        else if($user == 10)
        {
            $user_name  =   'Tipu';
        }
        else if($user == 11)
        {
            $user_name  =   'Niloy';
        }
        else if($user == 12)
        {
            $user_name  =   'Nishat';
        }
        else if($user == 13)
        {
            $user_name  =   'Jamil';
        }
        else if($user == 14)
        {
            $user_name  =   'Shamim';
        }
        else if((int)$value['user_id'] == 15)
        {
            $user_name  =   'Payer';
        }
        $entry_time =   Attendance::where('user_id','=',$user)->whereBetween('attendance_time', array($from, $to))->where('status','=',0)->orderBy('attendance_time','desc')->get();
        $exit_time =   Attendance::where('user_id','=',$user)->whereBetween('attendance_time', array($from, $to))->where('status','=',1)->orderBy('attendance_time','desc')->groupBy('attendance_time')->get();
        return view('admin.attendace.report_content',compact('entry_time','exit_time',''))->render();
    }
    public function leave_report()
    {
        $title  =   "Leave Requests";
        $users  =   User::all();
        foreach($users as $user)
        {
            $all_users[]  = array('id'=>$user->id,'name'=>$user->name);
        }
        $leave_type     =   DB::table('leave_type_head')->where('is_published','=',1)->get();
        foreach($leave_type as $leave)
        {
            $purposes[]  = array('id'=>$leave->id,'name'=>$leave->name);
        }
        $all =  DB::table('leave_req')->orderBy('id','DESC')->get();
        return view('admin.leave_request',compact('all','all_users','title','purposes'));
    }
    public function apply(){
        $title          =   "Apply For Leave";
        $leave_type     =   DB::table('leave_type_head')->where('is_published','=',1)->get();
        $users          =   User::all();
        return view('admin.leave_apply',compact('title','leave_type','users'));
    }
    public function store(Request $request){
        $purpose= $request->purpose;
        $apply_to= 2;
        $from= $request->get('from',date('Y-m-d H:i:s'));
        $to= $request->get('to',date('Y-m-d H:i:s'));
        $from_day = strtotime($from);
        $to_day = strtotime($to);
        $days_between = (ceil(abs( $to_day - $from_day) / 86400))+1;
        $description    =   $request->description;
        $approval    =   0;
        $created_at  =  date('Y-m-d H:i:s');
        $user_id    =   Auth::id();
        DB::table('leave_req')->insert(array(
                    'purpose'=>$purpose,
                    'apply_to'=>$apply_to,
                    'from'=>$from,
                    'to'=>$to,
                    'days'=>$days_between,
                    'description'=>$description,
                    'approval'=>$approval,
                    'created_at'=>$created_at,
                    'user_id'=>$user_id,
        ));
        return redirect('admin/leave_requests')->with('msg','Leave request apply Successful.');
    }

    public function leave_details($id)
    {
        $title  =   "Leave Details";
        $leave_req   =    DB::table('leave_req')->where('id',$id)->first();
        
        $all_leave_req =  DB::table('leave_req')->where('user_id',$leave_req->user_id)->orderBy('id','DESC')->get();

        $users  =   User::all();
        foreach($users as $user)
        {
            $all_users[]  = array('id'=>$user->id,'name'=>$user->name);
        }

        $leave_type  = DB::table('leave_type_head')->where('is_published','=',1)->get();
        foreach($leave_type as $leave)
        {
            $taken = DB::table('leave_req')->where('user_id',$leave_req->user_id)->where('purpose',$leave->id)->where('approval',1)->whereYear('created_at', '=', date('Y'))->sum('days');

            $purposes[]  = array('id'=>$leave->id,'name'=>$leave->name,'approved_days'=>$leave->approved_days,'taken'=> $taken );
        }

        return view('admin.leave_details',compact('leave_req','all_leave_req','all_users','title','purposes','leave_type'));
    }

    public function leave_accept(Request $request,$id)
    {
        
        $from= $request->get('from',date('Y-m-d H:i:s'));
        $to= $request->get('to',date('Y-m-d H:i:s'));
        $from_day = strtotime($from);
        $to_day = strtotime($to);
        $days_between = (ceil(abs( $to_day - $from_day) / 86400))+1;
        $approval    =   1;
        DB::table('leave_req')->where('id','=',$id)->update(
            array('approval'=>$approval,'from'=>$from,'to'=>$to,'days'=>$days_between,)
        );

        return redirect('admin/leave_requests')->with('msg','Leave Request Approved.');
    }
    public function leave_discard($id)
    {
        $approval    =   0;
        DB::table('leave_req')->where('id','=',$id)->update(
            array('approval'=>$approval)
        );

        return redirect('admin/leave_requests')->with('msg','Leave Request Discard.');
    }
    public function destroy($id)
    {
        DB::table('leave_req')->where('id','=',$id)->delete();

        return redirect('admin/leave_requests')->with('msg','Leave Request Deleted.');
    }


    public function change_support_status(Request $request){
        $ticket_ids=$request->ticket_ids;
        if(count($ticket_ids)>0){
            foreach($ticket_ids as $ticket_id){
                $stat=1;
                DB::table('ticket')->where('id','=',$ticket_id)->update(array('display_status'=>$stat));
            }
        }
        
    }

}