<?php

namespace VGM\WOBundle\Controller;

use Symfony\Component\Config\Definition\Exception\Exception;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Bundle\FrameworkBundle\Controller\Controller;

use Doctrine\ORM\EntityRepository;

use Symfony\Component\Validator\Constraints\DateTime;
use VGM\WOBundle\Entity\Equipment;
use VGM\WOBundle\Entity\LaborLoop;
use VGM\WOBundle\Entity\WorkOrder;
use VGM\WOBundle\Entity\Worker;
use VGM\WOBundle\Entity\Stock;
use VGM\WOBundle\Entity\WorkOrderStockAssociation;

use VGM\WOBundle\Repository\WorkOrderRepository;
use VGM\WOBundle\Repository\EquipmentRepository;
use VGM\WOBundle\Repository\StockRepository;
use VGM\WOBundle\Repository\WorkerRepository;

use PHPExcel_Style_Font;
use PHPExcel_Shared_Font;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_NumberFormat;

use VGM\WOBundle\Utils\Utils;

/**
 * WorkOrder controller.
 *
 */
class ExporterController extends Controller
{
    /* EXCEL XLS
     * Work Orders
     */
    public function exportFilteredWorkOrders2ExcelAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         */
        $workorders = json_decode($request->get('wos'), true);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reposrt")
            ->setSubject("Work Orders cost report")
            ->setDescription("Detailed Work Orders labors with time and cost.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        $phpExcelObject->getSheet(0)
            ->setCellValue('A1', 'WO/OT')
            ->setCellValue('B1', 'Supervisor')
            ->setCellValue('C1', 'Started')
            ->setCellValue('D1', 'Time')
            ->setCellValue('E1', 'Cost');

        $sheet_index = 1;

        foreach($workorders as $jwo){
            $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($jwo["id"]);
            $ttime = 0;
            $tcost = 0;
            /*if($sheet_index > 0)*/
            $phpExcelObject->createSheet();

            $phpExcelObject->getSheet($sheet_index)
                ->setCellValue('A1', 'WO/OT')
                ->setCellValue('B1', 'Department')
                ->setCellValue('C1', 'Employee')
                ->setCellValue('D1', 'Area')
                ->setCellValue('E1', 'Work')
                ->setCellValue('F1', 'Date')
                ->setCellValue('G1', 'Starts')
                ->setCellValue('H1', 'Ends')
                ->setCellValue('I1', 'Time')
                ->setCellValue('J1', 'Cost');

            $phpExcelObject->getSheet($sheet_index)->setTitle($wo->getNumber());

            $loops = $em->getRepository('VGMWOBundle:LaborLoop')->getFilteredLoopsByWorkOrder($wo->getId());

            $loop_index = 0;
            foreach($loops as $loop){
                $phpExcelObject->getSheet($sheet_index)
                    ->setCellValue('A'.($loop_index + 2), $wo->getNumber().
                        ($loop->getAssignedStockNo() ? "-".$loop->getAssignedStockNo() : ""))
                    ->setCellValue('B'.($loop_index + 2), $loop->getDepartment()->__toString())
                    ->setCellValue('C'.($loop_index + 2), $loop->getWorker()->__toString())
                    ->setCellValue('D'.($loop_index + 2), $loop->getArea())
                    ->setCellValue('E'.($loop_index + 2), $loop->getDescription())
                    ->setCellValue('F'.($loop_index + 2), $loop->getDate() ? $loop->getDate()->format('m/d/Y') : "")
                    ->setCellValue('G'.($loop_index + 2), $loop->getStartedAt() ?
                            $loop->getStartedAt()->format('H:i') : "")
                    ->setCellValue('H'.($loop_index + 2), $loop->getFinishedAt() ?
                            $loop->getFinishedAt()->format('H:i') : "")
                    ->setCellValue('I'.($loop_index + 2), $loop->getTotalTime())
                    ->setCellValue('J'.($loop_index + 2), $loop->getTotalTime() * $loop->getHourCost());

                $ttime = $ttime + ($loop->getTotalTime() ? $loop->getTotalTime() : 0);
                $tcost = $tcost + ($loop->getTotalTime() ? $loop->getTotalTime() * $loop->getHourCost() : 0);

                $loop_index++;
            }

            $phpExcelObject->getSheet(0)
                ->setCellValue('A'.($sheet_index + 1), $wo->getNumber())
                ->setCellValue('B'.($sheet_index + 1), $wo->getSupervisor() ? $wo->getSupervisor()->__toString() : "")
                ->setCellValue('C'.($sheet_index + 1), $wo->getStartedAt() ? $wo->getStartedAt()->format('m/d/Y') : "")
                ->setCellValue('D'.($sheet_index + 1), $ttime)
                ->setCellValue('E'.($sheet_index + 1), $tcost);

            $sheet_index++;
        }
        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $phpExcelObject->setActiveSheetIndex(0);
        $phpExcelObject->getSheet(0)->setTitle("Consolidate");

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";
        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok");

        return new Response(json_encode($response));
    }
    public function exportConsolidatedFilteredWorkOrders2ExcelAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         */
        $workorders = json_decode($request->get('wos'), true);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reposrt")
            ->setSubject("Consolidate Work Orders cost report")
            ->setDescription("Detailed Work Orders labors with time and cost.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        $phpExcelObject->getSheet(0)
            ->setCellValue('A1', 'WO/OT')
            ->setCellValue('B1', 'Supervisor')
            ->setCellValue('C1', 'Started')
            ->setCellValue('D1', 'Time')
            ->setCellValue('E1', 'Cost');

        $wo_index = 1;

        foreach($workorders as $jwo){
            $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($jwo["id"]);
            $ttime = 0;
            $tcost = 0;

            $loops = $em->getRepository('VGMWOBundle:LaborLoop')->getFilteredLoopsByWorkOrder($wo->getId());

            foreach($loops as $loop){
                $ttime = $ttime + ($loop->getTotalTime() ? $loop->getTotalTime() : 0);
                $tcost = $tcost + ($loop->getTotalTime() ? $loop->getTotalTime() * $loop->getHourCost() : 0);
            }

            $phpExcelObject->getSheet(0)
                ->setCellValue('A'.($wo_index + 1), $wo->getNumber())
                ->setCellValue('B'.($wo_index + 1), $wo->getSupervisor() ? $wo->getSupervisor()->__toString() : "")
                ->setCellValue('C'.($wo_index + 1), $wo->getStartedAt() ? $wo->getStartedAt()->format('m/d/Y') : "")
                ->setCellValue('D'.($wo_index + 1), $ttime)
                ->setCellValue('E'.($wo_index + 1), $tcost);

            $wo_index++;
        }

        $phpExcelObject->setActiveSheetIndex(0);
        $phpExcelObject->getSheet(0)->setTitle("Consolidate");

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";
        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok");

        return new Response(json_encode($response));
    }
    public function exportFilteredWorkOrdersLabors2ExcelAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         */
        $workorders = json_decode($request->get('wos'), true);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reposrt")
            ->setSubject("Work Orders cost report")
            ->setDescription("Detailed Work Orders labors with time and cost.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $phpExcelObject->setActiveSheetIndex(0);

        $loop_index = 1;
        $init_values_index = 1;
        $stocks_index = 1;

        $ttime = 0;
        $rtime = 0;
        $rcost = 0;
        $otime = 0;
        $ocost = 0;
        $tcost = 0;

        if(count($workorders) == 1){
            $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($workorders[0]["id"]);
            //$wo = new WorkOrder();
            $phpExcelObject->getActiveSheet()
                ->setCellValue('A'.$init_values_index, 'WO/OT')
                ->setCellValue('B'.$init_values_index++, $wo->getNumber())
                ->setCellValue('A'.$init_values_index, 'Created at')
                ->setCellValue('B'.$init_values_index++, $wo->getCreatedAt()->format('m/d/Y'))
                ->setCellValue('A'.$init_values_index, 'Status')
                ->setCellValue('B'.$init_values_index++, $wo->getStatus() != null ? $wo->getStatus() : '---')
                ->setCellValue('A'.$init_values_index, 'Created By')
                ->setCellValue('B'.$init_values_index++, $wo->getCreatedBy() != null ? $wo->getCreatedBy() : 'Pending')
                ->setCellValue('A'.$init_values_index, 'Supervisor')
                ->setCellValue('B'.$init_values_index++, $wo->getSupervisor() != null ? $wo->getCreatedBy() : 'Pending')
                ->setCellValue('A'.$init_values_index, 'Equipment')
                ->setCellValue('B'.$init_values_index++, $wo->getEquipment() != null ? $wo->getCreatedBy() : 'Pending')
                ->setCellValue('A'.$init_values_index, 'Delivery date')
                ->setCellValue('B'.$init_values_index++, $wo->getDeliveryDate() != null ? $wo->getCreatedBy() : 'Pending')
                ->setCellValue('A'.$init_values_index, 'Sale type')
                ->setCellValue('B'.$init_values_index++, $wo->getSaletype() != null ? $wo->getCreatedBy() : 'Pending')
                ->setCellValue('A'.$init_values_index, 'Work type')
                ->setCellValue('B'.$init_values_index++, $wo->getWorktype() != null ? $wo->getCreatedBy() : 'Pending')
                ->setCellValue('A'.$init_values_index, 'Client')
                ->setCellValue('B'.$init_values_index++, $wo->getClient() != null ? $wo->getCreatedBy() : 'Pending')
                ->setCellValue('A'.$init_values_index, 'Obs')
                ->setCellValue('B'.$init_values_index++, $wo->getObs() != null ? $wo->getObs() : '---');

            $loop_index = $init_values_index;

            $phpExcelObject->getActiveSheet()
                ->setCellValue('A'.$loop_index++, 'Total time of work')
                ->setCellValue('A'.$loop_index++, 'Regular time of work')
                ->setCellValue('A'.$loop_index++, 'Regular cost')
                ->setCellValue('A'.$loop_index++, 'Overtime time of work')
                ->setCellValue('A'.$loop_index++, 'Overtime cost')
                ->setCellValue('A'.$loop_index++, 'Total cost')
                ->setCellValue('D'.$stocks_index++, 'Stocks list');

            $loop_index++;

            foreach($wo->getStocksFull() as $stock){
                $phpExcelObject->getActiveSheet()
                    ->setCellValue('D'.$stocks_index++, $stock->getStock()->getCode());

                if($loop_index <= $stocks_index)
                    $loop_index = $stocks_index + 1;
            }

            if($this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR')){
                $phpExcelObject->getActiveSheet()
                    ->setCellValue('F1', 'Price')
                    ->setCellValue('F2', round($wo->getPrice() != null ? $wo->getPrice() : 0, 2));
            }

        }

        $phpExcelObject->getActiveSheet()
            ->setCellValue('A'.$loop_index, 'WO/OT')
            ->setCellValue('B'.$loop_index, 'Department')
            ->setCellValue('C'.$loop_index, 'Employee')
            ->setCellValue('D'.$loop_index, 'Area')
            ->setCellValue('E'.$loop_index, 'Work')
            ->setCellValue('F'.$loop_index, 'Date')
            ->setCellValue('G'.$loop_index, 'Starts')
            ->setCellValue('H'.$loop_index, 'Ends')
            ->setCellValue('I'.$loop_index, 'Total time')
            ->setCellValue('J'.$loop_index, 'Regular time')
            ->setCellValue('K'.$loop_index, 'Regular cost')
            ->setCellValue('L'.$loop_index, 'Overtime')
            ->setCellValue('M'.$loop_index, 'Overtime cost')
            ->setCellValue('N'.$loop_index++, 'Total cost');

        foreach($workorders as $jwo){
            $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($jwo["id"]);

            $loops = $em->getRepository('VGMWOBundle:LaborLoop')->getFilteredLoopsByWorkOrder($wo->getId());

            foreach($loops as $loop){
                $lttime = 0;
                $lrtime = 0;
                $lotime = 0;
                $lrcost = 0;
                $locost = 0;

                $lttime = $loop->getTotalTime() != null ? $loop->getTotalTime() : 0;
                $lrtime = ($loop->getCOvertime() != null && $loop->getTotalTime() != null) ?
                    $loop->getTotalTime() - $loop->getCOvertime() : $loop->getTotalTime() != null ?
                        $loop->getTotalTime() : 0;
                $lotime = $loop->getCOvertime() != null ? $loop->getCOvertime() : 0;

                $lrcost = ($loop->getHourCost() != null && $lrtime != 0) ? $lrtime * $loop->getHourCost() : 0;
                $locost = ($loop->getHourCost() != null && $lotime > 0) ? $lotime * $loop->getHourCost() * 1.5 : 0;


                $phpExcelObject->getActiveSheet()
                    ->setCellValue('A'.($loop_index), $wo->getNumber().
                        ($loop->getAssignedStockNo() ? "-".$loop->getAssignedStockNo() : ""))
                    ->setCellValue('B'.($loop_index), $loop->getDepartment() ?
                            $loop->getDepartment()->__toString() : "")
                    ->setCellValue('C'.($loop_index), $loop->getWorker()->__toString())
                    ->setCellValue('D'.($loop_index), $loop->getArea())
                    ->setCellValue('E'.($loop_index), $loop->getDescription())
                    ->setCellValue('F'.($loop_index), $loop->getDate() ? $loop->getDate()->format('m/d/Y') : "")
                    ->setCellValue('G'.($loop_index), $loop->getStartedAt() ?
                            $loop->getStartedAt()->format('H:i') : "")
                    ->setCellValue('H'.($loop_index), $loop->getFinishedAt() ?
                            $loop->getFinishedAt()->format('H:i') : "")
                    ->setCellValue('I'.($loop_index), round($lttime, 2))
                    ->setCellValue('J'.($loop_index), round($lrtime, 2))
                    ->setCellValue('K'.($loop_index), round($lrcost, 2))
                    ->setCellValue('L'.($loop_index), round($lotime, 2))
                    ->setCellValue('M'.($loop_index), round($locost, 2))
                    ->setCellValue('N'.($loop_index), round($lrcost + $locost, 2));


                $ttime += $lttime;
                $rtime += $lrtime;
                $rcost += $lrcost;
                $otime += $lotime;
                $ocost += $locost;
                $tcost += $lrcost + $locost;

                $loop_index++;
            }
        }

        if(count($workorders) == 1){
            $phpExcelObject->getActiveSheet()
                ->setCellValue('B'.$init_values_index++, round($ttime, 2))
                ->setCellValue('B'.$init_values_index++, round($rtime, 2))
                ->setCellValue('B'.$init_values_index++, round($rcost, 2))
                ->setCellValue('B'.$init_values_index++, round($otime, 2))
                ->setCellValue('B'.$init_values_index++, round($ocost, 2))
                ->setCellValue('B'.$init_values_index++, round($tcost, 2));
        }

        $phpExcelObject->getSheet(0)->setTitle("All labors");

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";
        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok");

        return new Response(json_encode($response));
    }

