<?php

namespace RxConnexionBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Form\Extension\Core\DataTransformer\DateTimeToStringTransformer;

class PurchaseInvoiceRepository extends EntityRepository
{
    public function ajax_list(array $get, $userData){

        $columns = array(
            array('hf.`description`', 'hf.`description`', 'branch_desc'),
            array('s.`description`', 's.`description`', 'supplier_desc'),
            array('pob.`po_no`', 'pob.`po_no`'),
            array('pob.`payment_method`', 'pob.`payment_method`'),
            array('pobi.noOfItems', 'pobi.noOfItems'),
            array('pobi.totalAmount', 'pobi.totalAmount'),
            array('pi.`status`', "pi.`status`"),
            array('pobi.quantityAdded', 'pobi.quantityAdded' ,'quantityAdded'),
            array('pi.`id`', "pi.`id`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `purchase_invoice` pi";
        $joins = "
            LEFT JOIN `purchase_order_breakdown` pob ON pi.`purchase_order_breakdown_id` = pob.`id`
            LEFT JOIN `purchase_order` po ON pob.`purchase_order_id` = po.`id`
            INNER JOIN material_requisition mr ON po.material_requisition_id = mr.id
            INNER JOIN health_facility hf ON mr.health_facility_id = hf.id
            LEFT JOIN `supplier` s ON po.`supplier_id` = s.`id`
            LEFT JOIN (
                SELECT
                    pobi.`purchase_order_breakdown_id`,
                    pobi.`quantity_added` as quantityAdded ,
                    COUNT(DISTINCT pobi.`id`) AS noOfItems,
                    SUM(poi.`price` * (pobi.`quantity` - pobi.`quantity_voided`)) AS totalAmount
                FROM `purchase_order_breakdown_item` pobi
                LEFT JOIN `purchase_order_item` poi ON pobi.`purchase_order_item_id` = poi.`id`
                GROUP BY pobi.`purchase_order_breakdown_id`
            ) pobi ON pi.`purchase_order_breakdown_id` = pobi.`purchase_order_breakdown_id`
        ";


        $sqlWhere = "";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        if(isSet($get['status']) && $get['status'] !== 'all') {
            if($get['status'] == 'Active') {
                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . 'pi.`status` IN (:status1)';
                $stmtParams['status1'] = 'Pending';
            } elseif($get['status'] == 'Closed') {
                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . 'pi.`status` IN (:status1)';
                $stmtParams['status1'] = 'Completed';
            }
        }

	    if('Super Admin' !== $userData['type']) {
		    if(count($userData['branches'])) {
			    $ors = '';
			    foreach($userData['branches'] as $key => $branchId) {
				    $ors .= (!empty($ors) ? ' OR ' : '') . 'mr.`health_facility_id` = :branch_id_' . $key;
				    $stmtParams['branch_id_' . $key] = $branchId;
			    }
			    $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . "($ors)";
		    } else { // Don't show anything
			    $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . "mr.`health_facility_id` < 0";
		    }
	    }


        foreach($columns as $key => $column) {
            $select .= ($key > 0 ? ', ' : ' ') . $column[1] . (isset($column[2]) ? ' AS ' . $column[2] : '');
        }

        /*
         * Ordering
         */
        foreach($get['columns'] as $key => $column) {
            if($column['orderable']=='true') {
                if(isSet($get['order'])) {
                    foreach($get['order'] as $order) {
                        if($order['column']==$key) {
                            $orderBy .= (!empty($orderBy) ? ', ' : 'ORDER BY ') . $columns[$key][0] . (!empty($order['dir']) ? ' ' . $order['dir'] : '');
                        }
                    }
                }
            }
        }

        /*
         * Filtering
         */
        if(isset($get['search']) && $get['search']['value'] != ''){
            $aLikes = array();
            foreach($get['columns'] as $key => $column) {
                if($column['searchable']=='true') {
                    $aLikes[] = $columns[$key][0] . ' LIKE :searchValue';
                }
            }
            foreach($asColumns as $asColumn) {
                $aLikes[] = $asColumn . ' LIKE :searchValue';
            }
            if(count($aLikes)) {
                $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . '(' . implode(' OR ', $aLikes) . ')';
                $stmtParams['searchValue'] = "%" . $get['search']['value'] . "%";
            }
        }

        /* Set Limit and Length */
        if(isset( $get['start'] ) && $get['length'] != '-1'){
            $limit = 'LIMIT ' . (int)$get['start'] . ',' . (int)$get['length'];
        }

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy";
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);
        $result_count = $query->fetchAll();

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy $limit";
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);
        $result = $query->fetchAll();

        /* Data Count */
        $recordsTotal = count($result_count);

        /*
         * Output
         */
        $output = array(
            "draw" => intval($get['draw']),
            "recordsTotal" => $recordsTotal,
            "recordsFiltered" => $recordsTotal,
            "data" => array()
        );

        $url = $get['url'];
        $viewUrl  = $url . '/admin/material_requisition/purchase_invoice/view/';

        foreach($result as $row) {

            $id = base64_encode($row['id']);

            $values = array(
                $row['branch_desc'],
                $row['supplier_desc'],
                $row['po_no'],
                $row['payment_method'],
                $row['noOfItems'],
                number_format($row['totalAmount'], 2),
                $row['status'],
                $row['quantityAdded'],
                '<a href="'.$viewUrl . $id.'" class="action-button-style">View</a>'
            );

            $output['data'][] = $values;
        }

        unset($result);

        return $output;
    }

