Strange behaiviour with parent products


  • Default avatar
    ghiamar    
     23 days ago
    0

    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:
    coupon details

    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

    Discount applyed to products outside 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;
           
        }