    /*
     * Workers
     */
    public function exportFilteredWorkers2ExcelAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         */
        $workers_ids = json_decode($request->get('workers'), true);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reposrt")
            ->setSubject("Workers report")
            ->setDescription("Workers data.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        $worker_index = 0;
        $sheet_index = 0;
        $ids = [];

        foreach($workers_ids as $jwo){
            $ids[] = $jwo["id"];
        }

        $workers = $em->getRepository('VGMWOBundle:Worker')->findBy(array('id' => $ids),
            array('name' => 'ASC', 'lastname' => 'ASC'));

        foreach($workers as $wo){
            //$wo = $em->getRepository('VGMWOBundle:Worker')->find($jwo["id"]);

            $loops = $em->getRepository('VGMWOBundle:LaborLoop')->getFilteredLoopsByWorker($wo->getId());

            $regular_time = 0;
            $overtime = 0;
            $regular_cost = 0;
            $overtime_cost = 0;

            foreach($loops as $loop){
                if($loop->getCOvertime() > 0){
                    $regular_time += $loop->getTotalTime() - $loop->getCOvertime();
                    $regular_cost += (($loop->getTotalTime() - $loop->getCOvertime()) * $loop->getHourCost());

                    $overtime += $loop->getCOvertime();
                    $overtime_cost += $loop->getCOvertime()* $loop->getHourCost() * 1.5;
                }
                else{
                    $regular_time += $loop->getTotalTime();
                    $regular_cost += $loop->getTotalTime() * $loop->getHourCost();
                }
            }

            $phpExcelObject->getSheet($sheet_index)
                ->setCellValue('A1', 'Code')
                ->setCellValue('B1', 'Department')
                ->setCellValue('C1', 'Name')
                ->setCellValue('D1', 'Is supervisor')
                ->setCellValue('E1', 'Is Fulltime')
                ->setCellValue('F1', 'Payment schedule')
                ->setCellValue('G1', 'Rayment rate')
                ->setCellValue('H1', 'Regular Time')
                ->setCellValue('I1', 'Regular time cost')
                ->setCellValue('J1', 'Overtime')
                ->setCellValue('K1', 'Overtime cost')
                ->setCellValue('L1', 'Paid until now');

            $phpExcelObject->getSheet($sheet_index)->setTitle('Workers List');

            $phpExcelObject->getSheet($sheet_index)
                ->setCellValue('A'.($worker_index + 2), $wo->getWorkerCode())
                ->setCellValue('B'.($worker_index + 2), $wo->getDepartment()->__toString())
                ->setCellValue('C'.($worker_index + 2), $wo->__toString())
                ->setCellValue('D'.($worker_index + 2), $wo->getSupervisor() ? 'X' : '')
                ->setCellValue('E'.($worker_index + 2), $wo->getFullTime() ? 'X' : '')
                ->setCellValue('F'.($worker_index + 2), $wo->getNpayschedule() ? $wo->getNpayschedule()->getName() : '')
                ->setCellValue('G'.($worker_index + 2), $wo->getPayRate())
                ->setCellValue('H'.($worker_index + 2), round($regular_time, 2))
                ->setCellValue('I'.($worker_index + 2), round($regular_cost, 2))
                ->setCellValue('J'.($worker_index + 2), round($overtime, 2))
                ->setCellValue('K'.($worker_index + 2), round($overtime_cost, 2))
                ->setCellValue('L'.($worker_index + 2), round($regular_cost + $overtime_cost, 2));

            $worker_index++;
        }

        $phpExcelObject->setActiveSheetIndex(0);
        $phpExcelObject->getSheet(0)->setTitle("Workers");

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";
        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok");

        return new Response(json_encode($response));
    }
    public function exportFilteredWorkersLabors2ExcelAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         */

