<?php
include "system/library/logo.php";

class ControllerToolLogoStock extends Controller {
    public function index() {
        $this->getAllStocks();
        exit;
        $this->updateQuantities();
    }

    protected function getAllStocks() {
        $logo = new Logo;
        $firmno = FIRMNO;
        $sql = ["tsql" => "SELECT CEKASTOK.KOD, CEKASTOK.AD, SUM(CEKASTOK.Merkez) 'Merkez', SUM(CEKASTOK.Optimum) 'Optimum', SUM(CEKASTOK.Citys) 'Citys' FROM 		
            (SELECT ITM.CODE 'KOD', ITM.NAME 'AD', TOT.ONHAND 'MIKTAR', TOT.INVENNO 'AMBAR',
                    CASE WHEN TOT.INVENNO = 0 THEN TOT.ONHAND ELSE 0 END 'Merkez',
                    CASE WHEN TOT.INVENNO = 1 THEN TOT.ONHAND ELSE 0 END 'Optimum',
                    CASE WHEN TOT.INVENNO = 3 THEN TOT.ONHAND ELSE 0 END 'Citys'
            FROM LG_{$firmno}_ITEMS AS ITM RIGHT JOIN LV_{$firmno}_01_STINVTOT AS TOT ON ITM.LOGICALREF = TOT.STOCKREF) CEKASTOK
            GROUP BY CEKASTOK.KOD, CEKASTOK.AD"];

        $result = $logo->getLogo("/api/v1/queries?".http_build_query($sql));
        foreach($result['items'] as $item) {
            if(empty($item['KOD'])) {
                continue;
            }
            $find = $this->db->query("SELECT * FROM ". DB_PREFIX."product_stock WHERE barcode = '".$item['KOD']."'")->row;
            if(!empty($find)) {
                $quantity = $item['Merkez']>0 ? $item['Merkez'] : 0;
                $this->db->query("UPDATE " . DB_PREFIX ."product_stock SET quantity = " . $quantity . ", quantity_updated_at = '".date('Y-m-d H:i:s')."' WHERE barcode=" . $item['KOD'] . " LIMIT 1" );   
            }
        }
    }

    protected function updateQuantities() {
        $materials = $this->db->query("SELECT *  FROM " . DB_PREFIX ."product_stock ps WHERE ps.quantity_updated_at IS NOT NULL AND ps.product_stock_id <> 1 AND ps.quantity_transferred_at < NOW() - INTERVAL 10 MINUTE")->rows;

        foreach($materials as $material) {
            echo date('Y-m-d H:i:s u') . '<br/>';
            echo $material['barcode'] . '<br/>';
            echo $material['quantity'] . '<br/>';
            echo '<pre>';
            $material_products = $this->db->query("SELECT * FROM " . DB_PREFIX ."product AS p INNER JOIN ps_product_stock AS ps ON p.barcode = ps.barcode WHERE ps.barcode = '{$material['barcode']}'");
            //print_r($material_products);
            /*
            foreach($material_products as $product) {
                $this->db->query("UPDATE " . DB_PREFIX . "product 
                SET quantity = '" . $product_barcode . "', 
                LIMIT 1");
            }
            */

            $material_options = $this->db->query("SELECT * FROM " . DB_PREFIX ."product_option_value pov
                LEFT JOIN " . DB_PREFIX ."product_stock ps
                ON pov.option_value_barcode = ps.barcode	
                WHERE option_value_barcode = '{$material['barcode']}'"
            );
            /*
            foreach($material_options as $option) {
                $this->db->query("UPDATE " . DB_PREFIX . "product_option_value 
                SET quantity = '" . $material['quantity'] . "' 
                WHERE product_option_value_id = " . $option['product_option_value_id'] ."
                LIMIT 1");
            }
            */
            echo date('Y-m-d H:i:s u') . '<br/>';
            break;
        }
    }

    protected function createBarcodes() {
        $products = $this->db->query("SELECT * FROM " . DB_PREFIX ."product p WHERE p.status = 1 AND p.barcode = '' ORDER BY RAND() LIMIT 2500")->rows;
        
        foreach($products as $product) {
            $product_id = $product['product_id'];

            $category = $this->db->query("SELECT
                c.category_id,
                cd.`name`, 
                c.infinityeditid
                FROM
                ps_product_to_category AS ptc
                INNER JOIN ps_category AS c ON ptc.category_id = c.category_id
                INNER JOIN ps_category_description AS cd ON c.category_id = cd.category_id 
                WHERE
                ptc.product_id = {$product_id}   
                AND c.is_logo_material = 1
            ")->rows;
            
            if(empty($category)) {
                continue;
            }
            if(count($category)>1) {
                continue;
            }

            $category = $category[0];

            $is_option = false;
            if($category['category_id'] == 4762 || $category['category_id'] == 4894) {
                $is_option = true;
            }
            $color = $this->db->query("SELECT ad.`name`, a.infinityeditid, a.attribute_id 
                FROM ps_product AS p 
                INNER JOIN ps_product_attribute AS pa ON p.product_id = pa.product_id
                INNER JOIN ps_attribute AS a ON pa.attribute_id = a.attribute_id
                INNER JOIN ps_attribute_description AS ad ON a.attribute_id = ad.attribute_id
                INNER JOIN ps_attribute_group AS ag ON a.attribute_group_id = ag.attribute_group_id 
                WHERE a.attribute_group_id = 13
                AND p.product_id = '" . $product_id ."'
            ")->row;

            if(empty($color)) {
                continue;
            }
    
            $collection = $this->db->query("SELECT ad.`name`, a.infinityeditid, a.attribute_id 
                FROM ps_product AS p 
                INNER JOIN ps_product_attribute AS pa ON p.product_id = pa.product_id
                INNER JOIN ps_attribute AS a ON pa.attribute_id = a.attribute_id
                INNER JOIN ps_attribute_description AS ad ON a.attribute_id = ad.attribute_id
                INNER JOIN ps_attribute_group AS ag ON a.attribute_group_id = ag.attribute_group_id 
                WHERE a.attribute_group_id = 11
                AND p.product_id = '" . $product_id ."'
            ")->row;

            if(empty($collection)) {
                continue;
            }

            $barcode_category = !empty($category['infinityeditid']) ? $category['infinityeditid'] : $category['category_id'];
            $barcode_collection = !empty($collection['infinityeditid']) ? $collection['infinityeditid'] : $collection['attribute_id'];
            $barcode_color = !empty($color['infinityeditid']) ? $color['infinityeditid'] : $color['attribute_id'];
            $product_barcode = $barcode_category . $barcode_collection . $barcode_color;
            
            $this->db->query("UPDATE " . DB_PREFIX . "product 
            SET barcode = '" . $product_barcode . "', 
            product_stock_id = '" . $product_barcode . "'     
            WHERE product_id = " . $product_id . " 
            LIMIT 1");
            
            echo $product_id . '<br/>';
            echo 'MAIN: ' . $product_barcode . '<br/>';
            if($is_option) {
                $options = $this->db->query("SELECT
                        ovd.* 
                    FROM
                        ps_product_option AS po
                        INNER JOIN ps_product_option_value AS pov ON po.product_option_id = pov.product_option_id
                        INNER JOIN ps_product AS p ON po.product_id = p.product_id
                        INNER JOIN ps_option_value AS ov ON pov.option_value_id = ov.option_value_id
                        INNER JOIN ps_option_value_description AS ovd ON ov.option_value_id = ovd.option_value_id 
                    WHERE
                        p.product_id = {$product['product_id']}
                    ")->rows;

                foreach($options as $option) {
                    $option_barcode = $product_barcode . $option['option_value_id'];
                    echo 'VARIANT: ' . $option_barcode . '<br/>';

                    $this->db->query("UPDATE " . DB_PREFIX . "product_option_value 
                    SET option_value_barcode = '" . $option_barcode . "'
                    WHERE product_id = " . $product_id . " AND
                    option_value_id = " . $option['option_value_id'] ."
                    LIMIT 1");
                }

            }
            
        }
    }
}

?>