Strange behaiviour with parent products

Hi! a client asked help in his site because they created a coupon with 30% discount in an specific category. The site is using J! 3.10.0 and VM 4.0.6

The coupon definition is as follows:

In the image below the highligted product is the only product that belongs to cat ID 335, but coupon was applied to all products but no to the first one, that's really weird. Somehow my VM DB has smoething with products 2 to 6 that makes Awo Coupon to apply discount even if they are not in the included category

As a solution and after a lot of debugging I came up with this fix that I want to share here, I modified function get_storecategory in administrator/components/com_awocoupon/helper/estore/virtuemart/class-awocoupon-helper-estore-virtuemart-discount.php. Basically I separated product ids to parent product ids and child product ids

            // Check if product is parent
            if ($this->get_product($product_id)->product_parent_id == 0) {
                $product_parent_ids[ $product_id ] = (int) $product_id;
            } else {
                $product_child_ids[ $product_id ] = (int) $product_id;
            }

and      

  $sql = 'SELECT virtuemart_category_id AS asset_id,virtuemart_product_id AS product_id
                  FROM #__virtuemart_product_categories
                 WHERE virtuemart_product_id IN (' . implode( ',', $product_parent_ids ) . ')';
        $cats1 = AC()->db->get_objectlist( $sql );
        $cats2 = array();
        $cats3 = array();
        if ( $this->params->get( 'disable_coupon_product_children', 0 ) != 1 && !empty($product_child_ids)) {
            // get category list of parent products
            $sql = 'SELECT c.virtuemart_category_id AS asset_id,p.virtuemart_product_id AS product_id
                      FROM #__virtuemart_products p
                      JOIN #__virtuemart_product_categories c ON c.virtuemart_product_id=p.product_parent_id
                     WHERE p.virtuemart_product_id IN (' . implode( ',', $product_child_ids ) . ')';
            $cats2 = AC()->db->get_objectlist( $sql );

            // get category list of parent products second level
            $sql = 'SELECT c.virtuemart_category_id AS asset_id,p.virtuemart_product_id AS product_id
                      FROM #__virtuemart_products p
                      JOIN #__virtuemart_products p2 ON p2.virtuemart_product_id=p.product_parent_id
                      JOIN #__virtuemart_product_categories c ON c.virtuemart_product_id=p2.product_parent_id
                     WHERE p.virtuemart_product_id IN (' . implode( ',', $product_child_ids ) . ')';
            $cats3 = AC()->db->get_objectlist( $sql );
        }

To add more to the confussion, I tested the same coupon in another site and it works just fine. It doesn't applies coupon to products outside the included cat...

Here is the modified function

protected function get_storecategory( $ids ) {
        $product_parent_ids = array();
        $product_child_ids = array();
        $tmp = explode( ',', $ids );
        $definitions = array();
        foreach ( $tmp as $row ) {
            $row = urldecode( $row );
            if ( ( $pos = strpos( $row, '|' ) ) === false ) {
                continue;
            }
           
            $product_id = (int) substr( $row, 0, $pos );
            $attributes = substr( $row, $pos + 1 );

            // Check if product is parent
            if ($this->get_product($product_id)->product_parent_id == 0) {
                $product_parent_ids[ $product_id ] = (int) $product_id;
            } else {
                $product_child_ids[ $product_id ] = (int) $product_id;
            }

            $definitions[ $product_id ][ $attributes ] = $attributes;
        }
        if ( empty( $product_parent_ids ) ) {
            return array();
        }

        $sql = 'SELECT virtuemart_category_id AS asset_id,virtuemart_product_id AS product_id
                  FROM #__virtuemart_product_categories
                 WHERE virtuemart_product_id IN (' . implode( ',', $product_parent_ids ) . ')';
        $cats1 = AC()->db->get_objectlist( $sql );
        $cats2 = array();
        $cats3 = array();
        if ( $this->params->get( 'disable_coupon_product_children', 0 ) != 1 && !empty($product_child_ids)) {
            // get category list of parent products
            $sql = 'SELECT c.virtuemart_category_id AS asset_id,p.virtuemart_product_id AS product_id
                      FROM #__virtuemart_products p
                      JOIN #__virtuemart_product_categories c ON c.virtuemart_product_id=p.product_parent_id
                     WHERE p.virtuemart_product_id IN (' . implode( ',', $product_child_ids ) . ')';
            $cats2 = AC()->db->get_objectlist( $sql );

            // get category list of parent products second level
            $sql = 'SELECT c.virtuemart_category_id AS asset_id,p.virtuemart_product_id AS product_id
                      FROM #__virtuemart_products p
                      JOIN #__virtuemart_products p2 ON p2.virtuemart_product_id=p.product_parent_id
                      JOIN #__virtuemart_product_categories c ON c.virtuemart_product_id=p2.product_parent_id
                     WHERE p.virtuemart_product_id IN (' . implode( ',', $product_child_ids ) . ')';
            $cats3 = AC()->db->get_objectlist( $sql );
        }

        $categorys = array_merge( $cats1, $cats2, $cats3 );
        if ( empty( $categorys ) ) {
            return array();
        }

        $category_index = array();
        foreach ( $categorys as $cat ) {
            if ( ! isset( $category_index[ $cat->asset_id ] ) ) {
                $category_index[ $cat->asset_id ] = array();
            }
            $category_index[ $cat->asset_id ][] = $cat->product_id;
        }

        // get parent categories
        for ( $i = 0; $i < 10; $i ++ ) {
            if ( empty( $category_index ) ) {
                break;
            }
            $sql = 'SELECT category_parent_id, category_child_id FROM #__virtuemart_category_categories WHERE category_child_id IN (' . implode( ',', array_keys( $category_index ) ) . ')';
            $items = AC()->db->get_objectlist( $sql );
            $tmp_category_index = array();
            foreach ( $items as $item ) {
                if ( empty( $item->category_parent_id ) ) {
                    continue;
                }
                foreach ( $category_index[ $item->category_child_id ] as $product_id ) {
                    $categorys[] = (object) array(
                        'asset_id' => $item->category_parent_id,
                        'product_id' => $product_id,
                    );
                }
                //$tmp_category_index[ $item->category_parent_id ] = $category_index[ $item->category_child_id ];
                if ( ! isset( $tmp_category_index[ $item->category_parent_id ] ) ) {
                    $tmp_category_index[ $item->category_parent_id ] = array();
                }
                $tmp_category_index[ $item->category_parent_id ] = array_merge( $tmp_category_index[ $item->category_parent_id ], $category_index[ $item->category_child_id ] );
            }
            $category_index = $tmp_category_index;
        }

        $rtn = array();
        foreach( $categorys as $row ) {
            foreach( $definitions[ $row->product_id ] as $attributes ) {
                $row2 = clone( $row );
                $row2->definition_id = urlencode( $row2->product_id . '|' . $attributes );
                $rtn[] = $row2;
            }
        }
        return $rtn;
       
    }