<?php
class ModelMercadolivreMeasureManufacturer extends Model{


    public function getMeasured($data = array(), $page = 1){

        $sql = "SELECT 	MLG.`measure_GRID_id`,
                        MLG.`manufacturer_id`,
                        MLG.`GENDER`,
                        MLG.`SIZE_GRID_ID`,
                        MLG.`domain_id`,
                        MLG.`manufacturer_description`,
                        M.`name`,
                        M.`image`
                FROM 	".DB_PREFIX."mercadolivre_measure_GRID MLG
        INNER JOIN  ".DB_PREFIX."manufacturer M ON M.manufacturer_id = MLG.manufacturer_id";

        $filters = array();
        if(isset($data['description']) and trim($data['description']) != ''){
            $datasql = trim($data['description']);
            array_push($filters," `manufacturer_description`	like  '%{$datasql}%' ");
        }
        if(isset($data['gender']) AND trim($data['gender']) != ''){
            $datasql = trim($data['gender']);
            array_push($filters," GENDER like  '%{$datasql}%' ");
        }

        if($filters){
            $sql .= " WHERE ". implode(' AND ', $filters);
        }

        $limit = $this->config->get('config_admin_limit');
        $ini = (($page * $limit) - ($limit));

        $sql .=  " LIMIT $ini , $limit";

        $query	= $this->db->query($sql);

        return $query->rows;
    }

    public function getChartSize($size, $grid){

        $sql = "  SELECT
	  				     R.SIZE_GRID_ROW_ID,
                        R.BR_SIZE
                    FROM oc_mercadolivre_measure_GRID G
              INNER JOIN oc_mercadolivre_measure_ROW R ON G.measure_GRID_id = R.measure_GRID_id
              INNER JOIN oc_product_size P ON R.productsize_id = P.productsize_id
                  WHERE SIZE_GRID_ID = '{$grid}'
                    AND P.`description` = '{$size}'";


        $query	= $this->db->query($sql);

        return $query->row;
    }

    public function getMeasuredTotal($data = array()){

        $sql = "SELECT 	count(*) qtd
                FROM 	".DB_PREFIX."mercadolivre_measure_GRID MLG
        INNER JOIN  ".DB_PREFIX."manufacturer M ON M.manufacturer_id = MLG.manufacturer_id";

        $filters = array();
        if(isset($data['description']) and trim($data['description']) != ''){
            $datasql = trim($data['description']);
            array_push($filters," `manufacturer_description`	like  '%{$datasql}%' ");
        }
        if(isset($data['gender']) AND trim($data['gender']) != ''){
            $datasql = trim($data['gender']);
            array_push($filters," GENDER like  '%{$datasql}%' ");
        }

        if($filters){
            $sql .= " WHERE ". implode(' AND ', $filters);
        }

        $query	= $this->db->query($sql);

        return $query->row['qtd'];
    }


    public function deleteMeasured($list = []){

        $list = implode(',',$list);
        $sql = "DELETE FROM oc_mercadolivre_measure_GRID WHERE measure_GRID_id IN ({$list});";

        $query	= $this->db->query($sql);

        return $query;

    }

    public function getChartsProduct(){
        $sql = "SELECT `measure_GRID_id`,
                       `manufacturer_id`,
                       `manufacturer_description`,
                       `GENDER`,
                       `domain_id`,
                       `SIZE_GRID_ID`,
                       `type`
                  FROM oc_mercadolivre_measure_GRID";

        $query	= $this->db->query($sql);

        return $query->rows;
    }

