<?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 VGM\WOBundle\Entity\InvoiceReference;
use VGM\WOBundle\Entity\LaborLoop;
use VGM\WOBundle\Entity\PlanningTask;
use VGM\WOBundle\Entity\WorkOrder;
use VGM\WOBundle\Entity\Stock;
use VGM\WOBundle\Entity\WorkOrderObs;
use VGM\WOBundle\Entity\WorkOrderStockAssociation;

use VGM\WOBundle\Form\WorkOrderType;

/**
 * WorkOrder controller.
 *
 */
class WorkOrderController extends Controller
{
    /**
     * Showing Methods
     */

    /**
     * Lists all WorkOrder entities.
     *
     */
    public function currentWorkOrdersAction(Request $request){
        $client_type = $request->get('client_type');
        $em = $this->getDoctrine()->getEntityManager();

        //Con imagen
        /*$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,"
            ."client.name as client,DATE_FORMAT(workorder.delivery_date, '%m/%d/%y') as delivery,'/web/bundles/vgmwo/images/unknown-wo.png' as picture"
            ." 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"
            ." 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,saletype.sale_type as destiny,worktype.work_type as work,worker.name as supervisor,"
            ."as_stock.status as status,as_stock.client as client,DATE_FORMAT(workorder.delivery_date, '%m/%d/%y') as delivery,"
            ."CONCAT('http://www.aeroservicios.co/system/images/', as_stock.asdb_id, '_0.jpg') as picture"
            ." 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,stock.asdb_id as asdb_id,wsa.workorder_id as workorder_id"
            ." FROM workorderstockassociation as wsa LEFT JOIN stock ON stock.id = wsa.stock_id"
            ." LEFT JOIN client ON wsa.client_id = client.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";*/

        //Sin imagen
        $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 due_date, DATE_FORMAT(workorder.ready_at, '%m/%d/%y') as delivery_date"
            ." 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.due_date, '%m/%d/%y') as due_date ,DATE_FORMAT(as_stock.delivery_date, '%m/%d/%y') as delivery_date "
            ." 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.due_date as due_date, 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'";

        if($client_type == 1)
            $sql = $sql." AND client_type = '1'";
        elseif($client_type == 2)
            $sql = $sql." AND (client_type = '0' OR client_type IS NULL)";

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

        $output = array(
            "aaSorting" => [[2,'desc']],
            "iTotalRecords" => count($results),
            "iTotalDisplayRecords" => count($results),
            "aaData" => array()
        );
        $columns = array( 'wo_id', 'created', 'wo_number', 'stock','istatus', 'equipment', 'brand', 'model', 'destiny',
            'work', 'supervisor', 'status', 'client', 'due_date');

        if($this->getUser()->hasRole('ROLE_ACCOUNTING') || $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR') || $this->getUser()->hasRole('ROLE_ADMIN'))
            array_splice($columns, 5, 0, 'iamount');

        foreach($results as $arow){
            $row = array();
            foreach($columns as $column){
                $row[] = $arow[$column] != null ? $arow[$column] : "";
            }
            $output['aaData'][] = $row;
        }

        return new Response(json_encode($output));
    }
    public function unCurrentWorkOrdersAction(Request $request){
        $client_type = $request->get('client_type');
        $em = $this->getDoctrine()->getEntityManager();
        //Sin imagen
        $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 due_date,DATE_FORMAT(workorder.ready_at, '%m/%d/%y') as delivery_date"
            ." 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.due_date, '%m/%d/%y') as due_date,DATE_FORMAT(as_stock.delivery_date, '%m/%d/%y') as delivery_date "
            ." 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.due_date as due_date, 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'";

        /*"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,"
            ."client.name as client,DATE_FORMAT(workorder.ready_at, '%m/%d/%y') as delivery,client.internal as client_type"
            ." 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"
            ." 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.client as client,"
            ." as_stock.client_type 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 (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, stock.asdb_id as asdb_id,wsa.workorder_id as workorder_id,saletype.sale_type,worktype.work_type"
            ." FROM workorderstockassociation as wsa LEFT JOIN stock ON stock.id = wsa.stock_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'";*/

        if($client_type == 1)
            $sql = $sql." AND client_type = '1'";
        elseif($client_type == 2)
            $sql = $sql." AND (client_type = '0' OR client_type IS NULL)";

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

        $output = array(
            "aaSorting" => [[2,'desc']],
            "iTotalRecords" => count($results),
            "iTotalDisplayRecords" => count($results),
            "aaData" => array()
        );
        $columns = array( 'wo_id', 'created', 'wo_number', 'stock', 'istatus', 'equipment', 'brand', 'model', 'destiny',
            'work', 'supervisor', 'status', 'client', 'due_date', 'delivery_date');

        if($this->getUser()->hasRole('ROLE_ACCOUNTING') || $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR') || $this->getUser()->hasRole('ROLE_ADMIN'))
            array_splice($columns, 5, 0, 'iamount');

        foreach($results as $arow){
            $row = array();
            foreach($columns as $column){
                $row[] = $arow[$column] != null ? $arow[$column] : "";
            }
            $output['aaData'][] = $row;
        }

        return new Response(json_encode($output));
    }
    public function allStatusWorkOrdersAction(Request $request){
        $client_type = $request->get('client_type');
        $em = $this->getDoctrine()->getEntityManager();
        //Sin imagen
        $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 due_date,DATE_FORMAT(workorder.ready_at, '%m/%d/%y') as delivery_date"
            ." 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.due_date, '%m/%d/%y') as due_date,DATE_FORMAT(as_stock.delivery_date, '%m/%d/%y') as delivery_date "

            ." 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.due_date as due_date, 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'";

            /*"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,"
            ."client.name as client,DATE_FORMAT(workorder.ready_at, '%m/%d/%y') as delivery, client.internal as client_type"
            ." 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"
            ." 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.client as client,"
            ." as_stock.client_type 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 (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, stock.asdb_id as asdb_id,wsa.workorder_id as workorder_id,saletype.sale_type,worktype.work_type"
            ." FROM workorderstockassociation as wsa LEFT JOIN stock ON stock.id = wsa.stock_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'";*/

        if($client_type == 1)
            $sql = $sql." AND client_type = '1'";
        elseif($client_type == 2)
            $sql = $sql." AND (client_type = '0' OR client_type IS NULL)";

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

        $output = array(
            "aaSorting" => [[2,'desc']],
            "iTotalRecords" => count($results),
            "iTotalDisplayRecords" => count($results),
            "aaData" => array()
        );
        $columns = array( 'wo_id', 'created', 'wo_number', 'stock', 'istatus', 'equipment', 'brand', 'model', 'destiny',
            'work', 'supervisor', 'status', 'client', 'due_date', 'delivery_date');

        if($this->getUser()->hasRole('ROLE_ACCOUNTING') || $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR') || $this->getUser()->hasRole('ROLE_ADMIN'))
            array_splice($columns, 5, 0, 'iamount');

        foreach($results as $arow){
            $row = array();
            foreach($columns as $column){
                $row[] = $arow[$column] != null ? $arow[$column] : "";
            }
            $output['aaData'][] = $row;
        }

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

    public function indexAction($page)
    {
        return $this->render('VGMWOBundle:WorkOrder:index.html.twig');
    }
    public function archivedIndexAction($page)
    {
        return $this->render('VGMWOBundle:WorkOrder:archived_index.html.twig');
    }
    public function consultingIndexAction($page)
    {
        return $this->render('VGMWOBundle:WorkOrder:consulting_index.html.twig');
    }

    /**
     * Finds and displays a WorkOrder entity.
     *
     */
    public function showAction($id)
    {
        $em = $this->getDoctrine()->getManager();

        $entity = $em->getRepository('VGMWOBundle:WorkOrder')->find($id);

        if ($entity) {
            $equipments = $em->getRepository('VGMWOBundle:Equipment')->findBy(array(), array('code' => 'ASC'));
            $units = $em->getRepository('VGMWOBundle:CUnit')->findBy(array(), array('name' => 'ASC'));
            $clients = $em->getRepository('VGMWOBundle:Client')->findBy(array(), array('name' => 'ASC'));
            $salest = $em->getRepository('VGMWOBundle:SaleType')->findBy(array(), array('sale_type' => 'ASC'));
            $workst = $em->getRepository('VGMWOBundle:WorkType')->findBy(array(), array('work_type' => 'ASC'));
            $departments = $em->getRepository('VGMWOBundle:NDepartment')->findBy(array(), array('name' => 'ASC'));
            $supervisors = $em->getRepository('VGMWOBundle:Worker')->getSupervisors();
            $cost = $this->getWOCost($entity);

            $first_code = $this->container->getParameter('first_workorder');
            $fc = $this->getDoctrine()->getManager()->getRepository('VGMWOBundle:WorkOrder')->nextWorkOrderCode($first_code);

            $url = $this->getUser()->hasRole('ROLE_ACCOUNTING') && !($this->getUser()->hasRole('ROLE_SUPERVISOR') || $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR'))
                ? 'VGMWOBundle:WorkOrder:show_accounting.html.twig'
                : 'VGMWOBundle:WorkOrder:show.html.twig';

            return $this->render($url, array(
                    'entity' => $entity,
                    'cost' => $cost,
                    'next_wo_number' => $fc,
                    'supervisors' => $supervisors,
                    'units' => $units,
                    'equipments' => $equipments,
                    'departments' => $departments,
                    'clients' => $clients,
                    'salest' => $salest,
                    'workst' => $workst
                ));
        }
        else
            throw $this->createNotFoundException('Unable to find WorkOrder entity.');
    }
    public function showArchivedAction($id)
    {
        $em = $this->getDoctrine()->getManager();

        $entity = $em->getRepository('VGMWOBundle:WorkOrder')->find($id);

        if ($entity) {
            $equipments = $em->getRepository('VGMWOBundle:Equipment')->getAll();
            $units = $em->getRepository('VGMWOBundle:CUnit')->findBy(array(), array('name' => 'ASC'));
            $clients = $em->getRepository('VGMWOBundle:Client')->findBy(array(), array('name' => 'ASC'));
            $salest = $em->getRepository('VGMWOBundle:SaleType')->findBy(array(), array('sale_type' => 'ASC'));
            $workst = $em->getRepository('VGMWOBundle:WorkType')->findBy(array(), array('work_type' => 'ASC'));
            $departments = $em->getRepository('VGMWOBundle:NDepartment')->findBy(array(), array('name' => 'ASC'));
            $supervisors = $em->getRepository('VGMWOBundle:Worker')->getSupervisors();
            $cost = $this->getWOCost($entity);

            $first_code = $this->container->getParameter('first_workorder');
            $fc = $this->getDoctrine()->getManager()->getRepository('VGMWOBundle:WorkOrder')->nextWorkOrderCode($first_code);

            return $this->render('VGMWOBundle:WorkOrder:archived_show.html.twig', array(
                    'entity'      => $entity,
                    'cost' => $cost,
                    'next_wo_number' => $fc,
                    'supervisors' => $supervisors,
                    'units' => $units,
                    'equipments' => $equipments,
                    'departments' => $departments,
                    'clients' => $clients,
                    'salest' => $salest,
                    'workst' => $workst
                ));
        }
        else
            throw $this->createNotFoundException('Unable to find WorkOrder entity.');
    }
    public function showPrintPreviewAction($id)
    {
        $em = $this->getDoctrine()->getManager();

        $entity = $em->getRepository('VGMWOBundle:WorkOrder')->find($id);

        if ($entity) {
            $cost = $this->getWOCost($entity);
            $invoices = $em->getRepository('VGMWOBundle:InvoiceReference')->getExistInvoices4WO($id);


            return $this->render('VGMWOBundle:WorkOrder:print_preview.html.twig', array(
                'entity'      => $entity,
                'cost' => $cost,
                'has_invoices' => $invoices == 0 ? false : true
            ));
        }
        else
            throw $this->createNotFoundException('Unable to find WorkOrder entity.');
    }

    /**
     * Creation Methods
     */
    /**
     * Displays a form to create a new WorkOrder entity.
     *
     */
    public function newAction(Request $request)
    {
        $em = $this->getDoctrine()->getManager();
        $equipments = $em->getRepository('VGMWOBundle:Equipment')->findBy(array(), array('code' => 'ASC'));
        $units = $em->getRepository('VGMWOBundle:CUnit')->findBy(array(), array('name' => 'ASC'));
        $clients = $em->getRepository('VGMWOBundle:Client')->findBy(array(), array('name' => 'ASC'));
        $salest = $em->getRepository('VGMWOBundle:SaleType')->findBy(array(), array('sale_type' => 'ASC'));
        $workst = $em->getRepository('VGMWOBundle:WorkType')->findBy(array(), array('work_type' => 'ASC'));
        $departments = $em->getRepository('VGMWOBundle:NDepartment')->findBy(array(), array('name' => 'ASC'));
        $supervisors = $em->getRepository('VGMWOBundle:Worker')->getSupervisors();

        $first_code = $this->container->getParameter('first_workorder');
        $fc = $this->getDoctrine()->getManager()->getRepository('VGMWOBundle:WorkOrder')->nextWorkOrderCode($first_code);

        return $this->render('VGMWOBundle:WorkOrder:new.html.twig', array(
                'equipments' => $equipments,
                'units' => $units,
                'supervisors' => $supervisors,
                'departments' => $departments,
                'clients' => $clients,
                'salest' => $salest,
                'workst' => $workst,
                'wonumber' => $fc
            ));
    }

    /**
     * Deleting Methods
     */
    /**
     * Deletes a WorkOrder entity.
     *
     */
    public function deleteAction(Request $request, $id)
    {
        $form = $this->createDeleteForm($id);
        $form->handleRequest($request);

        if ($form->isValid()) {
            $em = $this->getDoctrine()->getManager();
            $entity = $em->getRepository('VGMWOBundle:WorkOrder')->find($id);

            if (!$entity) {
                throw $this->createNotFoundException('Unable to find WorkOrder entity.');
            }

            $em->remove($entity);
            $em->flush();
        }

        return $this->redirect($this->generateUrl('workorders'));
    }
    /**
     * Creates a form to delete a WorkOrder entity by id.
     *
     * @param mixed $id The entity id
     *
     * @return \Symfony\Component\Form\Form The form
     */
    private function createDeleteForm($id)
    {
        return $this->createFormBuilder()
            ->setAction($this->generateUrl('workorders_delete', array('id' => $id)))
            ->setMethod('DELETE')
            ->add('submit', 'submit', array('label' => 'Delete'))
            ->getForm()
        ;
    }

    /**
     * Ajax functions
     */
    //Proof ajax function
    public function proofAction(Request $request)
    {
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         * initializing others vars
         */
        $stockNo = $request->get('stock');
        $selectedeq = $request->get('selecteq');

        $result = [];
        $message = 0;
        $eoid = null;

        /**
         * Control vars
         */

        /**
         * Aeroservices DB
         */
        $ext_em = $this->getDoctrine()->getManager('aeroservicios');
        $conn = $ext_em->getConnection();

        /**
         * Stock Query
         * Getting stocks item own data
         */
        $stock_query = "SELECT count(Items.Id) as count ";
        $stock_query = $stock_query."FROM Items ";
        $query = $conn->prepare($stock_query);

        $query->execute();
        $stock_result = $query->fetchAll();

        $message = $stock_result[0]['count'];

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

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

    /**
     * @param Request $request
     * @return Response
     */
    public function getFilteredWorkOrdersAjaxAction(Request $request){
        //$this->Arreglo();
        //$this->excelproof();

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

        $first_date = new \DateTime($this->container->getParameter('first_cycle_first_day'));
        $date = new \DateTime();
        $date_from = clone $date;
        $date_from->modify('-21 day');

        $this->get('utils')->closeAllPastWeeks(($date_from > $first_date ? $date_from : $first_date), $date);

        /**
         * Getting data request
         */
        $request_filters = json_decode($request->get('filters'), true);
        $request_sorters = json_decode($request->get('sorters'), true);
        $page = $request->get('page');

        $filters = [];
        $filters['filter_created'] = $request_filters[0]['filter_created'];
        if($filters['filter_created']){
            $filters['filter_created']['sdate'] = $filters['filter_created']['sdate'] ?
                new \DateTime($filters['filter_created']['sdate']) : null;
            $filters['filter_created']['edate'] = $filters['filter_created']['edate'] ?
                new \DateTime($filters['filter_created']['edate']) : null;
        }

        $filters['filter_started'] = $request_filters[0]['filter_started'];
        if($filters['filter_started']){
            $filters['filter_started']['sdate'] = $filters['filter_started']['sdate'] ?
                new \DateTime($filters['filter_started']['sdate']) : null;
            $filters['filter_started']['edate'] = $filters['filter_started']['edate'] ?
                new \DateTime($filters['filter_started']['edate']) : null;
        }

        $filters['filter_wo'] = $request_filters[0]['filter_wo'];
        $filters['filter_stock'] = $request_filters[0]['filter_stock'] ? $request_filters[0]['filter_stock'] : null;
        $filters['filter_sup'] = $request_filters[0]['filter_sup'];
        $filters['filter_eqt'] = $request_filters[0]['filter_eqt'];
        $filters['filter_wt'] = $request_filters[0]['filter_wt'];
        $filters['filter_clt'] = $request_filters[0]['filter_clt'];

        $sorters = [];
        $sorters['sorter_created'] = $request_sorters[0]['sorter_created'];
        $sorters['sorter_started'] = $request_sorters[0]['sorter_started'];
        $sorters['sorter_wo'] = $request_sorters[0]['sorter_wo'];
        //$sorters['sorter_stock'] = $request_sorters[0]['sorter_stock'];
        $sorters['sorter_sup'] = $request_sorters[0]['sorter_sup'];
        $sorters['sorter_eqt'] = $request_sorters[0]['sorter_eqt'];
        $sorters['sorter_wt'] = $request_sorters[0]['sorter_wt'];
        $sorters['sorter_clt'] = $request_sorters[0]['sorter_clt'];

        //$m = $sorters['sorter_sup'];//$em->getRepository('VGMWOBundle:WorkOrder')->getFilteredWorkOrders($filters, $sorters);

        $work_orders = [];

        $cant_workorders = $em->getRepository('VGMWOBundle:WorkOrder')->countFilteredWorkOrders($filters, $sorters);
        $workorders_per_page = $this->container->getParameter('workorders_per_page');
        $last_page = ceil($cant_workorders / $workorders_per_page);
        $previous_page = $page > 1 ? $page - 1 : 1;
        $next_page = $page < $last_page ? $page + 1 : $last_page;

        $offset = $page > 1 ? (($page - 1) * $workorders_per_page) : 0;

        $work_orders = $em->getRepository('VGMWOBundle:WorkOrder')
            ->getFilteredWorkOrders($filters, $sorters);

        $workorders_list = [];
        $loop_end = ($offset + $workorders_per_page) < $cant_workorders ?
            ($offset + $workorders_per_page) : $cant_workorders;
        if(count($work_orders) > 0){
            /*for($i = $offset; $i < $loop_end; $i++)*/
            foreach($work_orders as $wo){
                /*$wo = $work_orders[$i];*/
                if($wo){
                    //Stocks
                    $my_stocks = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
                        ->getAssociationByWoNumber($wo->getNumber());
                    $stocks = [];
                    foreach($my_stocks as $stock){
                        $stocks[] = array("own_id" => $stock->getStock()->getId(),
                            "asdb_id" => $stock->getStock()->getAsdbId(),
                            "number" => $stock->getAsNumber(),
                            "equipment_id" => $stock->getStock()->getEquipment() ?
                                    $stock->getStock()->getEquipment()->getId() : null,
                            "status" => $stock->getStatus() ? $stock->getStatus() : "Pending",
                            "client_id" => $stock->getClient() ? $stock->getClient()->getId() : null,
                            "image" => "http://www.aeroservicios.co/system/images/".$stock->getStock()->getAsdbId()."_0.jpg",
                            "stock" => $stock->getStock()->getCode(),
                            "serial" => $stock->getStock()->getSerial(),
                            "hours_miles" => $stock->getStock()->getHoursMiles(),
                            "make" => $stock->getStock()->getBrand(),
                            "model" => $stock->getStock()->getModel(),
                            "capacity" => $stock->getStock()->getCapacity(),
                            "tgc" => $stock->getStock()->getTransGenCom(),
                            "engine" => $stock->getStock()->getEngine(),
                            "year" => $stock->getStock()->getYear(),
                            "localization" => $stock->getStock()->getLocalization(),
                            "a_condition" => $stock->getStock()->getActualCondition(),
                            "description" => $stock->getStock()->getDescription());
                    }

                    //Work Order
                    $workorders_list[] = array("id" => $wo->getId(),
                        "picture" => $wo->getImageUrl(),
                        "wo_number" => $wo->getNumber(),
                        "equipment_id" => $wo->getEquipment() ? $wo->getEquipment()->getId() : null,
                        "stocks_qty" => $wo->getStockQuantity(),
                        "created" => $wo->getCreatedAt() ? $wo->getCreatedAt()->format('m/d/y'): null,
                        "started" => $wo->getStartedAt() ? $wo->getStartedAt()->format('m/d/y'): null,
                        "price" => $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR') ? $wo->getPrice() : 0,
                        "supervisor_id" => $wo->getSupervisor() ? $wo->getSupervisor()->getId() : null,
                        "client_id" => $wo->getClient() ? $wo->getClient()->getId() : null,
                        "delivery_date" => $wo->getDeliveryDate() ?
                                $wo->getDeliveryDate()->format('m/d/Y') : null,
                        "sale_type_id" => $wo->getSaleType() ? $wo->getSaleType()->getId() : null,
                        "work_type_id" => $wo->getWorktype() ? $wo->getWorktype()->getId() : null,
                        "stocks" => $stocks,
                        "status" => $wo->getStatus() ? $wo->getStatus() : "Pending",
                        "obs" => $wo->getObs(),
                        "cost" => $this->getWOCost($wo));
                }
            }
        }

        $response = array("code" => 100, "success" => true,
            "workorders" => $workorders_list,
            "cant_wos" => $cant_workorders,
            "last_page" => $last_page,
            "previous_page" => $previous_page,
            "next_page" => $next_page,
            "message" => $offset);

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function getFilteredWorkOrdersStocksAjaxAction(Request $request){
        //$this->Arreglo();
        //$this->excelproof();

        $em = $this->getDoctrine()->getManager();
        /**
         * Getting data request
         */
        $request_filters = json_decode($request->get('filters'), true);
        $request_sorters = json_decode($request->get('sorters'), true);
        $page = $request->get('page');

        $filters = [];
        $filters['filter_created'] = $request_filters[0]['filter_created'];
        if($filters['filter_created']){
            $filters['filter_created']['sdate'] = $filters['filter_created']['sdate'] ?
                new \DateTime($filters['filter_created']['sdate']) : null;
            $filters['filter_created']['edate'] = $filters['filter_created']['edate'] ?
                new \DateTime($filters['filter_created']['edate']) : null;
        }

        $filters['filter_started'] = $request_filters[0]['filter_started'];
        if($filters['filter_started']){
            $filters['filter_started']['sdate'] = $filters['filter_started']['sdate'] ?
                new \DateTime($filters['filter_started']['sdate']) : null;
            $filters['filter_started']['edate'] = $filters['filter_started']['edate'] ?
                new \DateTime($filters['filter_started']['edate']) : null;
        }

        $filters['filter_wo'] = $request_filters[0]['filter_wo'];
        $filters['filter_stock'] = $request_filters[0]['filter_stock'] ? $request_filters[0]['filter_stock'] : null;
        $filters['filter_sup'] = $request_filters[0]['filter_sup'];
        $filters['filter_eqt'] = $request_filters[0]['filter_eqt'];
        $filters['filter_wt'] = $request_filters[0]['filter_wt'];
        $filters['filter_clt'] = $request_filters[0]['filter_clt'];
        $filters['filter_make'] = $request_filters[0]['filter_make'] ? $request_filters[0]['filter_make'] : null;
        $filters['filter_model'] = $request_filters[0]['filter_model'] ? $request_filters[0]['filter_model'] : null;

        $sorters = [];
        $sorters['sorter_created'] = $request_sorters[0]['sorter_created'];
        $sorters['sorter_started'] = $request_sorters[0]['sorter_started'];
        $sorters['sorter_wo'] = $request_sorters[0]['sorter_wo'];
        //$sorters['sorter_stock'] = $request_sorters[0]['sorter_stock'];
        $sorters['sorter_sup'] = $request_sorters[0]['sorter_sup'];
        $sorters['sorter_eqt'] = $request_sorters[0]['sorter_eqt'];
        $sorters['sorter_wt'] = $request_sorters[0]['sorter_wt'];
        $sorters['sorter_clt'] = $request_sorters[0]['sorter_clt'];
        $sorters['sorter_make'] = $request_sorters[0]['sorter_make'];
        $sorters['sorter_model'] = $request_sorters[0]['sorter_model'];

        $workorders_stocks = [];

        /*$cant_workorders_stocks = $em->getRepository('VGMWOBundle:WorkOrder')->countFilteredAssociations($filters, $sorters);
        $workorders_stocks_per_page = $this->container->getParameter('workorders_per_page');
        $last_page = ceil($cant_workorders_stocks / $workorders_stocks_per_page);
        $previous_page = $page > 1 ? $page - 1 : 1;
        $next_page = $page < $last_page ? $page + 1 : $last_page;

        $offset = $page > 1 ? (($page - 1) * $workorders_stocks_per_page) : 0;*/

        $workorders_stocks = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
            ->getFilteredAssociations($filters, $sorters);

        $workorders_stocks_list = [];/*
        $loop_end = ($offset + $workorders_stocks_per_page) < $cant_workorders_stocks ?
            ($offset + $workorders_stocks_per_page) : $cant_workorders_stocks;*/
        if(count($workorders_stocks) > 0){
            foreach($workorders_stocks as $association){
                if($association){
                    $workorders_stocks_list[] = array("own_id" => $association->getId(),
                        "picture" => $association->getStock() ? $association->getStock()->getImgURL(0) :
                                        $association->getWorkOrder()->getImageUrl(),
                        "wo_id" => $association->getWorkOrder()->getId(),
                        "wo_number" => $association->getWorkOrder()->getNumber(),
                        "wo_created" => $association->getWorkOrder()->getCreatedAt() ?
                                $association->getWorkOrder()->getCreatedAt()->format('m/d/Y'): null,
                        "wo_supervisor_id" => $association->getWorkOrder()->getSupervisor() ?
                                $association->getWorkOrder()->getSupervisor()->getId() : null,
                        "wo_t_sale_id" => $association->getWorkOrder()->getSaleType() ?
                                $association->getWorkOrder()->getSaleType()->getId() : null,
                        "wo_t_work_id" => $association->getWorkOrder()->getWorktype() ?
                                $association->getWorkOrder()->getWorktype()->getId() : null,
                        "wo_status" => $association->getWorkOrder()->getStatus(),

                        "stock_id" => $association->getStock()->getId(),
                        "stock_asdb_id" => $association->getStock()->getAsdbId(),
                        "stock_number" => $association->getStock()->getCode(),
                        "stock_equipment_id" => $association->getStock()->getEquipment() ?
                                $association->getStock()->getEquipment()->getId() : null,
                        "stock_serial" => $association->getStock()->getSerial(),
                        "stock_brand" => $association->getStock()->getBrand(),
                        "stock_model" => $association->getStock()->getModel(),

                        "stock_status" => $association->getStatus(),
                        "stock_as_number" => $association->getAsNumber(),

                        "stock_client_id" => $association->getClient() ? $association->getClient()->getId() : null);
                }
            }
        }

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

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function createWorkOrderAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         * initializing others vars
         */
        $wo_number = $request->get('wo_number');
        $origin = $request->get('origin');
        $supervisor = $request->get('supervisor') ?
            $em->getRepository('VGMWOBundle:Worker')->find($request->get('supervisor')) : null;
        $equipment = $request->get('equipment') ?
            $em->getRepository('VGMWOBundle:Equipment')->find($request->get('equipment')) : null;
        $due_date = $request->get('due_date') ? new \DateTime($request->get('due_date')) : null;
        $deliverydt = $request->get('deliverydt') ? new \DateTime($request->get('deliverydt')) : null;
        $t_sale = $request->get('t_sale') ?
            $em->getRepository('VGMWOBundle:SaleType')->find($request->get('t_sale')) : null;
        $t_work = $request->get('t_work') ?
            $em->getRepository('VGMWOBundle:WorkType')->find($request->get('t_work')) : null;

        $status = $request->get('status') && $request->get('status') != "" ? $request->get('status') : "Current";

        $client = $request->get('client') ?
            $em->getRepository('VGMWOBundle:Client')->find($request->get('client')) : null;
        $stocks_qty = $request->get('stocks_qty');
        $obs = $request->get('obs');

        $price = $request->get('price') != null ? $request->get('price') : null;
        $x_price = $request->get('x_price');

        $obs4save = json_decode($request->get('obs4save'), true);
        $stocks4save = json_decode($request->get('stocks4save'), true);
        $invoices4save = json_decode($request->get('invoices4save'), true);
        $tasks4save = json_decode($request->get('tasks4save'), true);

        $workorder = new WorkOrder();

        $workorder->setNumber($wo_number);
        $workorder->setOrigin($origin);

        if($x_price != null)
            $workorder->setXPrice($x_price);

        if($this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR'))
            $workorder->setPrice($price);

        if($obs != null && trim($obs) != ""){
            $date = new \DateTime();
            $new_obs = new WorkOrderObs();
            $new_obs->setObs("[".$date->format("m/d/Y")." - ".$this->getUser()->getWorker()->__toString()."]: ".$obs);

            $workorder->addOb($new_obs);
            $new_obs->setWorkorder($workorder);
        }

        $workorder->setCreatedBy($this->getUser()->getWorker());

        if($supervisor != null)$workorder->setSupervisor($supervisor);
        if($equipment != null)$workorder->setEquipment($equipment);
        if($client != null)$workorder->setClient($client);
        $workorder->setReadyAt($due_date);
        $workorder->setDeliveryDate($deliverydt);
        if($t_sale != null)$workorder->setSaletype($t_sale);
        if($t_work != null)$workorder->setWorktype($t_work);
        $workorder->setStockQuantity($stocks_qty);
        if($x_price)
            $workorder->setXPrice($x_price);

        $em->persist($workorder);
        $em->flush();
        /*
         * JStocks keys
         * own_id, asdb_id, stock, serial, *number, hours_miles, model, cap, year,
         * cond, brand, tgc, engine, loc, acond, actual_workorder, *status, *client
         * */
        foreach($obs4save as $jobs){
            if(trim($jobs['obs']) != ""){
                $date = new \DateTime();
                $new_obs = new WorkOrderObs();
                $new_obs->setObs("[".$date->format("m/d/Y")." - ".$this->getUser()->getWorker()->__toString()."]: ".$jobs['obs']);

                $workorder->addOb($new_obs);
                $new_obs->setWorkorder($workorder);

                $em->persist($new_obs);
                $em->flush();
            }
        }

        foreach($stocks4save as $jstock){
            $stock = null;
            if($jstock["own_id"])
                $stock = $em->getRepository('VGMWOBundle:Stock')->find($jstock["own_id"]);
            if($stock == null){
                $stock = new Stock();
                $em->persist($stock);
            }

            $stock->setCode($jstock['stock']);
            $stock->setSerial($jstock['serial']);
            $stock->setBrand($jstock['brand']);
            $stock->setModel($jstock['model']);
            $stock->setYear($jstock['year']);
            $stock->setCapacity($jstock['cap']);
            $stock->setEngine($jstock['engine']);
            $stock->setTransGenCom($jstock['tgc']);
            $stock->setHoursMiles($jstock['hours_miles']);
            $stock->setLocalization($jstock['loc']);
            $stock->setDescription($jstock['cond']);
            $stock->setActualCondition($jstock['acond']);
            $stock->setEquipment($equipment);
            $stock->setWo($jstock['actual_workorder']);

            $association = new WorkOrderStockAssociation();
            $association->setStock($stock);
            $association->setWorkorder($workorder);

            if($jstock['client'] != null)
                $association->setClient($em->getRepository('VGMWOBundle:Client')->find($jstock['client']));

            if($jstock['t_sale'] != null)
                $association->setSaletype($em->getRepository('VGMWOBundle:SaleType')->find($jstock['t_sale']));

            if($jstock['t_work'] != null)
                $association->setWorktype($em->getRepository('VGMWOBundle:WorkType')->find($jstock['t_work']));

            $association->setAsNumber($jstock['number']);

            $association->setStatus($jstock['status'] != null && $jstock['status'] != "" ? $jstock['status']
                                    : $status);
            $association->setDeliveryDate($jstock['delivery_date'] != null && $jstock['delivery_date'] != "" ?
                new \DateTime($jstock['delivery_date']) : null);
            $association->setOrigin($jstock['from']);

            $workorder->addStocksFull($association);
            $stock->addWorkordersFull($association);
            if($client)
                $client->addStocksWorkordersFull($association);

            $em->persist($association);
            $em->flush();

            if(count($jstock["invoices4save"]) > 0){
                foreach($jstock["invoices4save"] as $jinvoice){
                    $invoice = null;
                    if($jinvoice["id"])
                        $invoice = $em->getRepository('VGMWOBundle:InvoiceReference')->find($jinvoice["id"]);
                    if($invoice == null){
                        $invoice = new InvoiceReference();
                        $em->persist($invoice);
                    }
                    $invoice->setOrderNumber($jinvoice['order']);
                    $invoice->setStatus($jinvoice['status']);
                    $invoice->setAmount($jinvoice['amount']);
                    $invoice->setNumber($jinvoice['invoice']);
                    $invoice->setItems($jinvoice['items']);
                    $invoice->setDate($jinvoice['date'] != null && $jinvoice['date'] != "" ? new \DateTime($jinvoice['date']) : null);

                    if($jinvoice['client_id'] != null)
                        $invoice->setBillTo($em->getRepository('VGMWOBundle:Client')->find($jinvoice['client_id']));

                    if($jinvoice['destination_id'] != null)
                        $invoice->setPurpose($em->getRepository('VGMWOBundle:SaleType')->find($jinvoice['destination_id']));

                    $invoice->setWostock($association);

                    $em->flush($invoice);
                }
            }
        }

        foreach($invoices4save as $jinvoice){
            $invoice = null;
            if($jinvoice["id"])
                $invoice = $em->getRepository('VGMWOBundle:InvoiceReference')->find($invoice["id"]);
            if($invoice == null){
                $invoice = new InvoiceReference();
                $em->persist($invoice);
            }
            $invoice->setOrderNumber($jinvoice['order']);
            $invoice->setStatus($jinvoice['status']);
            $invoice->setAmount($jinvoice['amount']);
            $invoice->setNumber($jinvoice['invoice']);
            $invoice->setItems($jinvoice['items']);
            $invoice->setDate($jinvoice['date']);

            if($jinvoice['client_id'] != null)
                $invoice->setBillTo($em->getRepository('VGMWOBundle:Client')->find($jinvoice['client_id']));

            if($jinvoice['destination_id'] != null)
                $invoice->setPurpose($em->getRepository('VGMWOBundle:SaleType')->find($jinvoice['destination_id']));

            $invoice->setWorkorder($workorder);
            $em->flush();
        }

        $workorder->setStatus($status);
        $workorder->setStatusFromStocks();
        $workorder->setReadyAtFromStocks();
        $em->flush($workorder);

        foreach($tasks4save as $jstask){
            $task = new PlanningTask();
            $em->persist($task);

            $task->setArea($jstask['area']);
            $task->setDescription($jstask['description']);

            $task_unit = $em->getRepository('VGMWOBundle:CUnit')->find($jstask["unit_id"]);
            $task->setUnit($task_unit);

            $task_sup = $em->getRepository('VGMWOBundle:Worker')->find($jstask["supervisor_id"]);
            $task->setSupervisor($task_sup);

            $task->setStatus($jstask['status']);
            $task->setStartDate($jstask['start_date'] ? new \DateTime($jstask['start_date']) : null);
            $task->setEtime($jstask['eTime']);
            $task->setDeathLine($jstask['death_line'] ? new \DateTime($jstask['death_line']) : null);
            $task->setObs($jstask['obs']);
            $task->setCompletedAt($jstask['ended']);
            $task->setRtime($jstask['rTime']);
            $task->setCreatedAt(new \DateTime());
            $task->setCreatedBy($this->getUser()->getWorker());
            $task->setUpdatedAt(new \DateTime());
            $task->setUpdatedBy($this->getUser()->getWorker()->__toString());

            $task->addWorkorder($workorder);
            $workorder->addTask($task);

            $em->flush();
        }

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

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function updateWorkOrderAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         * initializing others vars
         */
        $own_id = $request->get('own_id');
        $wo_number = $request->get('wo_number');
        $origin = $request->get('origin');
        $due_date = $request->get('due_date') ? new \DateTime($request->get('due_date')) : null;
        $deliverydt = $request->get('deliverydt') ? new \DateTime($request->get('deliverydt')) : null;
        $status = $request->get('status') != null && $request->get('status') != "" ? $request->get('status') : "Current";
        $stocks_qty = $request->get('stocks_qty');
        $price = $request->get('price') != null ? $request->get('price') : null;
        $obs = $request->get('obs');
        $x_price = $request->get('x_price');

        $supervisor = $request->get('supervisor') ?
            $em->getRepository('VGMWOBundle:Worker')->find($request->get('supervisor')) : null;
        $equipment = $request->get('equipment') ?
            $em->getRepository('VGMWOBundle:Equipment')->find($request->get('equipment')) : null;
        $t_sale = $request->get('t_sale') ?
            $em->getRepository('VGMWOBundle:SaleType')->find($request->get('t_sale')) : null;
        $t_work = $request->get('t_work') ?
            $em->getRepository('VGMWOBundle:WorkType')->find($request->get('t_work')) : null;
        $client = $request->get('client') ?
            $em->getRepository('VGMWOBundle:Client')->find($request->get('client')) : null;

        $obs4save = json_decode($request->get('obs4save'), true);
        $obs4delete = json_decode($request->get('obs4delete'), true);

        $stocks4save = json_decode($request->get('stocks4save'), true);
        $stocks4delete = json_decode($request->get('stocks4delete'), true);

        $invoices4save = json_decode($request->get('invoices4save'), true);
        $invoices4delete = json_decode($request->get('invoices4delete'), true);

        $tasks4save = json_decode($request->get('tasks4save'), true);
        $tasks4delete = json_decode($request->get('tasks4delete'), true);

        $workorder = $em->getRepository('VGMWOBundle:WorkOrder')->find($own_id);

        $workorder->setNumber($wo_number);
        $workorder->setOrigin($origin);

        if($x_price != null)
            $workorder->setXPrice($x_price);

        if($this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR'))
            $workorder->setPrice($price);

        if($obs != null && trim($obs) != ""){
            $date = new \DateTime();
            $new_obs = new WorkOrderObs();
            $new_obs->setObs("[".$date->format("m/d/Y")." - ".$this->getUser()->getWorker()->__toString()."]: ".$obs);

            $workorder->addOb($new_obs);
            $new_obs->setWorkorder($workorder);
        }

        if($supervisor != null)$workorder->setSupervisor($supervisor);
        if($equipment != null)$workorder->setEquipment($equipment);
        if($client != null)$workorder->setClient($client);
        $workorder->setReadyAt($due_date);
        $workorder->setDeliveryDate($deliverydt);
        if($t_sale != null)$workorder->setSaletype($t_sale);
        if($t_work != null)$workorder->setWorktype($t_work);
        $workorder->setStockQuantity($stocks_qty);
        $em->flush();

        foreach($obs4save as $jobs){
            if(trim($jobs['obs']) != ""){
                $date = new \DateTime();
                $new_obs = new WorkOrderObs();
                $new_obs->setObs("[".$date->format("m/d/Y")." - ".$this->getUser()->getWorker()->__toString()."]: ".$jobs['obs']);

                $workorder->addOb($new_obs);
                $new_obs->setWorkorder($workorder);

                $em->persist($new_obs);
                $em->flush();
            }
        }

        if(count($obs4delete) > 0){
            foreach($obs4delete as $jdobs){
                $dobs = null;
                if($jdobs["id"])
                    $dobs = $em->getRepository('VGMWOBundle:WorkOrderObs')->find($jdobs["id"]);
                if($dobs != null){
                    $em->remove($dobs);
                    $em->flush();
                }
            }
        }

        foreach($stocks4delete as $jdstock){
            $dstock = null;
            if($jdstock["own_id"])
                $dstock = $em->getRepository('VGMWOBundle:Stock')->find($jdstock["own_id"]);
            if($dstock != null && $workorder != null){
                $as = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
                        ->getAssociationByWoStock($own_id, $jdstock["own_id"]);
                if($as != null){
                    $em->remove($as);
                    $em->flush();
                }
            }
        }

        foreach($stocks4save as $jstock){
            $stock = null;
            if($jstock["own_id"])
                $stock = $em->getRepository('VGMWOBundle:Stock')->find($jstock["own_id"]);
            if($stock == null){
                $stock = new Stock();
                $em->persist($stock);
            }

            $stock->setCode($jstock['stock']);
            $stock->setSerial($jstock['serial']);
            $stock->setBrand($jstock['brand']);
            $stock->setModel($jstock['model']);
            $stock->setYear($jstock['year']);
            $stock->setCapacity($jstock['cap']);
            $stock->setEngine($jstock['engine']);
            $stock->setTransGenCom($jstock['tgc']);
            $stock->setHoursMiles($jstock['hours_miles']);
            $stock->setLocalization($jstock['loc']);
            $stock->setDescription($jstock['cond']);
            $stock->setActualCondition($jstock['acond']);
            $stock->setEquipment($equipment);
            $stock->setWo($jstock['actual_workorder']);

            $association = null;
            if($jstock["own_id"])
                $association = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
                    ->getAssociationByWoStock($own_id, $jstock["own_id"]);
            else{
                $association = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
                    ->getAssociationByWoAsNumber($own_id, $jstock["number"]);
                if($association)
                    $association->setStock($stock);
            }

            if($association == null){
                $association = new WorkOrderStockAssociation();
                $association->setStock($stock);
                $association->setWorkorder($workorder);

                $em->persist($association);

                $workorder->addStocksFull($association);
                $stock->addWorkordersFull($association);
            }

            if($jstock['client'] != null)
                $association->setClient($em->getRepository('VGMWOBundle:Client')->find($jstock['client']));

            if($jstock['t_sale'] != null)
                $association->setSaletype($em->getRepository('VGMWOBundle:SaleType')->find($jstock['t_sale']));

            if($jstock['t_work'] != null)
                $association->setWorktype($em->getRepository('VGMWOBundle:WorkType')->find($jstock['t_work']));

            $association->setAsNumber($jstock['number']);
            $association->setStatus($jstock['status'] != null && $jstock['status'] != "" ? $jstock['status']
                : $status);
            $association->setDueDate($jstock['due_date'] != null && $jstock['due_date'] != "" ?
                new \DateTime($jstock['due_date']) : null);
            $association->setDeliveryDate($jstock['delivery_date'] != null && $jstock['delivery_date'] != "" ?
                new \DateTime($jstock['delivery_date']) : null);
            $association->setOrigin($jstock['from']);
            $em->flush();

            if(count($jstock["invoices4save"]) > 0){
                foreach($jstock["invoices4save"] as $jinvoice){
                    $invoice = null;
                    if($jinvoice["id"])
                        $invoice = $em->getRepository('VGMWOBundle:InvoiceReference')->find($jinvoice["id"]);
                    if($invoice == null){
                        $invoice = new InvoiceReference();
                        $em->persist($invoice);
                    }
                    $invoice->setOrderNumber($jinvoice['order']);
                    $invoice->setStatus($jinvoice['status']);
                    $invoice->setAmount($jinvoice['amount']);
                    $invoice->setNumber($jinvoice['invoice']);
                    $invoice->setItems($jinvoice['items']);
                    $invoice->setDate($jinvoice['date'] != null && $jinvoice['date'] != "" ? new \DateTime($jinvoice['date']) : null);

                    if($jinvoice['client_id'] != null)
                        $invoice->setBillTo($em->getRepository('VGMWOBundle:Client')->find($jinvoice['client_id']));

                    if($jinvoice['destination_id'] != null)
                        $invoice->setPurpose($em->getRepository('VGMWOBundle:SaleType')->find($jinvoice['destination_id']));

                    $invoice->setWostock($association);

                    $em->flush($invoice);
                }
            }

            if(count($jstock["invoices4delete"]) > 0){
                foreach($jstock["invoices4delete"] as $jdinvoice_id){
                    $dinvoice = null;
                    if($jdinvoice_id["id"])
                        $dinvoice = $em->getRepository('VGMWOBundle:InvoiceReference')->find($jdinvoice_id["id"]);
                    if($dinvoice != null){
                        $em->remove($dinvoice);
                        $em->flush();
                    }
                }
            }
        }

        foreach($invoices4delete as $jdinvoice){
            $dinvoice = null;
            if($jdinvoice["id"])
                $dinvoice = $em->getRepository('VGMWOBundle:InvoiceReference')->find($jdinvoice["id"]);
            if($dinvoice != null){
                    $em->remove($dinvoice);
                    $em->flush();
            }
        }

        foreach($invoices4save as $jinvoice){
            $invoice = null;
            if($jinvoice["id"])
                $invoice = $em->getRepository('VGMWOBundle:InvoiceReference')->find($jinvoice["id"]);
            if($invoice == null){
                $invoice = new InvoiceReference();
                $em->persist($invoice);
            }
            $invoice->setOrderNumber($jinvoice['order']);
            $invoice->setStatus($jinvoice['status']);
            $invoice->setAmount($jinvoice['amount']);
            $invoice->setNumber($jinvoice['invoice']);
            $invoice->setItems($jinvoice['items']);
            $invoice->setDate($jinvoice['date'] != null && $jinvoice['date'] != "" ? new \DateTime($jinvoice['date']) : null);

            if($jinvoice['client_id'] != null)
                $invoice->setBillTo($em->getRepository('VGMWOBundle:Client')->find($jinvoice['client_id']));

            if($jinvoice['destination_id'] != null)
                $invoice->setPurpose($em->getRepository('VGMWOBundle:SaleType')->find($jinvoice['destination_id']));

            $invoice->setWorkorder($workorder);

            $em->flush();
        }

        $workorder->setStatus($status);
        $workorder->setStatusFromStocks();
        $workorder->setReadyAtFromStocks();
        $em->flush($workorder);

        foreach($tasks4delete as $jsdtask){
            $dtask = null;
            if($jsdtask["own_id"])
                $dtask = $em->getRepository('VGMWOBundle:PlanningTask')->find($jsdtask["own_id"]);
            if($dtask != null){
                $em->remove($dtask);
                $em->flush();
            }
        }

        foreach($tasks4save as $jstask){
            $task = null;
            if($jstask["own_id"])
                $task = $em->getRepository('VGMWOBundle:PlanningTask')->find($jstask["own_id"]);
            if($task == null){
                $task = new PlanningTask();
                $em->persist($task);
                $task->addWorkorder($workorder);
                $workorder->addTask($task);
            }

            $task->setArea($jstask['area']);
            $task->setDescription($jstask['description']);

            $task_unit = $em->getRepository('VGMWOBundle:CUnit')->find($jstask["unit_id"]);
            $task->setUnit($task_unit);

            $task_sup = $em->getRepository('VGMWOBundle:Worker')->find($jstask["supervisor_id"]);
            $task->setSupervisor($task_sup);

            $task->setOrderNumber($jstask['order']);
            $task->setStatus($jstask['status'] ? $jstask['status'] : "Current");
            $task->setStartDate($jstask['start_date'] ? new \DateTime($jstask['start_date']) : null);
            $task->setEtime($jstask['eTime']);
            $task->setDeathLine($jstask['death_line'] ? new \DateTime($jstask['death_line']) : null);
            $task->setObs($jstask['obs']);
            $task->setCompletedAt($jstask['ended']);
            $task->setRtime($jstask['rTime']);
            if($task->getId() == null){
                $task->setCreatedAt(new \DateTime());
                $task->setCreatedBy($this->getUser()->getWorker());
            }
            $task->setUpdatedAt(new \DateTime());
            $task->setUpdatedBy($this->getUser()->getWorker()->__toString());

            $em->flush();
        }

        $response = array("code" => 100, "success" => true, 'message' => "Updated", "status"=>$workorder->getStatus());

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function woExistAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         */
        $wonumber = $request->get('wo_number');

        $exist = $em->getRepository('VGMWOBundle:WorkOrder')->countWorOrdersByNo($wonumber) > 0 ? true : false;
        $id = null;
        if($exist){
            $id = $em->getRepository('VGMWOBundle:WorkOrder')->getWorOrderByNo($wonumber)->getId();
            /*$data = $this->getWorkOrderData($id);*/
            //$response = array("code" => 100, "success" => true, "exist" => $exist, "wo_id" => $id);
        }
        //else
        $response = array("code" => 100, "success" => true, "exist" => $exist, "wo_id" => $id);

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function woExist4CreateUpdateAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         */
        $wonumber = $request->get('wo_number');
        $woid = "";
        if($request->get('wo_id'))
            $woid = $request->get('wo_id');

        $exist = $em->getRepository('VGMWOBundle:WorkOrder')->countWorOrdersByNo($wonumber) > 0 ?
            $em->getRepository('VGMWOBundle:WorkOrder')->getWorOrderByNo($wonumber)->getId() == $woid ?
                false : true : false;

        $response = array("code" => 100, "success" => true, "exist" => $exist);

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     * status =
     * 0:
     * 1:
     * 2: Duplicated
     * 3:
     * 4: In use (It could be used by other WO or not depending on its equipment.multiple_wos property)
     */
    public function findStockAjaxAction(Request $request)
    {
        $em = $this->getDoctrine()->getManager();

        /**
         * Getting data request
         * initializing others vars
         */
        $stockNo = $request->get('stock');

        $result = [];
        /*
         * message meaning
         * 0: It exists and everything is Ok
         * 1: It doesn't exist (You can't do anything with it)
         * 2: It is duplicated (You can't do anything with it)
         * 3: It is sold
         * 4: It is in use in another WO/OT
         */
        $message = 0;
        $equip = null;

        /**
         * Aeroservices DB
         */
        $ext_em = $this->getDoctrine()->getManager('aeroservicios');
        $conn = $ext_em->getConnection();

        /**
         * Stock Query
         * Getting stocks item own data
         */
        $stock_query = "SELECT Items.Id as asdb_id,Items.StockNo as stock,Items.SerialNo as serial,Items.Status as Status,";
        $stock_query = $stock_query."Items.UseVar as hours_miles,Items.Model as model,Items.Cap as cap,";
        $stock_query = $stock_query."Items.Type as sType,Items.Year as year,Items.WorkOrder as wo,Items.ItemCondition as cond,";

        /**
         * Stock Query Relationships
         * Getting stocks item relationships
         */
        $stock_query = $stock_query."Items.Brand as brand,Items.TranGenCom as tgc,Items.Engine as engine,";
        $stock_query = $stock_query."Items.Localization as loc,Items.ActualCond as acond ";
        $stock_query = $stock_query."FROM Items ";

        $stock_query = $stock_query."WHERE Items.StockNo =:stock";

        $query = $conn->prepare($stock_query);

        $query->bindValue('stock', $stockNo);

        $query->execute();
        $stock_result = $query->fetchAll();

        /**
         * Default DB
         */
        $updatedStock = $em->getRepository('VGMWOBundle:Stock')->getStockByNo($stockNo);

        if($stock_result != null){
            $stock_cant = $query->rowCount();

            if($stock_cant == 1){
                $result = $stock_result[0];
                if($result['Status'] == 1)
                    $message = 3;

                /**
                 * Brand
                 */
                if($result['brand'] != null){
                    if($result['brand'] != ""){
                        $brand_query = "SELECT Items_Brands.ResourceName as brand FROM Items_Brands WHERE Items_Brands.Id =:brand";
                        $query = $conn->prepare($brand_query);

                        $query->bindValue('brand', $result['brand']);
                        $query->execute();
                        $brand_result = $query->fetchAll();

                        if($query->rowCount() > 0)
                            $result['brand'] = $brand_result[0]['brand'];
                        else
                            $result['brand'] = "---";
                    }
                }
                else
                    $result['brand'] = "---";
                /**
                 * TGC
                 */
                if($result['tgc'] != null){
                    if($result['tgc'] != ""){
                        $tgc_query = "SELECT TranGenCom.ResourceName as tgc FROM TranGenCom WHERE TranGenCom.Id =:tgc";
                        $query = $conn->prepare($tgc_query);

                        $query->bindValue('tgc', $result['tgc']);
                        $query->execute();
                        $tgc_result = $query->fetchAll();

                        if($query->rowCount() > 0)
                            $result['tgc'] = $tgc_result[0]['tgc'];
                        else
                            $result['tgc'] = "---";
                    }
                }
                else
                    $result['tgc'] = "---";
                /**
                 * Engine
                 */
                if($result['engine'] != null){
                    if($result['engine'] != "" ){
                        $engine_query = "SELECT Items_Engines.ResourceName as engine FROM Items_Engines WHERE Items_Engines.Id =:engine";
                        $query = $conn->prepare($engine_query);

                        $query->bindValue('engine', $result['engine']);
                        $query->execute();
                        $engine_result = $query->fetchAll();

                        if($query->rowCount() > 0)
                            $result['engine'] = $engine_result[0]['engine'];
                        else
                            $result['engine'] = "---";
                    }
                }
                else
                    $result['engine'] = "---";
                /**
                 * Localization
                 */
                if($result['loc'] != null){
                    if($result['loc'] != ""){
                        $loc_query = "SELECT Items_Localization.ResourceName as loc FROM Items_Localization ";
                        $loc_query = $loc_query."WHERE Items_Localization.Id =:loc";
                        $query = $conn->prepare($loc_query);

                        $query->bindValue('loc', $result['loc']);
                        $query->execute();
                        $loc_result = $query->fetchAll();

                        if($query->rowCount() > 0)
                            $result['loc'] = $loc_result[0]['loc'];
                        else
                            $result['loc'] = "---";
                    }
                }
                else
                    $result['loc'] = "---";
                /**
                 * ActualCond
                 */
                if($result['acond'] != null){
                    if($result['acond'] != ""){
                        $acond_query = "SELECT Items_ActualCond.ResourceName as acond FROM Items_ActualCond ";
                        $acond_query = $acond_query."WHERE Items_ActualCond.Id =:acond";
                        $query = $conn->prepare($acond_query);

                        $query->bindValue('acond', $result['acond']);
                        $query->execute();
                        $acond_result = $query->fetchAll();

                        if($query->rowCount() > 0)
                            $result['acond'] = $acond_result[0]['acond'];
                        else
                            $result['acond'] = "---";
                    }
                }
                else
                    $result['acond'] = "---";
                /**
                 * Type
                 */
                if($result['sType'] != null){
                    if($result['sType'] != ""){
                        $type_query = "SELECT Items_Type.ResourceName as sType FROM Items_Type ";
                        $type_query = $type_query."WHERE Items_Type.Id =:sType";
                        $query = $conn->prepare($type_query);

                        $query->bindValue('sType', $result['sType']);
                        $query->execute();
                        $type_result = $query->fetchAll();

                        if($query->rowCount() > 0)
                            $result['sType'] = $type_result[0]['sType'];
                        else
                            $result['sType'] = "---";
                    }
                }
                else
                    $result['sType'] = "---";
            }
            else{
                if($stock_cant > 1)
                    $message = 2;
                else
                    $message = 1;
            }
        }
        else
            $message = 1;

        if($message == 0 || $message == 3)
        {
            $inDB = true;

            if($updatedStock == null){
                $updatedStock = new Stock();
                $inDB = false;
            }

            $updatedStock->setAsdbId((int)$result['asdb_id']);
            $updatedStock->setCode($result['stock']);
            $updatedStock->setSerial($result['serial']);
            $updatedStock->setBrand($result['brand']);
            $updatedStock->setModel($result['model']);
            $updatedStock->setEngine($result['engine']);
            $updatedStock->setHoursMiles($result['hours_miles']);
            $updatedStock->setCapacity($result['cap']);
            $updatedStock->setActualCondition($result['acond']);
            $updatedStock->setDescription($result['cond']);
            $updatedStock->setLocalization($result['loc']);
            $updatedStock->setTransGenCom($result['tgc']);
            $updatedStock->setYear($result['year']);
            $updatedStock->setStatus($result['Status']);

            if(!$inDB)
                $em->persist($updatedStock);

            $em->flush();

            $result["equipment_id"] = $inDB == true && $updatedStock->getEquipment() ?
                $updatedStock->getEquipment()->getId() : null;
            $result["own_id"] = $updatedStock->getId();
        }
        else if($message == 1 && $updatedStock != null){
            $result["asdb_id"] = $updatedStock->getAsdbId();
            $result["stock"] = $updatedStock->getCode();
            $result["serial"] = $updatedStock->getSerial();
            $result["hours_miles"] = $updatedStock->getHoursMiles();
            $result["model"] = $updatedStock->getModel();
            $result["cap"] = $updatedStock->getCapacity();
            $result["year"] = $updatedStock->getYear();
            $result["wo"] = $updatedStock->getWo();
            $result["cond"] = $updatedStock->getDescription();
            $result["brand"] = $updatedStock->getBrand();
            $result["tgc"] = $updatedStock->getTransGenCom();
            $result["engine"] = $updatedStock->getEngine();
            $result["loc"] = $updatedStock->getLocalization();
            $result["acond"] = $updatedStock->getActualCondition();
            $result["equipment_id"] = $updatedStock->getEquipment() ?
                $updatedStock->getEquipment()->getId() : null;
            $result["own_id"] = $updatedStock->getId();

            $message = 0;
        }

        if($updatedStock != null)
        {
            if($updatedStock->hasActiveLink()){
                $message = 4;
                /*$duplicable_stocks = $this->container->getParameter('duplicable_stocks');
                foreach($duplicable_stocks as $ds){
                    if(strpos(strtolower($result["sType"]), $ds) >= 0)
                        $message = 0;
                }*/
            }
        }
        $response = array("code" => 100, "success" => true, "result" => $result, "message" => $message);

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function refreshEquipmentListAction(Request $request){
        $em = $this->getDoctrine()->getManager();

        $equipments = $em->getRepository('VGMWOBundle:Equipment')->getAll();

        $list = [];
        foreach($equipments as $eq){
            $list[] = array('name' => $eq->getCode(), 'id' => $eq->getId());
        }

        $stock_eq = $request->get('stock_eq');

        $selected = $em->getRepository('VGMWOBundle:Equipment')->getbyASDBId($stock_eq);
        $selected = $selected->getId();

        $response = array("code" => 100, "success" => true, "list" => $list, "selected" => $selected);

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function updateStocks4WorkOrderAjaxAction(Request $request){
        /**
         * Getting data request
         * initializing others vars
         */
        $p = "ldkjlskjfls";

        $stocks = $request->get("list");
        $work_order = $request->get('work_order');
        $form = $request->get('myform');

        /**
         * Aeroservices DB
         */
        $em = $this->getDoctrine()->getManager();

        $p = $stocks[0]["stock"];

        foreach($stocks as $st){
            //$p = $p + $stock->stock;

            $stock4update = $em->getRepository('VGMWOBundle:Stock')->getStockByNo($st['stock']);
            $stock4update->setWo($work_order);
            $em->flush();
        }

        $response = array("code" => 100, "success" => true, 'message' => $p);

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function getWorkOrderByNoAjaxAction(Request $request){
        /**
         * Getting data request
         * initializing others vars
         */

        $number = $request->get("number");

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

        $wo = $em->getRepository('VGMWOBundle:WorkOrder')->getWorOrderByNo($number);
        $wo2send = null;
        if($wo){
            //$stocks = $wo->getStocksFull();
            $stocks = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
                            ->getAssociationByWoNumber($wo->getNumber());

            $stocks_list = [];
            if(count($stocks) > 0){
                foreach($stocks as $st){
                    $stocks_list[] = array("id" => $st->getStock()->getId(),
                    "number" => $st->getAsNumber(),
                    "code" => $st->getStock() ? $st->getStock()->getCode() : null,
                    "serial" => $st->getStock() ? $st->getStock()->getSerial() : null,
                    "equipment" => $st->getStock() ? $st->getStock()->getEquipment() ?
                                    $st->getStock()->getEquipment()->__toString() : null : null,
                    "description" => $st->getStock()->getDescription(),
                    "picture" => $st->getStock() ? $st->getStock()->getImgURL(0) : null);
                }
            }
            $wo2send = array("id" => $wo->getId(),
                "number" => $wo->getNumber(),
                "supervisor" => $wo->getSupervisor() ? $wo->getSupervisor()->__toString() : null,
                "client" => $wo->getClient() ? $wo->getClient()->__toString() : null,
                "equipment" => $wo->getEquipment() ? $wo->getEquipment()->__toString() : null,
                "worktype" => $wo->getWorkType() ? $wo->getWorkType()->__toString() : null,
                "picture" => $wo->getImageUrl(),
                "stocks" => $stocks_list);
        }

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

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function getWorkOrderSupportAjaxAction(Request $request){
        /**
         * Getting data request
         * initializing others vars
         */

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

        $equipments = $em->getRepository('VGMWOBundle:Equipment')->findBy(array(), array('code' => 'ASC'));
        $supervisors = $em->getRepository('VGMWOBundle:Worker')->getSupervisors();
        $units = $em->getRepository('VGMWOBundle:CUnit')->findAllUnits();
        $departments = $em->getRepository('VGMWOBundle:NDepartment')->findBy(array(), array('name' => 'ASC'));
        $clients = $em->getRepository('VGMWOBundle:Client')->findBy(array(), array('name' => 'ASC'));
        $t_sales = $em->getRepository('VGMWOBundle:SaleType')->findBy(array(), array('sale_type' => 'ASC'));
        $t_works = $em->getRepository('VGMWOBundle:WorkType')->findBy(array(), array('work_type' => 'ASC'));

        $equipments2send = [];
        foreach($equipments as $eqt){
            $equipments2send[] = array("id" => $eqt->getId(),
                "name" => $eqt->__toString(),
                "code" => $eqt->getCode());
        }
        $supervisors2send = [];
        foreach($supervisors as $sup){
            $supervisors2send[] = array("id" => $sup->getId(),
                "picture" => $sup->getPicture() ? $sup->getPicture()->getFinalWebPath() : null,
                "code" => $sup->getWorkerCode(),
                "name" => $sup->getName(),
                "fullname" => $sup->__toString(),
                "department" => $sup->getDepartment()->__toString());
        }
        $units2send = [];
        foreach($units as $unit){
            $units2send[] = array("id" => $unit->getId(),
                "name" => $unit->getName(),
                "fullname" => $unit->getParent() ? $unit->getParent()->getName().'-'.$unit->getName() : null);
        }
        $departments2send = [];
        foreach($departments as $dep){
            $departments2send[] = array("id" => $dep->getId(), "name" => $dep->__toString());
        }
        $clients2send = [];
        foreach($clients as $cl){
            $clients2send[] = array("id" => $cl->getId(), "name" => $cl->__toString());
        }
        $t_sales2send = [];
        foreach($t_sales as $ts){
            $t_sales2send[] = array("id" => $ts->getId(), "name" => $ts->__toString());
        }
        $t_works2send = [];
        foreach($t_works as $tw){
            $t_works2send[] = array("id" => $tw->getId(), "name" => $tw->__toString());
        }

        $response = array("code" => 100, "success" => true, 'message' => "Ok",
            "equipments" => $equipments2send,
            "supervisors" => $supervisors2send,
            "units" => $units2send,
            "departments" => $departments2send,
            "clients" => $clients2send,
            "t_sales" => $t_sales2send,
            "t_works" => $t_works2send);

        return new Response(json_encode($response));
    }
    public function getNextWorkOrderNumberAjaxAction(Request $request){
        /**
         * Getting data request
         * initializing others vars
         */

        $first_code = $this->container->getParameter('first_workorder');
        $next_code = $this->getDoctrine()->getManager()->getRepository('VGMWOBundle:WorkOrder')->nextWorkOrderCode($first_code);

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

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function getWorkOrderDataByIdAjaxAction(Request $request){
        /**
         * Getting data request
         * initializing others vars
         */
        $wo_id = $request->get('id');
        $data = $this->getWorkOrderData($wo_id);
        $response = array("code" => 100, "success" => true, 'message' => "Ok",
            "data" => $data);

        return new Response(json_encode($response));
    }
    /**
     * @param Request $request
     * @return Response
     */
    public function getWorkOrderDataByNumberAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();
            /**
             * Getting data request
             * initializing others vars
             */

        $wo_number = $request->get('wo_number');
        $data = null;
        $wo = $em->getRepository('VGMWOBundle:WorkOrder')->getWorOrderByNo($wo_number);

        if($wo){
            //$wo_sts = $wo->getStocksFull();
            $wo_sts = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
                            ->getAssociationByWoNumber($wo->getNumber());
            $stocks = [];

            if(count($wo_sts) > 0){
                foreach($wo_sts as $wo_st){
                    $stocks[] = array("own_id" => $wo_st->getStock() ? $wo_st->getStock()->getId() : null,
                    "asdb_id" => $wo_st->getStock() ? $wo_st->getStock()->getAsdbId() : null,
                    "stock" => $wo_st->getStock() ? $wo_st->getStock()->getCode() : null,
                    "serial" => $wo_st->getStock() ? $wo_st->getStock()->getSerial() : null,
                    "hours_miles" => $wo_st->getStock() ? $wo_st->getStock()->getHoursMiles() : null,
                    "model" => $wo_st->getStock() ? $wo_st->getStock()->getModel() : null,
                    "cap" => $wo_st->getStock() ? $wo_st->getStock()->getCapacity() : null,
                    "year" => $wo_st->getStock() ? $wo_st->getStock()->getYear() : null,
                    "cond" => $wo_st->getStock() ? $wo_st->getStock()->getDescription() : null,
                    "brand" => $wo_st->getStock() ? $wo_st->getStock()->getBrand() : null,
                    "tgc" => $wo_st->getStock() ? $wo_st->getStock()->getTransGenCom() : null,
                    "engine" => $wo_st->getStock() ? $wo_st->getStock()->getEngine() : null,
                    "loc" => $wo_st->getStock() ? $wo_st->getStock()->getLocalization : null(),
                    "acond" => $wo_st->getStock() ? $wo_st->getStock()->getActualCondition() : null,
                    "actual_workorder" => $wo_st->getStock() ? $wo_st->getStock()->getWo() : null,
                    "equipment_id" => $wo_st->getStock() ? $wo_st->getStock()->getEquipment() ?
                                        $wo_st->getStock()->getEquipment()->getId() : null : null,

                    "number" => $wo_st->getAsNumber(),
                    "status" => $wo_st->getStatus(),
                    "client_id" => $wo_st->getClient() ? $wo_st->getClient()->getId() : null);
                }
            }

            $data = array("id" => $wo->getId(),
                "equipment_id" => $wo->getEquipment() ? $wo->getEquipment()->getId() : null,
                "sup_id" => $wo->getSupervisor() ? $wo->getSupervisor()->getId() : null,
                "picture" => $wo->getImageUrl(),
                "deliverydt" => $wo->getDeliveryDate() ? $wo->getDeliveryDate()->format('m/d/Y') : null,
                "ts_id" => $wo->getSaletype() ? $wo->getSaletype()->getId() : null,
                "tw_id" => $wo->getWorktype() ? $wo->getWorktype()->getId() : null,
                "status" => $wo->getStatus(),
                "client_id" => $wo->getClient() ? $wo->getClient()->getId() : null,
                "stocks_qty" => $wo->getStockQuantity(),
                "price" => $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR') ? $wo->getPrice() : 0,
                "obs" => $wo->getObs(),
                "stocks" => $stocks,
                "cost" => $this->getWOCost($wo));
        }

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

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

    public function getWorkOrderObsAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();
            /**
             * Getting data request
             * initializing others vars
             */

        $wo_id = $request->get('id');
        $data = $this->getWorkOrderObs($wo_id);

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

        return new Response(json_encode($response));
    }
    public function getWorkOrderTasksAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();
            /**
             * Getting data request
             * initializing others vars
             */

        $wo_id = $request->get('id');
        $data = $this->getWorkOrderTasks($wo_id);

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

        return new Response(json_encode($response));
    }
    public function getWorkOrderStocksAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();
            /**
             * Getting data request
             * initializing others vars
             */

        $wo_id = $request->get('id');
        $data = $this->getWorkOrderStocks($wo_id);

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

        return new Response(json_encode($response));
    }
    public function getWorkOrderInvoicesAjaxAction(Request $request){
        $em = $this->getDoctrine()->getManager();
            /**
             * Getting data request
             * initializing others vars
             */

        $wo_id = $request->get('id');
        $data = $this->getWorkOrderInvoices($wo_id);

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

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

    //Auxiliary functions
    private function getWorkOrderObs($wo_id){
        $em = $this->getDoctrine()->getManager();
        $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($wo_id);
        $wo_obs = $wo->getObs();
        $obs_list = [];
        if(count($wo_obs) > 0){
            foreach($wo_obs as $obs){
                $obs_list[] = array("id" => $obs->getId(), "obs" => $obs->getObs());
            }
        }
        return $obs_list;
    }
    private function getWorkOrderTasks($wo_id){
        $em = $this->getDoctrine()->getManager();
        $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($wo_id);
        $wo_tasks = $wo->getTasks();
        $tasks = [];
        if(count($wo_tasks) > 0){
            foreach($wo_tasks as $task){
                $tasks[] = array("own_id" => $task->getId(),
                    "order" => $task->getOrderNumber(),
                    "area" => $task->getArea(),
                    "description" => $task->getDescription(),
                    "unit_id" => $task->getUnit() ? $task->getUnit()->getId() : null,
                    "supervisor_id" => $task->getSupervisor() ? $task->getSupervisor()->getId() : null,
                    "status" => $task->getStatus(),
                    "start_date" => $task->getStartDate() ? $task->getStartDate()->format("m/d/Y") : null,
                    "etime" => $task->getEtime() ? round($task->getEtime(), 2) : null,
                    "rtime" => $task->getRtime() ? round($task->getRtime(), 2) : null,
                    "death_line" => $task->getDeathLine() ? $task->getDeathLine()->format("m/d/Y") : null,
                    "obs" => $task->getObs(),
                    "ended" => $task->getCompletedAt() ? $task->getCompletedAt()->format("m/d/Y") : null,
                    "obs" => $task->getRtime() ? round($task->getRtime(), 2) : null,
                    "created_at" => $task->getCreatedAt() ? $task->getCreatedAt()->format("m/d/Y") : null,
                    "created_by_id" => $task->getCreatedBy() ? $task->getCreatedBy()->getId() : null);
            }
        }
        usort($tasks, function ($a, $b) {
            return strnatcmp($a['order'], $b['order']);
        });
        return $tasks;
    }
    private function getWorkOrderStocks($wo_id){
        $em = $this->getDoctrine()->getManager();
        $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($wo_id);
        $wo_sts = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
            ->getAssociationByWoNumber($wo->getNumber());
        $stocks = [];
        if(count($wo_sts) > 0){
            foreach($wo_sts as $wo_st){
                $invoices = [];
                if(count($wo_st->getInvoiceRefs()) > 0){
                    foreach($wo_st->getInvoiceRefs() as $invoice){
                        $invoices[] = array("id" => $invoice->getId(),
                            "status" => $invoice->getStatus(),
                            "amount" => $invoice->getAmount(),
                            "items" => $invoice->getItems(),
                            "invoice" => $invoice->getNumber(),
                            "date" => $invoice->getDate() ? $invoice->getDate()->format("m/d/Y") : null,
                            "client_id" => $invoice->getBillTo() ? $invoice->getBillTo()->getId() : null,
                            "destination_id" => $invoice->getPurpose() ? $invoice->getPurpose()->getId() : null);
                    }
                }
                $stocks[] = array("own_id" => $wo_st->getStock() ? $wo_st->getStock()->getId() : null,
                    "woas_id" => $wo_st->getId(),
                    "asdb_id" => $wo_st->getStock() ? $wo_st->getStock()->getAsdbId() : null,
                    "stock" => $wo_st->getStock() ? $wo_st->getStock()->getCode() : null,
                    "serial" => $wo_st->getStock() ? $wo_st->getStock()->getSerial() : null,
                    "hours_miles" => $wo_st->getStock() ? $wo_st->getStock()->getHoursMiles() : null,
                    "model" => $wo_st->getStock() ? $wo_st->getStock()->getModel() : null,
                    "cap" => $wo_st->getStock() ? $wo_st->getStock()->getCapacity() : null,
                    "year" => $wo_st->getStock() ? $wo_st->getStock()->getYear() : null,
                    "cond" => $wo_st->getStock() ? $wo_st->getStock()->getDescription() : null,
                    "brand" => $wo_st->getStock() ? $wo_st->getStock()->getBrand() : null,
                    "tgc" => $wo_st->getStock() ? $wo_st->getStock()->getTransGenCom() : null,
                    "engine" => $wo_st->getStock() ? $wo_st->getStock()->getEngine() : null,
                    "loc" => $wo_st->getStock() ? $wo_st->getStock()->getLocalization() : null,
                    "acond" => $wo_st->getStock() ? $wo_st->getStock()->getActualCondition() : null,
                    "actual_workorder" => $wo_st->getStock() ? $wo_st->getStock()->getWo() : null,
                    "equipment_id" => $wo_st->getStock() ? $wo_st->getStock()->getEquipment() ?
                            $wo_st->getStock()->getEquipment()->getId() : null : null,
                    "number" => $wo_st->getAsNumber(),
                    "status" => $wo_st->getStatus(),
                    "client_id" => $wo_st->getClient() ? $wo_st->getClient()->getId() : null,
                    "due_date" => $wo_st->getDueDate() ? $wo_st->getDueDate()->format("m/d/y") : null,
                    "delivery_date" => $wo_st->getDeliveryDate() ? $wo_st->getDeliveryDate()->format("m/d/y") : null,
                    "from" => $wo_st->getOrigin(),
                    "invoices" => $invoices,
                    "t_sale_id" => $wo_st->getSaletype() ? $wo_st->getSaletype()->getId() : null,
                    "t_work_id" => $wo_st->getWorktype() ? $wo_st->getWorktype()->getId() : null);
            }
        }
        return $stocks;
    }
    private function getWorkOrderInvoices($wo_id){
        $em = $this->getDoctrine()->getManager();
        $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($wo_id);
        $wo_invoices = $wo->getInvoiceRefs();
        $invoices = [];
        if(count($wo_invoices) > 0){
            foreach($wo_invoices as $invoice){
                $invoices[] = array("id" => $invoice->getId(),
                    "order" => $invoice->getOrderNumber(),
                    "status" => $invoice->getStatus(),
                    "amount" => $invoice->getAmount(),
                    "items" => $invoice->getItems(),
                    "invoice" => $invoice->getNumber(),
                    "date" => $invoice->getDate() ? $invoice->getDate()->format("m/d/Y") : null,
                    "client_id" => $invoice->getBillTo() ? $invoice->getBillTo()->getId() : null,
                    "destination_id" => $invoice->getPurpose() ? $invoice->getPurpose()->getId() : null);
            }
        }
        usort($invoices, function ($a, $b) {
            return strnatcmp($a['order'], $b['order']);
        });
        return $invoices;
    }
    private function getWorkOrderData($wo_id){
        $em = $this->getDoctrine()->getManager();
        $wo = $em->getRepository('VGMWOBundle:WorkOrder')->find($wo_id);
        /*$wo_sts = $em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
            ->getAssociationByWoNumber($wo->getNumber());*/
        $stocks = count($em->getRepository('VGMWOBundle:WorkOrderStockAssociation')
            ->getAssociationByWoNumber($wo->getNumber()));
        /*if(count($wo_sts) > 0){
            foreach($wo_sts as $wo_st){
                $stocks[] = array("own_id" => $wo_st->getStock() ? $wo_st->getStock()->getId() : null,
                    "asdb_id" => $wo_st->getStock() ? $wo_st->getStock()->getAsdbId() : null,
                    "stock" => $wo_st->getStock() ? $wo_st->getStock()->getCode() : null,
                    "serial" => $wo_st->getStock() ? $wo_st->getStock()->getSerial() : null,
                    "hours_miles" => $wo_st->getStock() ? $wo_st->getStock()->getHoursMiles() : null,
                    "model" => $wo_st->getStock() ? $wo_st->getStock()->getModel() : null,
                    "cap" => $wo_st->getStock() ? $wo_st->getStock()->getCapacity() : null,
                    "year" => $wo_st->getStock() ? $wo_st->getStock()->getYear() : null,
                    "cond" => $wo_st->getStock() ? $wo_st->getStock()->getDescription() : null,
                    "brand" => $wo_st->getStock() ? $wo_st->getStock()->getBrand() : null,
                    "tgc" => $wo_st->getStock() ? $wo_st->getStock()->getTransGenCom() : null,
                    "engine" => $wo_st->getStock() ? $wo_st->getStock()->getEngine() : null,
                    "loc" => $wo_st->getStock() ? $wo_st->getStock()->getLocalization() : null,
                    "acond" => $wo_st->getStock() ? $wo_st->getStock()->getActualCondition() : null,
                    "actual_workorder" => $wo_st->getStock() ? $wo_st->getStock()->getWo() : null,
                    "equipment_id" => $wo_st->getStock() ? $wo_st->getStock()->getEquipment() ?
                            $wo_st->getStock()->getEquipment()->getId() : null : null,

                    "number" => $wo_st->getAsNumber(),
                    "status" => $wo_st->getStatus(),
                    "client_id" => $wo_st->getClient() ? $wo_st->getClient()->getId() : null);
            }
        }*/

        $invoices = count($wo->getInvoiceRefs());

        //$wo_tasks = $wo->getTasks();
        $tasks = count($wo->getTasks());
        /*if(count($wo_tasks) > 0){
            foreach($wo_tasks as $task){
                $tasks[] = array("own_id" => $task->getId(),
                    "area" => $task->getArea(),
                    "description" => $task->getDescription(),
                    "unit_id" => $task->getUnit() ? $task->getUnit()->getId() : null,
                    "supervisor_id" => $task->getSupervisor() ? $task->getSupervisor()->getId() : null,
                    "status" => $task->getStatus(),
                    "start_date" => $task->getStartDate() ? $task->getStartDate()->format("m/d/Y") : null,
                    "etime" => $task->getEtime() ? round($task->getEtime(), 2) : null,
                    "rtime" => $task->getRtime() ? round($task->getRtime(), 2) : null,
                    "death_line" => $task->getDeathLine() ? $task->getDeathLine()->format("m/d/Y") : null,
                    "obs" => $task->getObs(),
                    "ended" => $task->getCompletedAt() ? $task->getCompletedAt()->format("m/d/Y") : null,
                    "obs" => $task->getRtime() ? round($task->getRtime(), 2) : null,
                    "created_at" => $task->getCreatedAt() ? $task->getCreatedAt()->format("m/d/Y") : null,
                    "created_by_id" => $task->getCreatedBy() ? $task->getCreatedBy()->getId() : null);
            }
        }*/

        //$wo_obs = $wo->getObs();
        $obs_list = count($wo->getObs());
        /*if(count($wo_obs) > 0){
            foreach($wo_obs as $obs){
                $obs_list[] = array("id" => $obs->getId(), "obs" => $obs->getObs());
            }
        }*/

        $data = array("id" => $wo->getId(),
            "wo_number" => $wo->getNumber(),
            "origin" => $wo->getOrigin(),
            "status" => $wo->getStatus(),
            "picture" => $wo->getImageUrl(),
            "due_date" => $wo->getDeliveryDate() ? $wo->getDeliveryDate()->format('m/d/Y') : null,
            "delivery_date" => $wo->getReadyAt() ? $wo->getReadyAt()->format('m/d/Y') : null,
            "stocks_qty" => $wo->getStockQuantity(),
            "price" => $this->getUser()->hasRole('ROLE_SUPER_SUPERVISOR') ? $wo->getPrice() : 0,
            "obs" => $obs_list,
            "created" => $wo->getCreatedAt() ? $wo->getCreatedAt()->format('m/d/Y') : null,
            "started" => $wo->getStartedAt() ? $wo->getStartedAt()->format('m/d/Y') : null,
            "equipment_id" => $wo->getEquipment() ? $wo->getEquipment()->getId() : null,
            "sup_id" => $wo->getSupervisor() ? $wo->getSupervisor()->getId() : null,
            "ts_id" => $wo->getSaletype() ? $wo->getSaletype()->getId() : null,
            "tw_id" => $wo->getWorktype() ? $wo->getWorktype()->getId() : null,
            "client_id" => $wo->getClient() ? $wo->getClient()->getId() : null,
            "stocks" => $stocks,
            "invoices" => $invoices,
            "tasks" => $tasks,
            "cost" => $this->getWOCost($wo),
            "created_by" => $wo->getCreatedBy() != null ? array("id" => $wo->getCreatedBy()->getId(),
                    "name" => $wo->getCreatedBy()->__toString()) : null,
            "x_price" => $wo->getXPrice());
        return $data;
    }
    private function getWOCost($wo){
        $loops = $wo->getShifts();
        $cost = 0;
        foreach($loops as $loop){
            if($loop->getCOvertime() > 0)
                $cost += (($loop->getTotalTime() - $loop->getCOvertime())* $loop->getHourCost()
                    +   $loop->getCOvertime()* $loop->getHourCost() * 1.5);
            else
                $cost += $loop->getTotalTime() * $loop->getHourCost();
        }
        return $cost;
    }


}