        $workers_ids = json_decode($request->get('workers'), true);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reposrt")
            ->setSubject("Workers report")
            ->setDescription("Workers data.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        $phpExcelObject->setActiveSheetIndex(0);

        $loop_index = 1;
        $total_hours = 0;
        $total_cost = 0;

        $ids = [];
        foreach($workers_ids as $jwo){
            $ids[] = $jwo["id"];
        }

        $sorters = [];
        $sorters['sorter_shift_dep'] = null;
        $sorters['sorter_shift_worker'] = null;
        $sorters['sorter_shift_description'] = null;
        $sorters['sorter_shift_date'] = 'ASC';
        $sorters['sorter_shift_area'] = null;
        $sorters['sorter_shift_workorder'] = null;
        $sorters['sorter_shift_asn'] = null;

        $loops = $em->getRepository('VGMWOBundle:LaborLoop')
            ->getFilteredLoopsByWorkers($ids, null, $sorters);

        if(count($ids) == 1){
            $worker = $em->getRepository('VGMWOBundle:Worker')->find($ids[0]);

            $phpExcelObject->getActiveSheet()
                ->setCellValue('A1', 'Full name')
                ->setCellValue('B1', $worker->__toString())
                ->setCellValue('A2', 'Code')
                ->setCellValue('B2', $worker->getWorkerCode())
                ->setCellValue('A3', 'Department')
                ->setCellValue('B3', $worker->getDepartment())
                ->setCellValue('A4', 'Labors count')
                ->setCellValue('B4', count($loops))
                ->setCellValue('A5', 'Total hours')
                ->setCellValue('A6', 'Total cost');
            $loop_index = 8;
        }

        $phpExcelObject->getActiveSheet()
            ->setCellValue('A'.$loop_index, 'WO/OT')
            ->setCellValue('B'.$loop_index, 'Department')
            ->setCellValue('C'.$loop_index, 'Employee')
            ->setCellValue('D'.$loop_index, 'Area')
            ->setCellValue('E'.$loop_index, 'Work')
            ->setCellValue('F'.$loop_index, 'Date')
            ->setCellValue('G'.$loop_index, 'Starts')
            ->setCellValue('H'.$loop_index, 'Ends')
            ->setCellValue('I'.$loop_index, 'Total time')
            ->setCellValue('J'.$loop_index, 'Regular time')
            ->setCellValue('K'.$loop_index, 'Regular cost')
            ->setCellValue('L'.$loop_index, 'Overtime')
            ->setCellValue('M'.$loop_index, 'Overtime cost')
            ->setCellValue('N'.$loop_index, 'Total cost');
        $loop_index++;



        foreach($loops as $loop){
            $ttime = $loop->getTotalTime() != null ? $loop->getTotalTime() : 0;
            $rtime = ($loop->getCOvertime() != null && $loop->getTotalTime() != null) ?
                $loop->getTotalTime() - $loop->getCOvertime() : $loop->getTotalTime() != null ?
                    $loop->getTotalTime() : 0;
            $otime = $loop->getCOvertime() != null ? $loop->getCOvertime() : 0;

            $rcost = ($loop->getHourCost() != null && $rtime != 0) ? $rtime * $loop->getHourCost() : 0;
            $ocost = ($loop->getHourCost() != null && $otime > 0) ? $otime * $loop->getHourCost() * 1.5 : 0;

            $phpExcelObject->getActiveSheet()
                ->setCellValue('A'.($loop_index), $loop->getWorkOrder()->getNumber().
                    ($loop->getAssignedStockNo() ? "-".$loop->getAssignedStockNo() : ""))
                ->setCellValue('B'.($loop_index), $loop->getDepartment() ?
                        $loop->getDepartment()->__toString() : "")
                ->setCellValue('C'.($loop_index), $loop->getWorker()->__toString())
                ->setCellValue('D'.($loop_index), $loop->getArea())
                ->setCellValue('E'.($loop_index), $loop->getDescription())
                ->setCellValue('F'.($loop_index), $loop->getDate() ? $loop->getDate()->format('m/d/Y') : "")
                ->setCellValue('G'.($loop_index), $loop->getStartedAt() ?
                        $loop->getStartedAt()->format('H:i') : "")
                ->setCellValue('H'.($loop_index), $loop->getFinishedAt() ?
                        $loop->getFinishedAt()->format('H:i') : "")
                ->setCellValue('I'.($loop_index), $loop->getTotalTime() ? $loop->getTotalTime() : 0)
                ->setCellValue('J'.($loop_index), round($rtime, 2))
                ->setCellValue('K'.($loop_index), round($rcost, 2))
                ->setCellValue('L'.($loop_index), round($otime, 2))
                ->setCellValue('M'.($loop_index), round($ocost, 2))
                ->setCellValue('N'.($loop_index), round($rcost + $ocost, 2));

            $total_hours += $ttime;
            $total_cost += ($rcost + $ocost);
            $loop_index++;
        }

        $phpExcelObject->getActiveSheet()
            ->setCellValue('B5', round($total_hours, 2))
            ->setCellValue('B6', round($total_cost, 2));
        $phpExcelObject->getSheet(0)->setTitle("All labors");

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";
        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok");

        return new Response(json_encode($response));
    }