    //pinabago itong query
    /* public function reportPurchaseInvoice($userData, $date_from=null, $date_to=null){
         $stmtParams = array();
         $sqlWhere = 'WHERE (pi.`status` = :status)';
         $stmtParams['status'] = 'Pending Payment';

         /*if (!is_null($date_from) && $date_from !== 'null' && $date_from !== 'all' && !empty($date_from) && !is_null($date_to) && $date_to !== 'null' && $date_to !== 'all' && !empty($date_to)) {

             $sqlWhere .= ' AND pi.`created_at` BETWEEN :date_from AND :date_to';
             $stmtParams['date_from'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_from))) . ' 00:00:00';
             $stmtParams['date_to'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_to))) . ' 00:00:00';
         }*/


    /*     $sql = "SELECT
                    DATE_FORMAT(pi.`created_at`, '%m/%d/%Y') as purchaseDate,
                    pi.`id`,
                    s.`description` as supplier,
                    pob.`purchase_order_no` as purchaseOrderNo,
                    credit,
                    creditPayment,
                    IF(creditPayment > 0, credit - creditPayment, credit) as balance,
                    pobi.`invoice_no` as invoiceNo
                 FROM `purchase_invoice` pi
                 LEFT JOIN `purchase_order_breakdown` pob ON pob.`id` = pi.`purchase_order_breakdown_id`
                 LEFT JOIN `purchase_order` po ON po.`id` = pob.`purchase_order_id`
                 LEFT JOIN `supplier` s ON s.`id` = po.`supplier_id`
                 LEFT JOIN `purchase_order_breakdown_invoice` pobi ON pobi.`purchase_order_breakdown_id` = pi.`purchase_order_breakdown_id`
                 LEFT JOIN (
                   SELECT sum(pip.`amount`) as credit,pip.`amount`, pip.`payment_method`, pip.`purchase_invoice_id`
                   FROM `purchase_invoice_payment` pip
                   WHERE pip.`payment_method` = 'Credit'
                   GROUP BY pip.`purchase_invoice_id`
                 ) c ON c.`purchase_invoice_id` = pi.`id`
                 LEFT JOIN (
                   SELECT sum(picp.`amount`) as creditPayment, picp.`amount`, picp.`payment_method`, picp.`purchase_invoice_id`
                   FROM `purchase_invoice_credit_payment` picp
                   GROUP BY picp.`purchase_invoice_id`
                 ) credit_payment ON credit_payment.`purchase_invoice_id` = pi.`id`
                 $sqlWhere
                 GROUP BY pi.`id`
                 ";*/

    /*    $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $result = $stmt->fetchAll();
        return $result;
    }*/

