<?php

class appExportAttributesPeer {

    const PRODUCT_CODE = 'st_product.code';
    
    public static function doCount(Criteria $c) {
        return ProductPeer::doCount(new Criteria());
    }

    public static function doSelect(Criteria $c) {

        $cr = new Criteria();
        $cr->setLimit($c->getLimit());
        $cr->setOffset($c->getOffset());
        $products = ProductPeer::doSelect($cr);
        $variantSeparator = stConfig::getInstance('appProductAttributeBackend')->get('import_export_variant_separator', '|');

        $results = array();

        $con = Propel::getConnection();

        foreach ($products as $product) {
            $attributes = self::doSelectArrayByProduct($product);

            foreach ($attributes as $attributeId => $attribute) {
                $record = new appExportAttributes();
                $record->setProductCode($product->getCode());
                $record->setProductName($product->getName());
                $record->setAttributeName($attribute['name']);
                $record->setAttributeId($attributeId);

                $sql = "SELECT 
                            GROUP_CONCAT(pav.opt_name SEPARATOR ' $variantSeparator ') as variantName,
                            GROUP_CONCAT(pav.opt_value SEPARATOR ' $variantSeparator ') as variantValue
                        FROM 
                            app_product_attribute_has_variant pahv,
                            app_product_attribute_variant_has_product pavhp,
                            app_product_attribute_variant pav
                        WHERE 
                            pahv.variant_id = pav.id AND
                            pav.id = pavhp.variant_id AND
                            pavhp.PRODUCT_ID = " . $product->getId() . " AND
                            pahv.attribute_id = " . $attributeId;

                $rs = $con->executeQuery($sql);
                $rs->setFetchmode(ResultSet::FETCHMODE_ASSOC);

                $rs->next();
                $row = $rs->getRow();

                $record->setVariantName($row['variantValue']);
                
                if ($attribute['type'] == 'C')
                {
                    $record->setVariantName($row['variantName']);
                }
                
                if ($attribute['type'] == 'B') 
                {
                    $record->setVariantName(isset($row['variantValue']) ? 'Y' : 'N');
                }

                $results[] = $record;
            }
        }

        return $results;
    }

    public static function doSelectOne($c) {
        $c = clone $c;
        return ProductPeer::doSelectOne($c);
    }

    public static function doSelectArrayByProduct($product) {
        $ranges = appProductAttributePeer::doSelectCategoriesRangesByProduct($product);

        if (!$ranges) {
            return array();
        }

        $c = new Criteria();

        foreach ($ranges as $range) {
            $sql = sprintf('%s <= %s AND %s >= %s AND %s = %s', CategoryPeer::LFT, $range[0], CategoryPeer::RGT, $range[1], CategoryPeer::SCOPE, $range[2]);
            $c->addOr(CategoryPeer::LFT, $sql, Criteria::CUSTOM);
        }   

        $c->addJoin(appProductAttributePeer::ID, appProductAttributeHasCategoryPeer::ATTRIBUTE_ID);
        $c->addJoin(appProductAttributeHasCategoryPeer::CATEGORY_ID, CategoryPeer::ID); 
        $c->addGroupByColumn(appProductAttributePeer::ID);

        return appProductAttributePeer::doSelectArray($c, 'pl_PL');     
    }
}