<?php

namespace RxConnexionBundle\Service;

use Doctrine\ORM\EntityManager;
use Symfony\Component\DependencyInjection\ContainerInterface;

Class AccountingDashboardService {

    private $connection;
    private $container;
    private $em;

    public function __construct(EntityManager $em, ContainerInterface $container) {
        $this->em = $em;
        $this->connection = $em->getConnection();
        $this->container = $container;
    }


    public function getDashboardData() {
        return array(
            'overview' => $this->overview(),
            'accounting_billing' => $this->accountingBilling(),
        );

    }

    public function overview() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $sql_encoded = $this->connection->prepare("
            SELECT 
              ae.`id`,
              TO_BASE64(ae.`id`) AS idEncoded,
              ae.`accounting_file_id`,
              ae.`file_desc`
            FROM `accounting_encode` ae
            WHERE ae.`encode_date` IS NULL
        ");
        $sql_encoded->execute();

        $sql_check = $this->connection->prepare("
            SELECT    
              ae.`id`,
              TO_BASE64(ae.`id`) AS idEncoded,
              ae.`accounting_file_id`,
              ae.`file_desc`
            FROM `accounting_encode` ae
            WHERE ae.check_date IS NULL
        ");
        $sql_check->execute();

        $result['encoded'] = $sql_encoded->fetchAll();
        $result['check'] = $sql_check->fetchAll();

        return $result;
    }
    public function accountingBilling() {
        $sql_invoice = $this->connection->prepare("
            SELECT 
            (
              SELECT COUNT(DISTINCT ab1.`id`) 
              FROM `accounting_billing` ab1
              WHERE ab1.`is_deleted` = 0
              AND ab1.`encode_date` IS NULL
              AND ab1.`type` = 'Invoice'
              AND ab1.`id` = ab.`id`
              AND ab1.`is_deleted` = 0
            ) AS pendingInvoiceEncode,
            (
              SELECT COUNT(DISTINCT ab2.`id`) 
              FROM `accounting_billing` ab2
              WHERE ab2.`is_deleted` = 0
              AND ab2.`check_date` IS NULL
              AND ab2.`type` = 'Invoice'
              AND ab2.`id` = ab.`id`
              AND ab2.`is_deleted` = 0
            ) AS pendingInvoiceAudit
            FROM `accounting_billing` ab   
        ");
        $sql_invoice->execute();

        $sql_payment =  $this->connection->prepare("
            SELECT 
            (
              SELECT COUNT(DISTINCT ab1.`id`) 
              FROM `accounting_billing` ab1
              WHERE ab1.`is_deleted` = 0
              AND ab1.`encode_date` IS NULL
              AND ab1.`type` = 'Payment'
              AND ab1.`id` = ab.`id`
              AND ab1.`is_deleted` = 0
            ) AS pendingInvoiceEncode,
            (
              SELECT COUNT(DISTINCT ab2.`id`) 
              FROM `accounting_billing` ab2
              WHERE ab2.`is_deleted` = 0
              AND ab2.`check_date` IS NULL
              AND ab2.`type` = 'Payment'
              AND ab2.`id` = ab.`id`
              AND ab2.`is_deleted` = 0
            ) AS pendingInvoiceAudit
            FROM `accounting_billing` ab 
        ");
        $sql_payment->execute();

        $sql_refund = $this->connection->prepare("
             SELECT 
            (
              SELECT COUNT(DISTINCT ab1.`id`) 
              FROM `accounting_billing` ab1
              WHERE ab1.`is_deleted` = 0
              AND ab1.`encode_date` IS NULL
              AND ab1.`type` = 'Refund'
              AND ab1.`id` = ab.`id`
              AND ab1.`is_deleted` = 0
            ) AS pendingInvoiceEncode,
            (
              SELECT COUNT(DISTINCT ab2.`id`) 
              FROM `accounting_billing` ab2
              WHERE ab2.`is_deleted` = 0
              AND ab2.`check_date` IS NULL
              AND ab2.`type` = 'Refund'
              AND ab2.`id` = ab.`id`
              AND ab2.`is_deleted` = 0
            ) AS pendingInvoiceAudit
            FROM `accounting_billing` ab
        ");
        $sql_refund->execute();

        $result['invoice'] = $sql_invoice->fetch();
        $result['payment'] = $sql_payment->fetch();
        $result['refund'] = $sql_refund->fetch();

        return $result;
    }
}