    public function reportPurchaseInvoice($userData, $date_from=null, $date_to=null){
        $stmtParams = array();
        //$sqlWhere = '';
        $sqlWhere = 'WHERE (pi.`status` != :status)';
        $stmtParams['status'] = 'Completed';

        $sqlWhere .= ' AND (po.`status` = :po_status)';
        $stmtParams['po_status'] = 'Received';

        $sqlWhere .= ' AND IF(totalPrice > 0 , totalPrice ,0) - (IF(payment > 0, payment , 0) + IF(payment2 > 0 , payment2, 0)) > 0';

//        $sqlWhere .= ' AND (IF(sum(totalPrice) > 0 , sum(totalPrice) , 0) - (IF(sum(IF(pip.`amount` IS NOT NULL ,pip.`amount` , 0)) > 0 , sum(IF(pip.`amount` IS NOT NULL ,pip.`amount` , 0)), 0 ) + IF( sum(IF(picp.`amount` IS NOT NULL ,picp.`amount` , 0)) > 0 ,  sum(IF(picp.`amount` IS NOT NULL ,picp.`amount` , 0)), 0 )) > 0)';

        /*if (!is_null($date_from) && $date_from !== 'null' && $date_from !== 'all' && !empty($date_from) && !is_null($date_to) && $date_to !== 'null' && $date_to !== 'all' && !empty($date_to)) {

            $sqlWhere .= ' AND pi.`created_at` BETWEEN :date_from AND :date_to';
            $stmtParams['date_from'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_from))) . ' 00:00:00';
            $stmtParams['date_to'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_to))) . ' 00:00:00';
        }*/


        $sql = "SELECT
                   DATE_FORMAT(pi.`created_at`, '%m/%d/%Y') as purchaseDate,
                   po.`status`,
                   pi.`id`,
                   s.`description` as supplier,
                   pob.`purchase_order_no` as purchaseOrderNo,
                   payment,
                   payment2,
                   IF(payment > 0, payment , 0) + IF(payment2 > 0 , payment2, 0) as creditPayment,
                   totalPrice as credit,
                   invoiceNo,
                   IF(totalPrice > 0 , totalPrice ,0) - (IF(payment > 0, payment , 0) + IF(payment2 > 0 , payment2, 0)) as balance
				FROM `purchase_invoice` pi
				LEFT JOIN `purchase_order_breakdown` pob ON pob.`id` = pi.`purchase_order_breakdown_id`
				LEFT JOIN (
				  SELECT
				    pobii.`purchase_order_item_id`,
				    pobii.`purchase_order_breakdown_id`,
				    pobii.`quantity` as totalQuantity,
				    SUM((IF(pobii.`quantity` IS NOT NULL, pobii.`quantity`, 0) + IF(pobii.`quantity_added` IS NOT NULL, pobii.`quantity_added`, 0)) * IF(poi.`price` IS NOT NULL, poi.`price`, 0)) as totalPrice 
				  FROM `purchase_order_breakdown_item` pobii
				  LEFT JOIN `purchase_order_item` poi ON poi.`id` = pobii.`purchase_order_item_id`
				  GROUP BY  pobii.`purchase_order_breakdown_id`
				) pobii ON pobii.`purchase_order_breakdown_id` = pob.`id` 
				LEFT JOIN `purchase_order` po ON po.`id` = pob.`purchase_order_id`
				LEFT JOIN `supplier` s ON s.`id` = po.`supplier_id`
				LEFT JOIN  (
				  SELECT 
				    pobi.`purchase_order_breakdown_id`,
				    GROUP_CONCAT(pobi.`invoice_no` SEPARATOR ', ') as invoiceNo
				  FROM (
				    SELECT *
				    FROM `purchase_order_breakdown_invoice`
				    GROUP BY `invoice_no`
				  ) pobi 
				  GROUP BY pobi.`purchase_order_breakdown_id` 
				) pobi ON pobi.`purchase_order_breakdown_id` = pi.`purchase_order_breakdown_id`
				LEFT JOIN (
				  SELECT 
				   pip.`purchase_invoice_id`,
				   sum(IF(pip.`amount` IS NOT NULL ,pip.`amount` , 0)) as payment
				  FROM `purchase_invoice_payment` pip
				  WHERE pip.`payment_method` != 'Credit'
				  GROUP BY pip.`purchase_invoice_id`
				) pip  ON pip.`purchase_invoice_id` = pi.`id`
				LEFT JOIN (
				  SELECT 
				    picp.`purchase_invoice_id`,
				    sum(IF(picp.`amount` IS NOT NULL ,picp.`amount` , 0)) as payment2
				  FROM `purchase_invoice_credit_payment` picp 
				  GROUP BY picp.`purchase_invoice_id`
				) picp ON picp.`purchase_invoice_id` = pi.`id`
				$sqlWhere
				";

        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $result = $stmt->fetchAll();
        return $result;
    }

