<?php
namespace App\Http\Controllers\Hr;
use App\Http\Controllers\Controller;
use App\Http\Requests\MinuteSheetRequest;
use Illuminate\Http\Request;
use App\MinuteSheet;
use App\PayrollDetail;
use App\Deduction;
use App\Account;
use App\Payroll;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 use PhpOffice\PhpSpreadsheet\Style\Alignment;
 use EF;


class MinuteSheetController extends Controller
{
     public function index()
    {
    	$minute_sheets = new MinuteSheet;
        return view('hr.minute_sheet.manage_minute_sheet',
        [

        		'minute_sheet_list'       => $minute_sheets->getMinuteSheetList(),
        		
        	]   
        );
    }

    public function storeMinuteSheet(MinuteSheetRequest $request)
    {

      $minute_sheet = new MinuteSheet;

      $form_collect = $request->input();
    

      $curr_month = date('m',strtotime($form_collect['date']));

      $curr_year  = date('Y',strtotime($form_collect['date']));


      $previous_minute_sheet = $minute_sheet->getPreviousSheet($curr_month,$curr_year);

      if($previous_minute_sheet > 0)
      {
           parent::errorMessage('Minute Sheet already found.');

           return redirect('minutesheet');
      }

        $curr_minute_sheet = $minute_sheet->storeMinuteSheet($form_collect);

        if(isset($curr_minute_sheet->id))
        {
            parent::setLogMessage('Minute Sheet "'.$curr_minute_sheet->title.' has been created');

            parent::successMessage('Minute Sheet has been created.',parent::getLogMessage());
        }

        return redirect('minutesheet');
    }

     public function updateMinuteSheet(MinuteSheetRequest $request)
    {
        try 
        {
        	$form_collect = $request->input();
            $form_collect['minute_sheet'] = decrypt($form_collect['minute_sheet']);

            $minute_sheet = new MinuteSheet;


            $curr_minute_sheet = $minute_sheet->updateMinuteSheet($form_collect);

            if(isset($curr_minute_sheet->id))
            {
                 parent::setLogMessage('Minute Sheet "'.$curr_minute_sheet->title.' has been updated');

                 parent::successMessage('Minute Sheet has been updated.',parent::getLogMessage());
            }
        } 
        catch (DecryptException $e) 
        {
            
        }

        return redirect('minutesheet');
    }

     public function refreshMinuteSheet($minute_id)
    {
        try 
        {
           
            $minute_id = decrypt($minute_id);

            $minute_sheet = new MinuteSheet;


            $curr_minute_sheet = $minute_sheet->refreshMinuteSheet($minute_id);

            if($curr_minute_sheet == 1)
            {
                parent::setLogMessage('Minute Sheet  has been refreshed successfully');

                 parent::successMessage('Minute Sheet has been refreshed.',parent::getLogMessage());
            }

         
        } 
        catch (DecryptException $e) 
        {
            
        }

        return redirect('minutesheet');
    }

    public function viewMinuteSheetDetail($minute_sheet_id)
    {
        try 
        {

            $minute_sheet_id   = decrypt($minute_sheet_id);
            $minute_sheet      = new MinuteSheet;
            $payroll_detail    = new PayrollDetail;
            $deduction         = new Deduction;
            $account           = new Account;
            $payroll           = new Payroll;

            $curr_minute_sheet = $minute_sheet->getMinuteSheetById($minute_sheet_id);
            
            if(isset($curr_minute_sheet->id))
            {
                $minute_payroll_detail = $payroll_detail->getPayrollDetailByMinuteId($curr_minute_sheet->id,['Cash','Cheque','Online']);


                   $deduction_titles = $deduction->getDistinctDeduction('title',['title','id']);

                   $curr_accounts = $account->getSubheadByType('Bank');


                  $data = [
                    'minute_payroll_detail'       =>  $minute_payroll_detail,
                    'curr_minute_sheet'           =>  $curr_minute_sheet,
                    'payroll'                     => $payroll,
                    'unique_id'                   => uniqid(),
                


                  ];

                  $view_minute_sheet = view('hr.components.minute_sheet.table_minute_sheet_details',$data);

                 return view('hr.minute_sheet.view_minute_details',
                 [

                'unique_id'                   => uniqid(),
                
                'curr_minute_sheet'           =>  $curr_minute_sheet,
                'titles'                      =>  $deduction_titles,
                'curr_accounts'               =>  $curr_accounts,
                'view_minute_sheet'           => $view_minute_sheet,
            
                
                 ]   
               );
            }
    
        } 
        catch (DecryptException $e) 
        {
            
        }

    }