    //Last Version Exporter
    public function exportWorkOrdersList2XLSAction(Request $request){
        /**
         * Getting data request
         */
        $svalue = $request->get('svalue');
        $status = $request->get('status');

        $rows = $this->getWorkOrdersRows($svalue, $status);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getDefaultStyle()
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reposrt")
            ->setSubject("Workers report")
            ->setDescription("Workers data.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        $phpExcelObject->setActiveSheetIndex(0);
        $phpExcelObject->getActiveSheet()->freezePane('A2');
        $row_index = 2;
        if($this->getUser()->hasRole('ROLE_ACCOUNTING') || $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR') || $this->getUser()->hasRole('ROLE_ADMIN')){
            $phpExcelObject->getActiveSheet()->getStyle("A1:N1")->getFont()->setBold(true);
            $phpExcelObject->getActiveSheet()
                ->setCellValue('A1', 'Created')
                ->setCellValue('B1', 'WO/OT')
                ->setCellValue('C1', 'Stock')
                ->setCellValue('D1', 'Inv. Status')
                ->setCellValue('E1', 'Inv. Amount')
                ->setCellValue('F1', 'ID')
                ->setCellValue('G1', 'Make')
                ->setCellValue('H1', 'Model')
                ->setCellValue('I1', 'Det.')
                ->setCellValue('J1', 'Condition')
                ->setCellValue('K1', 'Supervisor')
                ->setCellValue('L1', 'Status')
                ->setCellValue('M1', 'Client')
                ->setCellValue('N1', 'Due date');

            foreach($rows as $row){
                $phpExcelObject->getActiveSheet()
                    ->setCellValue('A'.$row_index, $row["created"])
                    ->setCellValue('B'.$row_index, $row["wo_number"])
                    ->setCellValue('C'.$row_index, $row["stock"])
                    ->setCellValue('D'.$row_index, $row["istatus"])
                    ->setCellValue('E'.$row_index, $row["iamount"])
                    ->setCellValue('F'.$row_index, $row["equipment"])
                    ->setCellValue('G'.$row_index, $row["brand"])
                    ->setCellValue('H'.$row_index, $row["model"])
                    ->setCellValue('I'.$row_index, $row["destiny"])
                    ->setCellValue('J'.$row_index, $row["work"])
                    ->setCellValue('K'.$row_index, $row["supervisor"])
                    ->setCellValue('L'.$row_index, $row["status"])
                    ->setCellValue('M'.$row_index, $row["client"])
                    ->setCellValue('N'.$row_index++, $row["delivery"]);
            }

            foreach(range('A','N') as $columnID) {
                $phpExcelObject->getActiveSheet()->getColumnDimension($columnID)
                    ->setAutoSize(true);
            }

            $phpExcelObject->getActiveSheet()->setAutoFilter("A1:N".$row_index);
        }
        else{
            $phpExcelObject->getActiveSheet()->getStyle("A1:L1")->getFont()->setBold(true);
            $phpExcelObject->getActiveSheet()
                ->setCellValue('A1', 'Created')
                ->setCellValue('B1', 'WO/OT')
                ->setCellValue('C1', 'Stock')
                ->setCellValue('D1', 'ID')
                ->setCellValue('E1', 'Make')
                ->setCellValue('F1', 'Model')
                ->setCellValue('G1', 'Det.')
                ->setCellValue('H1', 'Condition')
                ->setCellValue('I1', 'Supervisor')
                ->setCellValue('J1', 'Status')
                ->setCellValue('K1', 'Client')
                ->setCellValue('L1', 'Due date');

            foreach($rows as $row){
                $phpExcelObject->getActiveSheet()
                    ->setCellValue('A'.$row_index, $row["created"])
                    ->setCellValue('B'.$row_index, $row["wo_number"])
                    ->setCellValue('C'.$row_index, $row["stock"])
                    ->setCellValue('D'.$row_index, $row["equipment"])
                    ->setCellValue('E'.$row_index, $row["brand"])
                    ->setCellValue('F'.$row_index, $row["model"])
                    ->setCellValue('G'.$row_index, $row["destiny"])
                    ->setCellValue('H'.$row_index, $row["work"])
                    ->setCellValue('I'.$row_index, $row["supervisor"])
                    ->setCellValue('J'.$row_index, $row["status"])
                    ->setCellValue('K'.$row_index, $row["client"])
                    ->setCellValue('L'.$row_index++, $row["delivery"]);
            }

            foreach(range('A','L') as $columnID) {
                $phpExcelObject->getActiveSheet()->getColumnDimension($columnID)
                    ->setAutoSize(true);
            }

            $phpExcelObject->getActiveSheet()->setAutoFilter("A1:L".$row_index);
        }

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";

        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok");

        return new Response(json_encode($response));
    }
    public function getWorkOrdersRows($svalue = null, $status = null){
        $em = $this->getDoctrine()->getEntityManager();
        $sql = null;
        switch($status){
            //All
            case 0:{
                $sql = "SELECT * FROM ((SELECT workorder.id as wo_id,DATE_FORMAT(workorder.created_at, '%m/%d/%y') as created,workorder.number as wo_number,"
                    ." null as stock,equipment.code as equipment,null as brand,null as model,saletype.sale_type as destiny,"
                    ." worktype.work_type as work,worker.name as supervisor,workorder.status as status,invoice_data.status as istatus,invoice_data.amount as iamount,"
                    ." client.name as client,client.internal as client_type,DATE_FORMAT(workorder.delivery_date, '%m/%d/%y') as delivery"
                    ." FROM workorder LEFT JOIN equipment ON workorder.equipment_id = equipment.id"
                    ." LEFT JOIN saletype ON workorder.saletype_id = saletype.id LEFT JOIN worktype ON workorder.worktype_id = worktype.id"
                    ." LEFT JOIN worker ON workorder.supervisor_id = worker.id LEFT JOIN client ON workorder.client_id = client.id"
                    ." LEFT JOIN (SELECT i1.* FROM invoicereference i1 WHERE i1.id = (SELECT i2.id FROM invoicereference i2"
                    ." WHERE i2.workorder_id = i1.workorder_id"
                    ." ORDER BY i2.order_number DESC LIMIT 1)) invoice_data ON invoice_data.workorder_id = workorder.id"
                    ." WHERE NOT EXISTS (SELECT * FROM workorderstockassociation WHERE workorderstockassociation.workorder_id = workorder.id))"
                    ."  UNION (SELECT workorder.id as wo_id,DATE_FORMAT(workorder.created_at, '%m/%d/%y') as created,"
                    ." CONCAT(workorder.number,'-',as_stock.as_number) as wo_number,as_stock.code as stock,equipment.code as equipment,SUBSTR(as_stock.brand,1,10) as brand,"
                    ." SUBSTR(as_stock.model,1,10) as model,"
                    ." CASE WHEN as_stock.sale_type IS NULL THEN saletype.sale_type ELSE as_stock.sale_type END AS destiny,"
                    ." CASE WHEN as_stock.work_type IS NULL THEN worktype.work_type ELSE as_stock.work_type END AS work,"
                    ." worker.name as supervisor,as_stock.status as status,as_stock.istatus,as_stock.iamount,as_stock.client as client,"
                    ." as_stock.client_type as client_type,DATE_FORMAT(as_stock.delivery_date, '%m/%d/%y') as delivery "
                    ." FROM workorder LEFT JOIN equipment ON workorder.equipment_id = equipment.id"
                    ." LEFT JOIN saletype ON workorder.saletype_id = saletype.id LEFT JOIN worktype ON workorder.worktype_id = worktype.id"
                    ." LEFT JOIN worker ON workorder.supervisor_id = worker.id LEFT JOIN (SELECT stock.code,stock.brand as brand,stock.model as model,"
                    ." wsa.status as status, wsa.as_number,client.name as client, client.internal as client_type, wsa.delivery_date as delivery_date,"
                    ." stock.asdb_id as asdb_id,wsa.workorder_id as workorder_id,saletype.sale_type,worktype.work_type, invoice_data.status as istatus,invoice_data.amount as iamount"
                    ." FROM workorderstockassociation wsa LEFT JOIN stock ON stock.id = wsa.stock_id"
                    ." LEFT JOIN (SELECT i1.* FROM invoicereference i1 WHERE i1.id = (SELECT i2.id FROM invoicereference i2"
                    ." WHERE i2.wostock_id = i1.wostock_id"
                    ." ORDER BY i2.order_number DESC LIMIT 1)) invoice_data ON invoice_data.wostock_id = wsa.id"
                    ." LEFT JOIN client ON wsa.client_id = client.id LEFT JOIN saletype ON wsa.saletype_id = saletype.id"
                    ." LEFT JOIN worktype ON wsa.worktype_id = worktype.id) as as_stock ON workorder.id = as_stock.workorder_id"
                    ." WHERE EXISTS (SELECT * FROM workorderstockassociation WHERE workorderstockassociation.workorder_id = workorder.id))) as WO"
                    ." WHERE wo_number <> '9999'";
                break;
            }
            //Current
            case 1:{
                $sql = "SELECT * FROM ((SELECT workorder.id as wo_id,DATE_FORMAT(workorder.created_at, '%m/%d/%y') as created,workorder.number as wo_number,"
                    ." null as stock,equipment.code as equipment,null as brand,null as model,saletype.sale_type as destiny,"
                    ." worktype.work_type as work,worker.name as supervisor,workorder.status as status,invoice_data.status as istatus,invoice_data.amount as iamount,"
                    ." client.name as client,client.internal as client_type,DATE_FORMAT(workorder.delivery_date, '%m/%d/%y') as delivery"
                    ." FROM workorder LEFT JOIN equipment ON workorder.equipment_id = equipment.id"
                    ." LEFT JOIN saletype ON workorder.saletype_id = saletype.id LEFT JOIN worktype ON workorder.worktype_id = worktype.id"
                    ." LEFT JOIN worker ON workorder.supervisor_id = worker.id LEFT JOIN client ON workorder.client_id = client.id"
                    ." LEFT JOIN (SELECT i1.* FROM invoicereference i1 WHERE i1.id = (SELECT i2.id FROM invoicereference i2"
                    ." WHERE i2.workorder_id = i1.workorder_id"
                    ." ORDER BY i2.order_number DESC LIMIT 1)) invoice_data ON invoice_data.workorder_id = workorder.id"
                    ." WHERE NOT EXISTS (SELECT * FROM workorderstockassociation WHERE workorderstockassociation.workorder_id = workorder.id)"
                    ." AND workorder.status = 'Current') UNION (SELECT workorder.id as wo_id,DATE_FORMAT(workorder.created_at, '%m/%d/%y') as created,"
                    ." CONCAT(workorder.number,'-',as_stock.as_number) as wo_number,as_stock.code as stock,equipment.code as equipment,SUBSTR(as_stock.brand,1,10) as brand,"
                    ." SUBSTR(as_stock.model,1,10) as model,"
                    ." CASE WHEN as_stock.sale_type IS NULL THEN saletype.sale_type ELSE as_stock.sale_type END AS destiny,"
                    ." CASE WHEN as_stock.work_type IS NULL THEN worktype.work_type ELSE as_stock.work_type END AS work,"
                    ." worker.name as supervisor,as_stock.status as status,as_stock.istatus,as_stock.iamount,as_stock.client as client,"
                    ." as_stock.client_type as client_type,DATE_FORMAT(as_stock.delivery_date, '%m/%d/%y') as delivery "
                    ." FROM workorder LEFT JOIN equipment ON workorder.equipment_id = equipment.id"
                    ." LEFT JOIN saletype ON workorder.saletype_id = saletype.id LEFT JOIN worktype ON workorder.worktype_id = worktype.id"
                    ." LEFT JOIN worker ON workorder.supervisor_id = worker.id LEFT JOIN (SELECT stock.code,stock.brand as brand,stock.model as model,"
                    ." wsa.status as status, wsa.as_number,client.name as client, client.internal as client_type, wsa.delivery_date as delivery_date,"
                    ." stock.asdb_id as asdb_id,wsa.workorder_id as workorder_id,saletype.sale_type,worktype.work_type, invoice_data.status as istatus,invoice_data.amount as iamount"
                    ." FROM workorderstockassociation wsa LEFT JOIN stock ON stock.id = wsa.stock_id"
                    ." LEFT JOIN (SELECT i1.* FROM invoicereference i1 WHERE i1.id = (SELECT i2.id FROM invoicereference i2"
                    ." WHERE i2.wostock_id = i1.wostock_id"
                    ." ORDER BY i2.order_number DESC LIMIT 1)) invoice_data ON invoice_data.wostock_id = wsa.id"
                    ." LEFT JOIN client ON wsa.client_id = client.id LEFT JOIN saletype ON wsa.saletype_id = saletype.id"
                    ." LEFT JOIN worktype ON wsa.worktype_id = worktype.id) as as_stock ON workorder.id = as_stock.workorder_id"
                    ." WHERE EXISTS (SELECT * FROM workorderstockassociation WHERE workorderstockassociation.workorder_id = workorder.id"
                    ." AND workorderstockassociation.status = 'Current'))) as WO WHERE status = 'Current' AND wo_number <> '9999'";
                break;
            }
            //Others
            default:{
                $sql = "SELECT * FROM ((SELECT workorder.id as wo_id,DATE_FORMAT(workorder.created_at, '%m/%d/%y') as created,workorder.number as wo_number,"
                    ." null as stock,equipment.code as equipment,null as brand,null as model,saletype.sale_type as destiny,"
                    ." worktype.work_type as work,worker.name as supervisor,workorder.status as status,invoice_data.status as istatus,invoice_data.amount as iamount,"
                    ." client.name as client,client.internal as client_type,DATE_FORMAT(workorder.delivery_date, '%m/%d/%y') as delivery"
                    ." FROM workorder LEFT JOIN equipment ON workorder.equipment_id = equipment.id"
                    ." LEFT JOIN saletype ON workorder.saletype_id = saletype.id LEFT JOIN worktype ON workorder.worktype_id = worktype.id"
                    ." LEFT JOIN worker ON workorder.supervisor_id = worker.id LEFT JOIN client ON workorder.client_id = client.id"
                    ." LEFT JOIN (SELECT i1.* FROM invoicereference i1 WHERE i1.id = (SELECT i2.id FROM invoicereference i2"
                    ." WHERE i2.workorder_id = i1.workorder_id"
                    ." ORDER BY i2.order_number DESC LIMIT 1)) invoice_data ON invoice_data.workorder_id = workorder.id"
                    ." WHERE NOT EXISTS (SELECT * FROM workorderstockassociation WHERE workorderstockassociation.workorder_id = workorder.id)"
                    ." AND workorder.status <> 'Current') UNION (SELECT workorder.id as wo_id,DATE_FORMAT(workorder.created_at, '%m/%d/%y') as created,"
                    ." CONCAT(workorder.number,'-',as_stock.as_number) as wo_number,as_stock.code as stock,equipment.code as equipment,SUBSTR(as_stock.brand,1,10) as brand,"
                    ." SUBSTR(as_stock.model,1,10) as model,"
                    ." CASE WHEN as_stock.sale_type IS NULL THEN saletype.sale_type ELSE as_stock.sale_type END AS destiny,"
                    ." CASE WHEN as_stock.work_type IS NULL THEN worktype.work_type ELSE as_stock.work_type END AS work,"
                    ." worker.name as supervisor,as_stock.status as status,as_stock.istatus,as_stock.iamount,as_stock.client as client,"
                    ." as_stock.client_type as client_type,DATE_FORMAT(as_stock.delivery_date, '%m/%d/%y') as delivery "
                    ." FROM workorder LEFT JOIN equipment ON workorder.equipment_id = equipment.id"
                    ." LEFT JOIN saletype ON workorder.saletype_id = saletype.id LEFT JOIN worktype ON workorder.worktype_id = worktype.id"
                    ." LEFT JOIN worker ON workorder.supervisor_id = worker.id LEFT JOIN (SELECT stock.code,stock.brand as brand,stock.model as model,"
                    ." wsa.status as status, wsa.as_number,client.name as client, client.internal as client_type, wsa.delivery_date as delivery_date,"
                    ." stock.asdb_id as asdb_id,wsa.workorder_id as workorder_id,saletype.sale_type,worktype.work_type, invoice_data.status as istatus,invoice_data.amount as iamount"
                    ." FROM workorderstockassociation wsa LEFT JOIN stock ON stock.id = wsa.stock_id"
                    ." LEFT JOIN (SELECT i1.* FROM invoicereference i1 WHERE i1.id = (SELECT i2.id FROM invoicereference i2"
                    ." WHERE i2.wostock_id = i1.wostock_id"
                    ." ORDER BY i2.order_number DESC LIMIT 1)) invoice_data ON invoice_data.wostock_id = wsa.id"
                    ." LEFT JOIN client ON wsa.client_id = client.id LEFT JOIN saletype ON wsa.saletype_id = saletype.id"
                    ." LEFT JOIN worktype ON wsa.worktype_id = worktype.id) as as_stock ON workorder.id = as_stock.workorder_id"
                    ." WHERE EXISTS (SELECT * FROM workorderstockassociation WHERE workorderstockassociation.workorder_id = workorder.id"
                    ." AND workorderstockassociation.status <> 'Current'))) as WO WHERE status <> 'Current' AND wo_number <> '9999'";
                break;
            }
        }

        if($status == 0 && $svalue != "")
            $sql = $sql." AND WO.created LIKE '%".$svalue."%' OR WO.wo_number LIKE '%".$svalue."%' OR WO.stock LIKE '%"
                .$svalue."%' OR WO.equipment LIKE '%".$svalue."%' OR WO.brand LIKE '%".$svalue."%' OR WO.model LIKE '%"
                .$svalue."%' OR WO.destiny LIKE '%".$svalue."%' OR WO.work LIKE '%".$svalue."%' OR WO.supervisor LIKE '%"
                .$svalue."%' OR WO.status LIKE '%".$svalue."%' OR WO.client LIKE '%".$svalue."%' OR WO.delivery LIKE '%".$svalue."%'";
        elseif($svalue != "")
            $sql = $sql." AND (WO.created LIKE '%".$svalue."%' OR WO.wo_number LIKE '%".$svalue."%' OR WO.stock LIKE '%"
                .$svalue."%' OR WO.equipment LIKE '%".$svalue."%' OR WO.brand LIKE '%".$svalue."%' OR WO.model LIKE '%"
                .$svalue."%' OR WO.destiny LIKE '%".$svalue."%' OR WO.work LIKE '%".$svalue."%' OR WO.supervisor LIKE '%"
                .$svalue."%' OR WO.status LIKE '%".$svalue."%' OR WO.client LIKE '%".$svalue."%' OR WO.delivery LIKE '%".$svalue."%')";

        $connection = $em->getConnection();
        $statement = $connection->prepare($sql);
        $statement->execute();
        return $statement->fetchAll();
    }

    public function exportClientsList2XLSAction(Request $request){
        /**
         * Getting data request
         */
        $svalue = $request->get('svalue');

        $rows = $this->getClientsRows($svalue);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getDefaultStyle()
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reports")
            ->setSubject("Customers report")
            ->setDescription("Customers data.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        $phpExcelObject->setActiveSheetIndex(0);
        $phpExcelObject->getActiveSheet()->getStyle("A1:I1")->getFont()->setBold(true);
        $phpExcelObject->getActiveSheet()->freezePane('A2');
        $phpExcelObject->getActiveSheet()
            ->setCellValue('A1', 'Customer')
            ->setCellValue('B1', 'e-mail')
            ->setCellValue('C1', 'Phone numbers')
            ->setCellValue('D1', 'Bill to')
            ->setCellValue('E1', 'Ship to')
            ->setCellValue('F1', 'Street')
            ->setCellValue('G1', 'City')
            ->setCellValue('H1', 'State')
            ->setCellValue('I1', 'Zip code');

        $row_index = 2;
        foreach($rows as $row){
            $data_row_index = 1;
            $phpExcelObject->getActiveSheet()
                ->setCellValue('A'.$row_index, $row["name"])
                ->setCellValue('B'.$row_index, $row["email"])
                ->setCellValue('C'.$row_index, $row["phones"])
                ->setCellValue('D'.$row_index, $row["bill_info"])
                ->setCellValue('E'.$row_index, $row["ship_info"])
                ->setCellValue('F'.$row_index, $row["adrees"])
                ->setCellValue('G'.$row_index, $row["city"])
                ->setCellValue('H'.$row_index, $row["state"])
                ->setCellValue('I'.$row_index++, $row["zip_code"]);
        }

        foreach(range('A','I') as $columnID) {
            $phpExcelObject->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

        $phpExcelObject->getActiveSheet()->setAutoFilter("A1:I".$row_index);

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";

        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok", "cant" => count($rows));

        return new Response(json_encode($response));
    }
    public function getClientsRows($svalue = null){
        $em = $this->getDoctrine()->getEntityManager();

        $sql = "SELECT client.name,"
            ."CASE WHEN client.email IS NOT NULL THEN client.email ELSE '' END as email,"
            ."CASE WHEN (client.phone1 IS NOT NULL AND client.phone2 IS NOT NULL)"
            ." THEN CONCAT(client.phone1,', ',client.phone2) ELSE CASE WHEN client.phone1 IS NOT NULL THEN client.phone1 ELSE"
            ." CASE WHEN client.phone1 IS NOT NULL THEN client.phone1 ELSE '' END END END as phones,"
            ."CASE WHEN client.bill_info IS NOT NULL THEN client.bill_info ELSE '' END as bill_info,"
            ."CASE WHEN client.ship_info IS NOT NULL THEN client.ship_info ELSE '' END as ship_info,"
            ."CASE WHEN client.adrees IS NOT NULL THEN client.adrees ELSE '' END as adrees,"
            ."CASE WHEN client.city IS NOT NULL THEN client.city ELSE '' END as city,"
            ."CASE WHEN client.state IS NOT NULL THEN client.state ELSE '' END as state,"
            ."CASE WHEN client.zip_code IS NOT NULL THEN client.zip_code ELSE '' END as zip_code FROM client";

        if($svalue != "")
            $sql = $sql." WHERE name LIKE '%".$svalue."%' OR email LIKE '%".$svalue."%' OR phones LIKE '%"
                .$svalue."%' OR bill_info LIKE '%".$svalue."%' OR client.ship_info LIKE '%".$svalue."%' OR adrees LIKE '%"
                .$svalue."%' OR city LIKE '%".$svalue."%' OR state LIKE '%".$svalue."%' OR zip_code LIKE '%"
                .$svalue."%'";

        $sql = $sql." ORDER BY name ASC";

        $connection = $em->getConnection();
        $statement = $connection->prepare($sql);
        $statement->execute();
        return $statement->fetchAll();
    }

    public function exportInvoicesList2XLSAction(Request $request){
        /**
         * Getting data request
         */
        $invoice_status = $request->get('invoice_status');
        $invoice_destination = $request->get('invoice_destination') != null && $request->get('invoice_destination') != '' ?
            $request->get('invoice_destination') : null;
        $invoice_number = $request->get('invoice_number');
        $invoice_date_from = $request->get('invoice_date_from') != null && $request->get('invoice_date_from') != '' ?
            explode('/',$request->get('invoice_date_from')) : null;
        $invoice_date_to = $request->get('invoice_date_to') != null && $request->get('invoice_date_to') != '' ?
            explode('/',$request->get('invoice_date_to')) : null;
        $wo_stock = $request->get('wo_stock');
        $client_name = $request->get('client_name');
        $equipment = $request->get('equipment');

        $rows = $this->getInvoicesListAction($invoice_status,$invoice_destination,$invoice_number,$invoice_date_from,$invoice_date_to,
            $wo_stock,$client_name,$equipment);

        $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();

        $phpExcelObject->getDefaultStyle()
            ->getAlignment()
            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $phpExcelObject->getProperties()->setCreator("Aeroservicios USA")
            ->setLastModifiedBy("Aeroservicios.com")
            ->setTitle("Aeroservicios reports")
            ->setSubject("Invoices report")
            ->setDescription("Invoices data.")
            ->setKeywords("office 2005 openxml php")
            ->setCategory("File");

        $phpExcelObject->setActiveSheetIndex(0);
        $phpExcelObject->getActiveSheet()->getStyle("A1:K1")->getFont()->setBold(true);
        $phpExcelObject->getActiveSheet()->freezePane('A2');
        $phpExcelObject->getActiveSheet()
            ->setCellValue('A1', 'Created at')
            ->setCellValue('B1', 'Purpose')
            ->setCellValue('C1', 'Status')
            ->setCellValue('D1', 'Client')
            ->setCellValue('E1', 'Equipment')
            ->setCellValue('F1', 'WO-Stock')
            ->setCellValue('G1', 'Stock')
            ->setCellValue('H1', 'To be invoiced')
            ->setCellValue('I1', 'Invoiced')
            ->setCellValue('J1', 'Inv. date')
            ->setCellValue('K1', 'Invoice');

        $row_index = 2;
        foreach($rows as $row){
            $data_row_index = 1;
            $phpExcelObject->getActiveSheet()
                ->setCellValue('A'.$row_index, $row["created_at"])
                ->setCellValue('B'.$row_index, $row["purpose"])
                ->setCellValue('C'.$row_index, $row["status"])
                ->setCellValue('D'.$row_index, $row["client"])
                ->setCellValue('E'.$row_index, $row["equipment"])
                ->setCellValue('F'.$row_index, $row["wo_stock"])
                ->setCellValue('G'.$row_index, $row["to_stock"] ? $row["to_stock"] : 0)
                ->setCellValue('H'.$row_index, $row["to_invoice"] ? $row["to_invoice"] : 0)
                ->setCellValue('I'.$row_index, $row["invoiced"] ? $row["invoiced"] : 0)
                ->setCellValue('J'.$row_index++, $row["date"])
                ->setCellValue('K'.$row_index, $row["number"]);
        }

        $phpExcelObject->getActiveSheet()->getStyle('G2:I'.($row_index - 1))
            ->getNumberFormat()->setFormatCode('_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)');

        foreach(range('A','K') as $columnID) {
            $phpExcelObject->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

        $phpExcelObject->getActiveSheet()->setAutoFilter("A1:K".$row_index);

        // create the writer
        $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
        // create the response
        $response = $this->get('phpexcel')->createStreamedResponse($writer);
        // adding headers
        $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
        $response->headers->set('Content-Disposition', 'attachment;filename=stream-file.xls');
        $response->headers->set('Pragma', 'public');
        $response->headers->set('Cache-Control', 'maxage=1');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcelObject, "Excel5");
        $url = "http://airportliquidators.com/web/reports/report.xls";

        $objWriter->save(__DIR__.'/../../../../web/reports/report.xls');
        //exit;

        $response = array("code" => 100, "success" => true,
            "url" => $url, "message" => "Ok", "cant" => count($rows));

        return new Response(json_encode($response));
    }
    public function getInvoicesListAction($invoice_status = null,$invoice_destination = null,$invoice_number = null,
                                          $invoice_date_from = null,$invoice_date_to = null,$wo_stock = null,$client_name = null,
                                          $equipment = null){

        $em = $this->getDoctrine()->getEntityManager();

        $sql = "SELECT DATE_FORMAT(invoicereference.created_at,'%m/%d/%Y') AS created_at,saletype.sale_type AS purpose,"
            ."invoicereference.status AS status,client.name AS client,"
            ."CASE WHEN invoicereference.workorder_id IS NULL THEN st.code ELSE wo.code END AS equipment,"
            ."CASE WHEN invoicereference.workorder_id IS NULL THEN st.wo_stock ELSE wo.wo_stock END AS wo_stock,"
            ."FORMAT(CASE WHEN invoicereference.status = 'S' THEN invoicereference.amount ELSE 0 END,2) AS to_stock,"
            ."FORMAT(CASE WHEN invoicereference.status = 'PF' THEN invoicereference.amount ELSE 0 END,2) AS to_invoice,"
            ."FORMAT(CASE WHEN invoicereference.status = 'F' THEN invoicereference.amount ELSE 0 END,2) AS invoiced,"
            ."DATE_FORMAT(invoicereference.date,'%m/%d/%Y') AS date,invoicereference.number AS number"
            ." FROM invoicereference LEFT JOIN saletype ON invoicereference.purpose_id = saletype.id"
            ." LEFT JOIN (SELECT workorder.id AS id, workorder.number AS wo_stock, equipment.code AS code"
            ." FROM workorder LEFT JOIN equipment ON workorder.equipment_id = equipment.id) wo ON invoicereference.workorder_id = wo.id"
            ." LEFT JOIN (SELECT workorderstockassociation.id AS id, CONCAT(workorder.number,' - ',stock.code) AS wo_stock, equipment.code AS code"
            ." FROM workorderstockassociation LEFT JOIN workorder ON workorderstockassociation.workorder_id = workorder.id"
            ." LEFT JOIN stock ON workorderstockassociation.stock_id = stock.id"
            ." LEFT JOIN equipment ON stock.equipment_id = equipment.id) AS st ON invoicereference.wostock_id = st.id"
            ." LEFT JOIN client ON invoicereference.bill_to_id = client.id"
            ." WHERE (invoicereference.status = 'F' OR invoicereference.status = 'PF')";

        if($invoice_date_from != null){
            if($invoice_date_to != null){
                $sql = $sql." AND ((MONTH(invoicereference.date) >= ".$invoice_date_from[0]
                    ." AND YEAR(invoicereference.date) >= ".$invoice_date_from[1].")";
                $sql = $sql." AND (MONTH(invoicereference.date) <= '".$invoice_date_to[0]."'"
                    ." AND YEAR(invoicereference.date) <= '".$invoice_date_to[1]."'))";
            }
            else
                $sql = $sql." AND (MONTH(invoicereference.date) = '".$invoice_date_from[0]."'"
                    ." AND YEAR(invoicereference.date) = ".$invoice_date_from[1].")";
        };

        if($invoice_status > 0){
            switch ($invoice_status){
                case 1:
                    $sql = $sql." AND invoicereference.status = 'F'";
                    break;
                case 2:
                    $sql = $sql." AND invoicereference.status = 'PF'";
                    break;
                default:
                    $sql = $sql." AND invoicereference.status = 'S'";
                    break;
            }
            //$sql = $sql." AND invoicereference.status = '".($invoice_status == 1 ? "F" : "PF")."'";
        }

        if($invoice_number != null)
            $sql = $sql." AND invoicereference.number LIKE '%".$invoice_number."%'";
        if($wo_stock != null)
            $sql = $sql." AND wo.wo_stock LIKE '%".$wo_stock."%' OR wo.wo_stock LIKE '%".$wo_stock."%'";
        if($client_name != null)
            $sql = $sql." AND client.name LIKE '%".$client_name."%'";
        if($equipment != null)
            $sql = $sql." AND wo.code LIKE '%".$equipment."%' OR st.code LIKE '%".$equipment."%'";
        if($invoice_destination != null)
            $sql = $sql." AND invoicereference.purpose_id = '".$invoice_destination."'";

        $connection = $em->getConnection();
        $statement = $connection->prepare($sql);
        $statement->execute();
        return $statement->fetchAll();
    }
}