<?php

namespace VGM\WOBundle\Controller;

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

use Doctrine\ORM\EntityRepository;

use VGM\WOBundle\Entity\InvoiceVia;
use VGM\WOBundle\Entity\Stock;
use VGM\WOBundle\Entity\Invoice;
use VGM\WOBundle\Entity\InvoiceItem;
use VGM\WOBundle\Entity\InvoiceTerms;
use VGM\WOBundle\Entity\InvoiceItemTemplate;

/**
 * WorkOrder controller.
 *
 */

class InvoiceController extends Controller
{
    public function indexAction(){
        return $this->render('VGMWOBundle:Invoicing:index.html.twig');
    }
    //Invoices for Invoicing module: Not applicable
    public function invoicesFullListAction(Request $request){
        $em = $this->getDoctrine()->getEntityManager();

        $sql = "SELECT * FROM (SELECT invoice.id,invoice.number,DATE_FORMAT(invoice.date, '%m/%d/%y') AS qbdate,"
            ."DATE_FORMAT(invoice.due_date, '%m/%d/%y') AS duedate,DATE_FORMAT(invoice.ship_date, '%m/%d/%y') AS shipdate,"
            ."invoice.total_balance AS total,client.name AS customer FROM invoice LEFT JOIN client ON invoice.bill_to_id = client.id) AS INV";

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

        $output = array(
            "aaSorting" => [[1,'desc']],
            "iTotalRecords" => count($results),
            "iTotalDisplayRecords" => count($results),
            "aaData" => array()
        );
        $columns = array( 'id', 'number', 'qbdate', 'duedate', 'shipdate', 'customer', 'total');

        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 invoicesListAction(Request $request){
        $invoice_status = $request->get('invoice_status');
        $invoice_destination = $request->get('invoice_destination') != null && $request->get('invoice_destination') != '' ?
            $request->get('invoice_destination') : null;
        $invoice_number = $request->get('invoice_number');
        $invoice_date_from = $request->get('invoice_date_from') != null && $request->get('invoice_date_from') != '' ?
            explode('/',$request->get('invoice_date_from')) : null;
        $invoice_date_to = $request->get('invoice_date_to') != null && $request->get('invoice_date_to') != '' ?
            explode('/',$request->get('invoice_date_to')) : null;
        $wo_stock = $request->get('wo_stock');
        $client_name = $request->get('client_name');
        $equipment = $request->get('equipment');

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

        $output = array(
            "aaSorting" => [[1,'desc']],
            "iTotalRecords" => count($results),
            "iTotalDisplayRecords" => count($results),
            "aaData" => array()
        );

        $columns = array('created_at','purpose','status','client','equipment','wo_stock','to_stock','to_invoice',
            'invoiced','date','number');

        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 getInvoicesListAction($invoice_status = null,$invoice_destination = null,$invoice_number = null,
                                          $invoice_date_from = null,$invoice_date_to = null,$wo_stock = null,$client_name = null,
                                          $equipment = null){

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

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

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

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

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

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

}