    public function downloadMinuteSheet($minute_sheet_id)
    {
        try 
        {

            $minute_sheet_id   = decrypt($minute_sheet_id);
            $minute_sheet      = new MinuteSheet;
            $payroll_detail    = new PayrollDetail;
            $deduction         = new Deduction;
            $account           = new Account;
            $payroll           = new Payroll;

            $curr_minute_sheet = $minute_sheet->getMinuteSheetById($minute_sheet_id);
            
            if(isset($curr_minute_sheet->id))
            {
                $minute_payroll_detail = $payroll_detail->getPayrollDetailByMinuteId($curr_minute_sheet->id,['Cash','Cheque','Online']);

                   $deduction_titles = $deduction->getDistinctDeduction('title',['title','id']);

                   $curr_accounts = $account->getSubHeadByType('Bank');


                  $data = [
                    'minute_payroll_detail'           =>  $minute_payroll_detail,
                    'curr_minute_sheet'               =>  $curr_minute_sheet,
                    'payroll'                         =>  $payroll,
                    'unique_id'                       =>  uniqid(),
                


                  ];

                  $curr_minute_sheet_date = $curr_minute_sheet->date;

                  $month = strtoupper(date("F",strtotime($curr_minute_sheet_date)));
                  $year = date("Y",strtotime($curr_minute_sheet_date));


                  



         $spreadsheet = new Spreadsheet();
         $sheet = $spreadsheet->getActiveSheet();
       // Set document properties
            $spreadsheet->getProperties()->setCreator('GISPLUS')
             ->setLastModifiedBy('GISPLUS')
             ->setTitle('Salary')
             ->setDescription('Salary Sheet');

           
             $sheet->mergeCells('A1:S1');

             $sheet->getStyle("A1:S1")->getFont()->setBold(true)
                                      ->setName('Verdana')
                                      ->setSize(12)
                                      ->getColor()->setRGB('ff3727');

 $styleArray = [
    
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical'   => Alignment::VERTICAL_CENTER,
    ]];

    $sheet->getStyle('A2:S2')->applyFromArray($styleArray);


$base_path = public_path();


$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Skylite Logo');
$drawing->setDescription('Skylite Logo');
$drawing->setPath($base_path.'/graphics/logo/logo.jpg'); // put your path and image here
$drawing->setCoordinates('I1');
$drawing->setOffsetX(20);
$drawing->setHeight(75);
$drawing->setWidth(140);

$drawing->setWorksheet($spreadsheet->getActiveSheet());










      
             $sheet->getStyle("A2:S2")->getFont()->setBold(true)
                                      ->setName('Verdana')
                                      ->setSize(10)
                                      ->getColor()->setRGB('000000');
         $sheet->getRowDimension(1)->setRowHeight(80);
         $sheet->getRowDimension(2)->setRowHeight(30);
         $sheet->getStyle('A2:S2')->getAlignment()->setWrapText(true); 


         $sheet->setCellValue('A1', 'REDSUN ASSOCIATES SALARY SHEET FOR THE MONTH OF '.$month.' '.$year)->getColumnDimension('C')->setWidth(5);
                                 


  

             // set the names of header cells
             $sheet->setCellValue('A2', 'S.No.')->getColumnDimension('A')->setWidth(5);
             $sheet->setCellValue('B2', 'Name')->getColumnDimension('B')->setWidth(10);
             $sheet->setCellValue('C2', 'Designation')->getColumnDimension('C')->setWidth(15);
             $sheet->setCellValue('D2', 'Location')->getColumnDimension('D')->setWidth(20); 
             $sheet->setCellValue('E2', 'Total Salary For Breakup')->getColumnDimension('E')->setWidth(12);
             $sheet->setCellValue('F2', 'Basic Salary')->getColumnDimension('F')->setWidth(15);
             $sheet->setCellValue('G2', 'House Rent Allowance')->getColumnDimension('G')->setWidth(12);
             $sheet->setCellValue('H2', 'Utility Allowance')->getColumnDimension('H')->setWidth(15);
             $sheet->setCellValue('I2', 'Medical Allowance')->getColumnDimension('I')->setWidth(15);
             $sheet->setCellValue('J2', 'Over Time')->getColumnDimension('J')->setWidth(15);
             $sheet->setCellValue('K2', 'Fuel Allowance')->getColumnDimension('K')->setWidth(15);
             $sheet->setCellValue('L2', 'Others')->getColumnDimension('L')->setWidth(15);
             $sheet->setCellValue('M2', 'Gross Salary')->getColumnDimension('M')->setWidth(15);
             $sheet->setCellValue('N2', 'Absentee')->getColumnDimension('N')->setWidth(15);
             $sheet->setCellValue('O2', 'Loan')->getColumnDimension('O')->setWidth(15);
             $sheet->setCellValue('P2', 'I.Tax')->getColumnDimension('P')->setWidth(15);
             // $sheet->setCellValue('Q2', 'Laptop Deduction')->getColumnDimension('Q')->setWidth(15);
             // $sheet->setCellValue('R2', 'Pro Rata')->getColumnDimension('R')->setWidth(15);
             $sheet->setCellValue('Q2', 'Total Deductions')->getColumnDimension('Q')->setWidth(15);
             $sheet->setCellValue('R2', 'Net Salary')->getColumnDimension('R')->setWidth(15);
              $sheet->setCellValue('S2', 'Bank A/C')->getColumnDimension('S')->setWidth(20);
           // Add some data
           $x = 3;
         
            
           foreach($minute_payroll_detail as $rows){

            $basic_salary = $rows->getBasicSalary($rows->id,$curr_minute_sheet->id);

            $allowance_sum = $rows->getAllowanceSum($rows->id,$curr_minute_sheet->id);


            $loan_sum = $rows->getLoanSum($rows->id,$curr_minute_sheet->id);

            $deduction_sum = $rows->getDeductionSum($rows->id,$curr_minute_sheet->id);

            $other_deduction = $deduction_sum- $loan_sum;

            $gross_salary = ($basic_salary + $allowance_sum)-($deduction_sum);

            $non_taxable_allowances = $rows->getNonTaxableAllowanceSum($rows->id,$curr_minute_sheet->id);

            $taxable_amount = $gross_salary - $non_taxable_allowances;

            $tax = $rows->calculateTax($taxable_amount)['monthly_tax_amount'];

            $net_salary = $gross_salary-$tax;

            $other_allowances = $allowance_sum-($rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Rent') + $rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Utility') + $rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Over Time') + $rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Medical') + $rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Fuel'));

          

            


          

            $count = 1;
               $sheet->setCellValue('A'.$x, $count);
               $sheet->setCellValue('B'.$x, $rows->name);
               $sheet->setCellValue('C'.$x, $rows->title);
               $sheet->setCellValue('D'.$x, $rows->location);
               $sheet->setCellValue('E'.$x,EF::numberFormat($gross_salary));
               $sheet->setCellValue('F'.$x,EF::numberFormat($basic_salary));
               $sheet->setCellValue('G'.$x,EF::numberFormat($rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Rent')));
               $sheet->setCellValue('H'.$x, EF::numberFormat($rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Utility')));
               $sheet->setCellValue('I'.$x, EF::numberFormat($rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Medical')));
               $sheet->setCellValue('J'.$x, EF::numberFormat($rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Over Time')));
               $sheet->setCellValue('K'.$x, EF::numberFormat($rows->getAllowanceSumByType($rows->id,$curr_minute_sheet->id,'Fuel')));
               $sheet->setCellValue('L'.$x, EF::numberFormat($other_allowances));
               $sheet->setCellValue('M'.$x, EF::numberFormat($gross_salary));
               $sheet->setCellValue('N'.$x, EF::numberFormat($gross_salary));
               $sheet->setCellValue('O'.$x, EF::numberFormat($loan_sum));
               $sheet->setCellValue('P'.$x, EF::numberFormat($tax));
               // $sheet->setCellValue('Q'.$x, '');
               // $sheet->setCellValue('R'.$x,'');
               $sheet->setCellValue('Q'.$x, EF::numberFormat($deduction_sum));
               $sheet->setCellValue('R'.$x, EF::numberFormat($net_salary));
               $sheet->setCellValue('S'.$x, $rows->account_no);
             $x++;
             $count++;
           }


             $new_x = $x+15;

             $new1 = "B".$new_x.":D".$new_x;

             $new2  = "I".$new_x.":K".$new_x;

             $new3  = "O".$new_x.":Q".$new_x;
 
             $sheet->getStyle($new1)->getFont()->setBold(true);

             $sheet->mergeCells($new1);


             $sheet->getStyle($new2)->getFont()->setBold(true);

             $sheet->mergeCells($new2);

             $sheet->getStyle($new3)->getFont()->setBold(true);

             $sheet->mergeCells($new3);

             $sheet->setCellValue('B'.$new_x,'Muhammad Ijaz');
             $sheet->setCellValue('I'.$new_x,'Arsalan Aziz');
             $sheet->setCellValue('O'.$new_x,'Faisal Gul');

              $new_x = $new_x+1;

             $new1 = "B".$new_x.":D".$new_x;

             $new2  = "I".$new_x.":K".$new_x;

             $new3  = "O".$new_x.":Q".$new_x;

             $sheet->getStyle($new1)->getFont()->setBold(true);

             $sheet->mergeCells($new1);

             $sheet->getStyle($new2)->getFont()->setBold(true);

             $sheet->mergeCells($new2);

             $sheet->getStyle($new3)->getFont()->setBold(true);

             $sheet->mergeCells($new3);

             $sheet->setCellValue('B'.$new_x,'Asst. Manager HR');
             $sheet->setCellValue('I'.$new_x,'Asst. Manager Finance');
             $sheet->setCellValue('O'.$new_x,'SR. Manager Finance');









             

            return response()->streamDownload(function () use($spreadsheet){
              $writer =  new Xlsx($spreadsheet);
              $writer->save('php://output');
            },'Salary Sheet.xlsx');

     
            }
    
        } 
        catch (DecryptException $e) 
        {

            parent::errorMessage('No such record found.');

           return redirect()->back();
            
        }

    }
}
