<?php
/**
 * Created by PhpStorm.
 * User: GRAPHICBOX
 * Date: 20.06.2017
 * Time: 10:45
 */
$servername = "127.0.0.1";
$username = "root";
$password = "";

// Create connection
$conn = new mysqli($servername, $username, $password,"polampy");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
$conn->set_charset("utf8");

$productsSql = "SELECT * FROM products ";

$products = $conn->query($productsSql);


$attributesSql = "SELECT * FROM attributes";
$attributesResult = $conn->query($attributesSql);

$attributes = [];
while($attributeRow = $attributesResult->fetch_assoc())
{
    $attributes[] = $attributeRow;
}

$outArr = [];
while($product = $products->fetch_assoc())
{
    $prod = new Product(count($attributes));
    $productId = $product['product_id'];

    $stockSql = "SELECT * FROM products_stock WHERE product_id=".$productId;
    $stock = $conn->query($stockSql)->fetch_assoc();


    $prod->code = $stock['code'];


    $prod->productId = $productId;

    $translationSql = "SELECT * FROM products_translations WHERE product_id = ".$productId;
    $translation = $conn->query($translationSql)->fetch_assoc();

    $prod->name = n($translation['name']);

    $prod->description = n(strip_tags($translation['description']));

    $categorySql = "SELECT * FROM products_categories WHERE product_id = ".$productId;

    $category = $conn->query($categorySql)->fetch_assoc();


    $categoryId = $category['category_id'];

    fillWithCategories($categoryId,$prod,$conn);
    fillWithAttributes($prod,$conn,$attributes);
    fillWithPhotos($prod,$conn);
//    $categoryMap = $conn->query($categoryMapSql)->fetch_assoc();

$outArr[] = $prod;
    print_r($prod);
//    die();
//    die();

}


$file = __DIR__."/polamp.csv";
//file_put_contents($file,"");

$firstRow = ["Kod","Nazwa","Opis","Kategoria główna", "Podkategoria 1","Podkategoria 2","Podkategoria 3"];
foreach($attributes as $attr)
{
    $firstRow[] = $attr['name'];
}
$firstRow[] = "Zdjęcia";

file_put_contents($file,implode(";",$firstRow)."\n");
/** @var Product $item */
foreach($outArr as $item)
{
    $outRowArr = [];
    $outRowArr[] = n($item->code);
    $outRowArr[] = n($item->name);
    $outRowArr[] = str_replace(["Do pobrania:Karta katalogowaCertyfikat CE","Do pobrania:Karta katalogowa Certyfikat CE","Do pobrania:Certyfikat CE","Do pobraniaKarta katalogowa Certyfikat CE","Do pobrania:  Karta katalogowa Certyfikat CE","Do pobrania:Karta katalogowaCertyfikat CE "],["","","","","",""],n($item->description));
//    $descr = mb_ereg_replace('/\s\s+/', ' ',$descr);
//    $outRowArr[] = mb_ereg_replace('/\t+/', ' ',$descr);
    $outRowArr[] = n(implode(";",$item->categories));
    $outRowArr[] = n(implode(";",$item->attributes));
    $outRowArr[] = n(implode(", ",$item->photos));

    file_put_contents($file,implode(";",$outRowArr)."\n",FILE_APPEND);
}


function fillWithCategories($mainCategoryId,Product $prod, mysqli $conn)
{
    $categoryMapSql = "SELECT * FROM categories_map WHERE child_id=".$mainCategoryId." ORDER BY level ASC";

    $map = $conn->query($categoryMapSql);

//    $categoriesCount = $map->num_rows;

    $i = 0;
    while($mapRow = $map->fetch_assoc())
    {
        $categoryId = $mapRow['category_id'];
        $categorySql = "SELECT * FROM categories_translations WHERE category_id=".$categoryId;
        $category = $conn->query($categorySql)->fetch_assoc();
//        $index = $categoriesCount - $mapRow['level'] - 1;
        $prod->categories[$i] = $category['name'];
        $i++;
    }

}

function fillWithPhotos(Product $prod, mysqli $conn)
{
    $photosSql = "SELECT * FROM products_gfx WHERE product_id=".$prod->productId." ORDER BY main DESC";
    $photos = $conn->query($photosSql);
    $i = 1;
    while($photo = $photos->fetch_assoc())
    {

        $name = $prod->code."_zdjecie".$i;
        $photoLink = "http://polampy.pl/export_images/images/".$name.".jpg";
        $prod->photos[] = $photoLink;
//        downloadImage($photo['unic_name'],$prod->code."_zdjecie".$i);
        $i++;
    }
}
function downloadImage($name,$outName)
{
    $photoLink = "http://polampy.pl/environment/cache/images/0_0_productGfx_".$name.".jpg";
    $content = file_get_contents($photoLink);
    file_put_contents(__DIR__."/images/".$outName.".jpg",$content);
    echo "\n image:".$name;
}

function fillWithAttributes(Product $prod, mysqli $conn, $attributes)
{
    $attributesSql = "SELECT * FROM attributes_values WHERE product_id=".$prod->productId;
    $attrs = $conn->query($attributesSql);
    while($attr = $attrs->fetch_assoc())
    {
//        $attributeInfoSql = "SELECT * FROM attributes WHERE attribute_id=".$attr['attribute_id'];
//        $attrInfo = $conn->query($attributeInfoSql)->fetch_assoc();
//        $prod->attributes[] = $attrInfo['name'].":".$attr['value'];

        for($i = 0; $i < count($attributes); $i++)
        {
            $attribute = $attributes[$i];
            if($attribute['attribute_id'] == $attr['attribute_id'])
            {
                $prod->attributes[$i] = $attr['value'];
            }
        }
    }
}





function n($string)
{
    return str_replace(["\n","\r"],[" "," "],$string);
}

class Product
{

    public function __construct($attrCount)
    {
        $this->attributes = array_fill(0,$attrCount,"");
        $this->categories = array_fill(0,4,"");
    }

    public $productId;

    public $code;

    public $name;

    public $description;

    public $categories;

    public $attributes;

    public $photos = [];

    public $catalog;

    public $ce;
}