    function reportDisbursement($userData, $date_from, $date_to){

        $stmtParams = array();
       /* $sqlWhere = 'WHERE (pi.`status` != :status)';
        $stmtParams['status'] = 'Completed';*/
        /*if (!is_null($date_from) && $date_from !== 'null' && $date_from !== 'all' && !empty($date_from) && !is_null($date_to) && $date_to !== 'null' && $date_to !== 'all' && !empty($date_to)) {

            $sqlWhere .= ' AND pi.`created_at` BETWEEN :date_from AND :date_to';
            $stmtParams['date_from'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_from))) . ' 00:00:00';
            $stmtParams['date_to'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_to))) . ' 00:00:00';
        }*/


        $sql = "
            SELECT
              paymentCreditCheckNo,
              paymentCreditCheckDate,
              picp.`cleared_date`,
               picp.`amount` as paymentCreditAmount,
              DATE_FORMAT(pi.`created_at`, '%m/%d/%Y') as purchaseDate,
              pi.`id`,
              s.`description` as supplier,
              pob.`purchase_order_no` as purchaseOrderNo,
              pob.`purchase_order_id` as purchase_order_id
            FROM `purchase_invoice` pi
            LEFT JOIN `purchase_order_breakdown` pob ON pi.`purchase_order_breakdown_id` = pob.`id` 
            LEFT JOIN `purchase_order` po ON pob.`purchase_order_id` = po.`id` 
            LEFT JOIN `supplier` s ON po.`supplier_id` = s.`id`
             LEFT JOIN(
              SELECT
                picp.`cleared_date`,
                picp.`amount`,
                picp.`check_no` as paymentCreditCheckNo,
                DATE_FORMAT(picp.`check_date`, '%m/%d/%Y') as paymentCreditCheckDate,
                picp.`purchase_invoice_id`,
                picp.`id` as purchaseInvoicePaymentCreditId
              FROM `purchase_invoice_credit_payment` picp
              WHERE picp.`payment_method` = 'Check'
            ) picp  ON   pi.`id` = picp.`purchase_invoice_id` 
            WHERE pi.`status` != 'Completed'
             AND picp.`cleared_date` IS NULL
              AND picp.`amount` > 0
         ";

        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $creditPaymentResult = $stmt->fetchAll();

        $sql = "
            SELECT
              pip.`amount` as paymentAmount,
              paymentCheckNo,
              paymentCheckDate,
              pip.`cleared_date`,
              DATE_FORMAT(pi.`created_at`, '%m/%d/%Y') as purchaseDate,
              pi.`id`,
              s.`description` as supplier,
              pob.`purchase_order_no` as purchaseOrderNo,
              pob.`purchase_order_id` as purchase_order_id
            FROM `purchase_invoice` pi
            LEFT JOIN `purchase_order_breakdown` pob ON pi.`purchase_order_breakdown_id` = pob.`id` 
            LEFT JOIN `purchase_order` po ON  pob.`purchase_order_id` = po.`id`
            LEFT JOIN `supplier` s ON po.`supplier_id` = s.`id`
             LEFT JOIN(
              SELECT
                pip.`amount`,
                pip.`check_no` as paymentCheckNo,
                DATE_FORMAT(pip.`check_date`, '%m/%d/%Y') as paymentCheckDate,
                pip.`purchase_invoice_id`,
                pip.`cleared_date`,
                pip.`id` as purchaseInvoicePaymentId
              FROM `purchase_invoice_payment` pip
              WHERE pip.`payment_method` = 'Check'
            ) pip  ON  pip.`purchase_invoice_id` = pi.`id`
            WHERE pi.`status` != 'Completed'
            AND pip.`cleared_date` IS NULL
            AND pip.`amount` > 0
        
         ";

        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $paymentResult = $stmt->fetchAll();

        $result = array('creditPayment' => $creditPaymentResult , 'payment' => $paymentResult);

        return $result;
    }