    public function getCharts($data){

            if($data['domain_id'] === 'MLB-SNEAKERS'){
                $sql = "SELECT   MLG.`measure_GRID_id`,
                MLG.`manufacturer_id`,
                MLG.`manufacturer_description`,
                MAR.`name`,
                MLG.`type`,
                MLG.`GENDER`,
                MLG.`domain_id`,
                MLG.`SIZE_GRID_ID`,
                MLR.`measure_ROW_id`,
                MLR.`measure_GRID_id`,
                MLR.`SIZE_GRID_ROW_ID`,
                SIZE.`description`,
                MLR.`BR_SIZE`,
                MLR.`US_SIZE`,
                MLR.`EU_SIZE`,
                MLR.`UK_SIZE`,
                MLR.`FOOT_LENGTH`,
                MLR.`productsize_id`,
                MLR.`product_grid_template_id`
                FROM  oc_mercadolivre_measure_GRID MLG
                INNER JOIN  oc_mercadolivre_measure_ROW  MLR
                ON  MLG.`measure_GRID_id` = MLR.`measure_GRID_id`
                INNER JOIN  oc_product_size SIZE
                ON  MLR.`productsize_id` = SIZE.`productsize_id`
                INNER JOIN  oc_manufacturer MAR
                ON  MLG.`manufacturer_id` = MAR.`manufacturer_id`
                WHERE  MLG.domain_id = '{$data['domain_id']}'";

            }else{
                $sql = "SELECT   MLG.`measure_GRID_id`,
                MLG.`manufacturer_id`,
                MLG.`manufacturer_description`,
                MAR.`name`,
                MLG.`type`,
                MLG.`GENDER`,
                MLG.`domain_id`,
                MLG.`SIZE_GRID_ID`,
                MLR.`measure_ROW_id`,
                MLR.`measure_GRID_id`,
                MLR.`SIZE_GRID_ROW_ID`,
                SIZE.`description`,
                MLR.`filtrable_size`,
                MLR.`chest_circumference_from`,
                MLR.`chest_circumference_to`,
                MLR.`waist_circumference_from`,
                MLR.`waist_circumference_to`,
                MLR.`person_height_from`,
                MLR.`person_height_to`,
                MLR.`hip_circumference_from`,
                MLR.`hip_circumference_to`,
                MLR.`productsize_id`
                FROM  oc_mercadolivre_measure_GRID MLG
                INNER JOIN  oc_mercadolivre_measure_ROW  MLR
                ON  MLG.`measure_GRID_id` = MLR.`measure_GRID_id`
                INNER JOIN  oc_product_size SIZE
                ON  MLR.`productsize_id` = SIZE.`productsize_id`
                INNER JOIN  oc_manufacturer MAR
                ON  MLG.`manufacturer_id` = MAR.`manufacturer_id`
                WHERE  MLG.domain_id = '{$data['domain_id']}'";
            }

        if(isset($data['chart']) AND $data['chart']){
            $sql .= " AND  MLG.SIZE_GRID_ID  = {$data['chart']}";
        }

        if($data['gender']){
            $sql .= " AND  MLG.GENDER    = '{$data['gender']}'";
        }

        if($data['gender']){
            $sql .= " AND  MLG.manufacturer_id = {$data['brand']}";
        }


        $query	= $this->db->query($sql);

        return $query->rows;

    }

    public function getChartsSql(){
        $sql = "SELECT * FROM oc_mercadolivre_measure_GRID";

        $query	= $this->db->query($sql);

        return $query->rows;
    }

    public function setCharts(){

        extract($this->request->post);

        $description = '';
        if(isset($list['names'])) {
            foreach ($list['names'] as $value) {

                if ($value['id'] == $selected) {
                    $description = $value['name'];
                    break;
                }
            }
        }

        if(isset($info['insert']) AND $info['insert']){
            $description = $info['name'];
        }

        $sql = "INSERT INTO oc_mercadolivre_measure_GRID( measure_GRID_id,
						manufacturer_id,
						manufacturer_description,
						GENDER,
						domain_id,
						SIZE_GRID_ID,
                        type)
	            VALUES (NULL,
	            '{$info['brand']}',
	            '{$description}',
	            '{$info['gender']}',
	            '{$info['domain_id']}',
	            '{$selected}',
	            '{$type}')";

        $query	= $this->db->query($sql);

        $idGrid =  $this->db->getLastId();

        if($info['domain_id'] === 'MLB-SNEAKERS'){
            foreach ($rows as $row){
                $sql = "INSERT INTO oc_mercadolivre_measure_ROW(`measure_ROW_id`,
                                `measure_GRID_id`,
                                `SIZE_GRID_ROW_ID`,
                                `BR_SIZE`,
                                `US_SIZE`,
                                `EU_SIZE`,
                                `UK_SIZE`,
                                `FOOT_LENGTH`,
                                `productsize_id`,
                                `product_grid_template_id`)
                        VALUES (NULL,
                                '{$idGrid}',
                                '{$row['id']}',
                                '{$row['size']}',
                                '{$row['us']}',
                                '{$row['eu']}',
                                '{$row['uk']}',
                                '{$row['length']}',
                                '{$row['tamanho']}',
                                null)";

                $query	= $this->db->query($sql);
            }
        }else{
            foreach ($rows as $row){
                $sql = "INSERT INTO oc_mercadolivre_measure_ROW(`measure_ROW_id`,
                                `measure_GRID_id`,
                                `SIZE_GRID_ROW_ID`,                      
                                `filtrable_size`,
                                `chest_circumference_from`,
                                `chest_circumference_to`,
                                `waist_circumference_from`,
                                `waist_circumference_to`,
                                `person_height_from`,
                                `person_height_to`,
                                `hip_circumference_from`,
                                `hip_circumference_to`,
                                `productsize_id`,
                                `product_grid_template_id`)
                        VALUES (NULL,
                                '{$idGrid}',
                                '{$row['id']}',
                                '" . json_encode($row['filtrable_size']) . "',
                                '{$row['chest_circumference_from']}',
                                '{$row['chest_circumference_to']}',
                                '{$row['waist_circumference_from']}',
                                '{$row['waist_circumference_to']}',
                                '{$row['person_height_from']}',
                                '{$row['person_height_to']}',
                                '{$row['hip_circumference_from']}',
                                '{$row['hip_circumference_to']}',
                                '{$row['tamanho']}',
                                null)";

                $query	= $this->db->query($sql);
            }
        }

        return $query;
    }

    public function getSystemSize(){

        $sql = "SELECT `productsize_id`,
                       `description`,
                       `id_futura`
                  FROM oc_product_size";

        $query	= $this->db->query($sql);

        return $query->rows;
    }

}