<?php

namespace AppBundle\Controller;

use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
class ExcelController extends ApiBaseController {

    /**
     * Generate Excel  files and return excel file url after saving on server 
     *
     * @param name, json obj
     *
     * @return Export to Excel url
     */
    public function exportToExcelFileAction(Request $request) {
           $styleArray = array(
          'font'  => array(
          'bold'  => true,
          'color' => array('rgb' => 'FF000000'),
          'size'  => 14,
          'name'  => 'Arial'));

           $postData = $this->_getPostData($request);
            if ($postData['status'] == 'fail') {
                return new JsonResponse([ 'status' => false, 'message' => $postData['message'], 'data' => []], 200);
            }
            // validate data
            $gump = $this->get('helpers.datafilter.datafilter');
            $gump->validation_rules(array(
                'start_date' => 'required|date',
                'end_date' => 'required|date',
            ));
            if ($gump->run($postData) === false) {
                $errors = $gump->get_errors_array(true);
                foreach ($errors as $msg) {
                    return new JsonResponse([ 'status' => false, 'message' => $msg, 'data' => []], 200);
                }
            }
            extract($postData);
            $startDate = !empty($postData['start_date']) ? date("m/d/Y", strtotime($postData['start_date'])) : '';
             $endDate = !empty($postData['end_date']) ? date("m/d/Y", strtotime($postData['end_date'])) : '';
             $startDate = $this->_convert_dateTime_Date($startDate);
             $endDate = $this->_convert_dateTime_Date($endDate);
             $getEmployeeIdOfAllTimeSheetEntries = $this->getDoctrine()->getRepository('AppBundle:TimeSheets')->getEmployeeIdByStartEndDate($startDate, $endDate);


             $responce = [];
             $timeSheetWeeklyData = [];
              $weekdays = [];
             $timeSheetApprovalId = [];
             $customerName= [];
             foreach ($getEmployeeIdOfAllTimeSheetEntries as $getEmpIds) {
                    $timeSheetApprovalId[] = $getEmpIds['id'];
                   $timeSheetApproval = $this->getDoctrine()->getRepository('AppBundle:TimeSheetApproval')->findOneBy(['id' => $timeSheetApprovalId]);
                if ($timeSheetApproval) {
                $responce['id'] = $timeSheetApproval->getId();
                $responce['pay_type'] = $timeSheetApproval->getPayType();
                $responce['per_diem'] = $timeSheetApproval->getPerDiem();
                $responce['employee_type'] = $timeSheetApproval->getEmployeeType();
                $responce['is_approved'] = $timeSheetApproval->getIsApproved();
                $responce['employee_id'] = $timeSheetApproval->getEmployeeId();
            } else {
                $responce['id'] = '';
                $responce['pay_type'] = '';
                $responce['per_diem'] = '';
                $responce['employee_type'] = '';
                $responce['is_approved'] = '';
                $responce['employee_id'] = '';
            }
               
          $employee = $this->getDoctrine()->getRepository('AppBundle:Employee')->findOneBy(['id' => $getEmpIds['employee_id']]);


          $responce['employee']['id'] = $employee->getId();

            $employeeName = $employee->getFirstName();


            if ($employee->getMiddleName())
                $employeeName .= ' ' . $employee->getMiddleName();

            if ($employee->getLastName())
                $employeeName .= ' ' . $employee->getLastName();
            $responce['employee']['name'] = $employeeName;
                   
                 $customerName[] =$employeeName;
            $timeSheetData = $this->getDoctrine()->getRepository('AppBundle:TimeSheets')->getHrReportForAllTimeSheetEntriesList($getEmpIds['employee_id']);


           //print_r($timeSheetData);
                foreach ($timeSheetData as $key => $timeSheetList) {

                    print_r($timeSheetList);
                
                    $time_sheet_date = $this->_convert_dateTime_Date($timeSheetList['time_sheet_date']);
                    $res = $this->getWeekStartEndByDate($time_sheet_date);
                    $weekdays[$res['start']][] = $timeSheetList;
                }
                krsort($weekdays);
                foreach ($weekdays as $index1 => $weektimeSheetDataList) {
                    $timeSheetArray = [];
                    foreach ($weektimeSheetDataList as $index2 => $timeSheetList) {
                         $timestamp = strtotime($timeSheetList['time_sheet_date']);

                         $timeSheetList['time_sheet_day'] =  date('D', $timestamp);
                        $timeSheetList['time_sheet_date'] = $this->_convert_dateTime_Date($timeSheetList['time_sheet_date']);
                        $timeSheetList['created_at'] = $this->_convert_Date_To_Epoch_Time($timeSheetList['created_at']);
                        if ($timeSheetList['per_diem_status']) {
                            $timeSheetList['per_diem_status'] = true;
                        } else {
                            $timeSheetList['per_diem_status'] = false;
                        }
                        $time_sheet_date = $timeSheetList['time_sheet_date'];
                        $timeSheetArray[$time_sheet_date][] = $timeSheetList;
                       
                    }

                    $res = $this->getWeekStartEndByDate($index1);
                    $startDate = $res['start'];
                    $endDate = $res['end'];
                    $tempData = [];
                    foreach ($this->createDateRangeArray($startDate, $endDate) as $date) {
                        $tempData[$date]['id'] = null;
                        $tempData[$date]['per_diem_status'] = false;
                        $tempData[$date]['project_id'] = null;
                        $tempData[$date]['project_name'] = null;
                        $tempData[$date]['time_sheet_date'] = $date;
                        $tempData[$date]['created_at'] = null;
                        $tempData[$date]['total_hours'] = 0;
                        $tempData[$date]['time_sheet_day'] = date('D', strtotime($date));
                    }
                        $timeSheetDateArray = array_replace($tempData, $timeSheetArray);
                        $sheetData = [$employeeName];

                    }
                    foreach ($timeSheetDateArray as $timeSheetDate) {
                        if (isset($timeSheetDate[0])) {

                     foreach ($timeSheetDate as $timeSheetDateByDay) {
                        $sheetData[] = $timeSheetDateByDay;
                    }
                 }  
                    else {
                        $sheetData [] = $timeSheetDate;
                        
                        }
                    }
                      $timeSheetWeeklyData[] = $sheetData;
               }   
            

            print_r($timeSheetWeeklyData);
                 if(empty($timeSheetWeeklyData)){
                return new JsonResponse(['status' => false,'message' => 'data empty'], 200);
                }

            $objPHPExcel = $this->get('phpexcel')->createPHPExcelObject();
            $objPHPExcel->getActiveSheet()->getStyle("A1:J1")->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->setAutoFilter('A1:A1');
            $objPHPExcel->getProperties()->setCreator("Stackover")
            ->setLastModifiedBy("john smith")
            ->setTitle("Test Agency List")
            ->setSubject("Test Agency List")
            ->setDescription("Test Agency List")
            ->setKeywords("Test Agency Excel List")
            ->setCategory("Test Agency");

           $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Agency')
            ->setCellValue('B1', 'Date Added')
            ->setCellValue('C1', '# of jobs')
            ->setCellValue('D1', '# of candidates')
            ->setCellValue('E1', 'Location')
            ->setCellValue('F1', 'Email')
            ->setCellValue('G1', 'Contact Name')
            ->setCellValue('H1', 'Last Logged in')
            ->setCellValue('I1', 'Sales Rep')
            ->setCellValue('J1', 'Active/Inactive');
    $rowID = 2;

foreach ($timeSheetWeeklyData as $rowArray) {
    $columnID = 'A';
    foreach ($rowArray as $columnValue) {

        
        $objPHPExcel->getActiveSheet()->setCellValue($columnID . $rowID, $columnValue);
        $columnID++;
    }
    $rowID++;
}


    $objPHPExcel->getActiveSheet()->setTitle('Intalex Agency List');


    $objPHPExcel->setActiveSheetIndex(0);


    // Redirect output to a client’s web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Agencies.xlsx"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.0

   // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $writer = $this->get('phpexcel')->createWriter($objPHPExcel, 'Excel2007');
    //$writer->save('php://output');
    // create the writer //
    // $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
    $writer->save($_SERVER['DOCUMENT_ROOT'].'/excel_files/'.'excel'.'-'.$start_date.'-'.$end_date.'.xlsx');
    $excelUrl = $_SERVER['HTTP_HOST'].'/excel_files/'.'excel'.'-'.$start_date.'-'.$end_date.'.xlsx';
    return new JsonResponse(['status' => true,'url'=>$excelUrl, 'message' => 'Export to Excel Url'], 200);


       }#function brackets

}#class brackets