<?php
namespace VendorModuleModelResourceModelBrandwisereport;
//use VendorModuleModelTempdataFactory;

class Collection extends MagentoFrameworkModelResourceModelDbCollectionAbstractCollection
{

    /**
     * Define resource model
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('VendorModuleModelBrandwisereport', 'VendorModuleModelResourceModelBrandwisereport');
        $this->_map['fields']['page_id'] = 'main_table.page_id';
        $this->addFilterToMap('brand', 'eavatt.value');
        $this->addFilterToMap('sku', 'main_table.sku');
        $this->addFilterToMap('name', 'main_table.name');

        //$this->_modelTempdataFactory = $modelTempdataFactory;
        //$this->authSession = $authSession;

    }

    public function filterOrder()
    {

    $catalog_product_entity_table = $this->getTable("catalog_product_entity");
    $catalog_product_entity_inttable = $this->getTable("catalog_product_entity_int");
    $eav_attributeoptionvalue = $this->getTable("eav_attribute_option_value");
    $sales_order = $this->getTable("sales_order");
    $sales_order_address = $this->getTable("sales_order_address");


    $this->getSelect()->joinLeft(array('cpe' =>$catalog_product_entity_table), 'main_table.product_id = cpe.entity_id')
    ->columns(array('sum_qty_ordered' => new Zend_Db_Expr("SUM(main_table.qty_ordered)"), 'total_amount' => new Zend_Db_Expr("SUM(main_table.base_row_total)")))
    ->joinLeft(array('cpei' =>$catalog_product_entity_inttable),'cpei.row_id= cpe.row_id
      AND cpei.attribute_id = "83" AND cpei.store_id = 0')
    ->joinLeft(array('eavatt' =>$eav_attributeoptionvalue), 'eavatt.option_id= cpei.value',array('brand' => 'eavatt.value'))
    ->joinLeft(array('so' =>$sales_order), 'so.entity_id= main_table.order_id',array('status' => 'so.status'))
    ->joinLeft(array('soa' =>$sales_order_address), 'soa.parent_id= main_table.order_id AND soa.address_type = "shipping"',array('shippingcity' => 'soa.city'))
    ->group('cpe.entity_id');

    $this->getSelect()->where("cpe.sku IS NOT NULL");

    $authSession = MagentoFrameworkAppObjectManager::getInstance()->create(MagentoBackendModelAuthSession::class);
    $username = $authSession->getUser()->getUsername();
    $TempdataModel = MagentoFrameworkAppObjectManager::getInstance()->create('VendorModuleModelTempdataFactory')->create();


    $TempdataCollection = $TempdataModel->getCollection()->addFieldToFilter('identifier','brandwisereport')->addFieldToFilter('data_1',$username);

    if($TempdataCollection->count()) {
        foreach ($TempdataCollection as $Tempdata){
            $datearray = explode('_',$Tempdata->getData2());
            $orderstatuses = $Tempdata->getData3();
            $brand = $Tempdata->getData4();
        }
        $orderstatusesarray = explode(',',$orderstatuses);
        $orderstatusesforquery = implode("','",$orderstatusesarray);


        $from  = date('Y-m-d', strtotime($datearray[0]));
        $to = date('Y-m-d', strtotime($datearray[1]));

        $this->getSelect()->where("main_table.created_at >= '".$from." 00:00:00'");
        $this->getSelect()->where("main_table.created_at <= '".$to." 23:59:59'");
        $this->getSelect()->where("so.status IN ('$orderstatusesforquery')");
        $this->getSelect()->where('eavatt.value LIKE "'.$brand.'"');
    }
    else {
        $this->getSelect()->where("cpe.entity_id = 0");
    }
    //echo $this->getSelect();
    //die;
   }

}
?>

<?php
namespace VendorModuleBlockAdminhtmlBrandwisereport;

class Grid extends MagentoBackendBlockWidgetGridExtended
{

    protected $_countTotals = true;
    /**
     * @var MagentoFrameworkModuleManager
     */
    protected $moduleManager;

    /**
     * @var VendorModuleModelbrandwisereportFactory
     */
    protected $_brandwisereportFactory;

    /**
     * @var VendorModuleModelStatus
     */
    protected $_status;