    //old report
    // function reportDisbursement($userData, $date_from, $date_to){

    //   $stmtParams = array();
    // $sqlWhere = 'WHERE (pi.`status` != :status)';
    // $stmtParams['status'] = 'Completed';
    /*if (!is_null($date_from) && $date_from !== 'null' && $date_from !== 'all' && !empty($date_from) && !is_null($date_to) && $date_to !== 'null' && $date_to !== 'all' && !empty($date_to)) {

        $sqlWhere .= ' AND pi.`created_at` BETWEEN :date_from AND :date_to';
        $stmtParams['date_from'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_from))) . ' 00:00:00';
        $stmtParams['date_to'] = date('Y-m-d', strtotime(str_replace('-', '/', $date_to))) . ' 00:00:00';
    }*/

    /*
            $sql = "SELECT
                       DATE_FORMAT(pi.`created_at`, '%m/%d/%Y') as purchaseDate,
                       pi.`id`,
                       s.`description` as supplier,
                       pob.`purchase_order_no` as purchaseOrderNo,
                       payment,
                       creditPayment,
                       paymentCheckNO,
                       PaymentCreditCheckNo,
                       paymentCreditCheckDate,
                       pobi.`invoice_no` as invoiceNo,
                       pip.`id`,
                       picp.`id`,
                       pob.`purchase_order_id` as purchase_order_id
                    FROM `purchase_invoice` pi
                    LEFT JOIN `purchase_order_breakdown` pob ON pob.`id` = pi.`purchase_order_breakdown_id`
                    LEFT JOIN `purchase_order` po ON po.`id` = pob.`purchase_order_id`
                    LEFT JOIN `supplier` s ON s.`id` = po.`supplier_id`
                    LEFT JOIN `purchase_order_breakdown_invoice` pobi ON pobi.`purchase_order_breakdown_id` = pi.`purchase_order_breakdown_id`
                    LEFT JOIN (
                      SELECT
                         IF(pip.`amount` IS NOT NULL, pip.`amount`, 0 ) as payment,
                         pip.`check_no` as paymentCheckNo,
                         DATE_FORMAT(pip.`check_date`, '%m/%d/%Y') as paymentCheckDate,
                         pip.`payment_method`,
                         pip.`purchase_invoice_id`,
                         pip.`id`,
                         pip.`cleared_date
                      FROM `purchase_invoice_payment` pip
                      WHERE pip.`payment_method` = 'Check'
                      AND pip.`cleared_date` IS NULL
                      AND pip.`amount` > 0
                    ) pip ON pip.`purchase_invoice_id` = pi.`id`
                    LEFT JOIN (
                      SELECT
                         IF(picp.`amount` IS NOT NULL, picp.`amount`, 0 ) as creditPayment,
                         picp.`check_no` as paymentCreditCheckNo,
                         DATE_FORMAT(picp.`check_date`, '%m/%d/%Y') as paymentCreditCheckDate,
                         picp.`amount`,
                         picp.`payment_method`,
                         picp.`purchase_invoice_id`,
                         picp.`id`,
                         picp.`cleared_date`
                      FROM `purchase_invoice_credit_payment` picp
                      WHERE picp.`payment_method` = 'Check'
                      AND picp.`cleared_date` IS NULL
                      AND picp.`amount` > 0
                    ) picp ON picp.`purchase_invoice_id` = pi.`id`
                    $sqlWhere
                    ";

            $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
            $stmt->execute($stmtParams);
            $result = $stmt->fetchAll();
            return $result;*/
    //}
}