    /**
     * @param MagentoBackendBlockTemplateContext $context
     * @param MagentoBackendHelperData $backendHelper
     * @param VendorModuleModelbrandwisereportFactory $brandwisereportFactory
     * @param VendorModuleModelStatus $status
     * @param MagentoFrameworkModuleManager $moduleManager
     * @param array $data
     *
     * @SuppressWarnings(PHPMD.ExcessiveParameterList)
     */
    public function __construct(
        MagentoBackendBlockTemplateContext $context,
        MagentoBackendHelperData $backendHelper,
        VendorModuleModelBrandwisereportFactory $BrandwisereportFactory,
        VendorModuleModelStatus $status,
        MagentoFrameworkModuleManager $moduleManager,
        array $data = []
    ) {
        $this->_brandwisereportFactory = $BrandwisereportFactory;
        $this->_status = $status;
        $this->moduleManager = $moduleManager;
        parent::__construct($context, $backendHelper, $data);
    }

    /**
     * @return void
     */
    protected function _construct()
    {
        parent::_construct();
        $this->setId('postGrid');
        $this->setDefaultSort('item_id');
        $this->setDefaultDir('DESC');
        $this->setSaveParametersInSession(true);
        $this->setUseAjax(false);
        //$this->setVarNameFilter('post_filter');
        $this->setFilterVisibility(false);
        //$this->buttonList->remove('selectall');
        //$this->buttonList->remove('save');
        //$this->buttonList->remove('reset');
        //$this->_removeButton('selectall');
        //$this->buttonList->remove('delete');
    }

    public function getTotals()
    {
        $totals = new MagentoFrameworkDataObject;
        $fields = array(
            'sum_qty_ordered' => 0,
            'base_price' => 0,
            'total_amount' => 0
        );
        foreach ($this->getCollection() as $item) {
            foreach($fields as $field=>$value){
                    $fields[$field] += $item->getData($field);
            }
        }
        $fields['name']='Totals';
        $totals->setData($fields);
        return $totals;
    }


    /**
     * @return $this
     */
    protected function _prepareCollection()
    {
        $collection = $this->_brandwisereportFactory->create()->getCollection();

        /********************** This code will apply filter on grid collection *****/
        $collection->filterOrder();

        $this->setCollection($collection);

        parent::_prepareCollection();

        return $this;
    }

    /**
     * @return $this
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    protected function _prepareColumns()
    {
        $this->addColumn(
            'brand',
            [
                'header' => __('Brand'),
                'index' => 'brand',
                'filter' => false
            ]
        );



                $this->addColumn(
                    'sku',
                    [
                        'header' => __('SKU'),
                        'index' => 'sku',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'name',
                    [
                        'header' => __('Name'),
                        'index' => 'name',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'sum_qty_ordered',
                    [
                        'header' => __('Sold Count'),
                        'type' => 'number',
                        'index' => 'sum_qty_ordered',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'base_price',
                    [
                        'header' => __('Unit Price'),
                        'type' => 'currency',
                        'index' => 'base_price',
                        'filter' => false
                    ]
                );

                $this->addColumn(
                    'total_amount',
                    [
                        'header' => __('Total Amount'),
                        'type' => 'currency',
                        'index' => 'total_amount',
                        'filter' => false,
                        'format' => true
                    ]
                );

                $this->addColumn('action', array(
                    'header' => __('Action'),
                    'width' => '100',
                    'type' => 'action',
                    'getter' => 'getProductId',
                    'actions' => array(
                        array(
                            'caption' => __('View'),
                            'url' => array('base' => 'catalog/product/edit'),
                            'target'=>'_blank',
                            'field' => 'id'
                        )
                    ),
                    'renderer'  => 'VendorModuleBlockAdminhtmlBrandwisereportEditTabRendererBrandwiseviewaction',
                    'filter' => false,
                    'sortable' => false,
                    'index' => 'id',
                    'is_system' => true,
                ));


           $this->addExportType($this->getUrl('module/*/exportCsv', ['_current' => true]),__('CSV'));
           $this->addExportType($this->getUrl('module/*/exportExcel', ['_current' => true]),__('Excel XML'));

        $block = $this->getLayout()->getBlock('grid.bottom.links');
        if ($block) {
            $this->setChild('grid.bottom.links', $block);
        }

        return parent::_prepareColumns();
    }



    /**
     * @return string
     */
    public function getGridUrl()
    {
        return $this->getUrl('module/*/index', ['_current' => true]);
    }

    /**
     * @param VendorModuleModelbrandwisereport|MagentoFrameworkObject $row
     * @return string
     */
    public function getRowUrl($row)
    {
        return '#';
    }
}