<?php

defined('BASEPATH') or exit('No direct script access allowed');

class GraphsQueries extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }

    function eggProductionByMonth()
    {
        $query = $this->db->query("SELECT
       production.id_production AS id_production,
       production.species AS species,
       production.date_productions AS date_productions,
        sum(egg_production.quantity_kg) as total_kg,
        sum(egg_production.num_pieces) as total_pz,
        YEAR(date_productions) AS year,
        MONTH(date_productions) AS month
    FROM
        production as production, 
        egg_production as egg_production
    WHERE
        production.status=1 AND production.product_type='Huevo'
        AND production.species='Aves' AND egg_production.id_production=production.id_production
        AND egg_production.status_eggs='Bueno' AND egg_production.status=1 AND YEAR(date_productions)= YEAR(curdate())
    GROUP BY MONTH(date_productions)
    ORDER BY
       MONTH(production.date_productions)
    ASC
        ");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }
    function eggProductionBySizeCurrentMonth()
    {
        $query = $this->db->query("SELECT
       production.species AS species,
       production.date_productions AS date_productions,
        egg_production.size_egg as size_egg,
        sum(egg_production.quantity_kg) as total_kg,
        sum(egg_production.num_pieces) as total_pz,
        MONTH(date_productions) AS month
    FROM
        production as production, 
        egg_production as egg_production
    WHERE
        production.status=1 AND production.product_type='Huevo'
        AND production.species='Aves' AND egg_production.id_production=production.id_production
        AND egg_production.status_eggs='Bueno' AND egg_production.status=1 AND  MONTH(date_productions) = MONTH(NOW())
    GROUP BY size_egg
    ORDER BY
       production.date_productions
    DESC    
        ");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }

    function brokenEggProductionCurrentMonth()
    {
        $query = $this->db->query("SELECT production.species AS species,
         production.date_productions AS date_productions,
          egg_production.status_eggs as status_eggs, 
          sum(egg_production.quantity_kg) as total_kg,
           sum(egg_production.num_pieces) as total_pz,
            MONTH(date_productions) AS month FROM production as production,
             egg_production as egg_production
              WHERE production.status=1 AND production.product_type='Huevo'
               AND production.species='Aves' AND egg_production.id_production=production.id_production 
               AND egg_production.status_eggs='Quebrado' AND egg_production.status=1 AND 
               MONTH(date_productions) = MONTH(NOW()) GROUP BY status_eggs 
               ORDER BY production.date_productions DESC 
        ");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }

    function eggProductionByDayCurrentMonth()
    {
        $query = $this->db->query("SELECT production.species AS species,
         production.date_productions AS date_productions,
          sum(egg_production.quantity_kg) as total_kg,
           sum(egg_production.num_pieces) as total_pz,
            YEAR(date_productions) AS year,
             DAY(date_productions) AS day 
             FROM production as production, 
             egg_production as egg_production
              WHERE production.status=1 
              AND production.product_type='Huevo' 
              AND production.species='Aves' 
              AND egg_production.id_production=production.id_production 
              AND egg_production.status_eggs='Bueno' 
              AND egg_production.status=1 
              AND MONTH(date_productions)= MONTH(curdate()) 
              GROUP BY DAY(date_productions) 
              ORDER BY DAY(production.date_productions) ASC ");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }

    function currentEggProductionGeneral($start, $end, $rodeo = NULL)
    {
        if ($rodeo) {
            if ($rodeo == 'all') {
                $query = $this->db->query("SELECT
                production.date_productions AS date_productions,
                sum(egg_production.quantity_kg) as total_kg,
                sum(egg_production.num_pieces) as total_pz,
                rodeos.name_rodeo as rodeo_name
            FROM
                production as production, 
                egg_production as egg_production,
                rodeos as rodeos
            WHERE
                production.status=1 AND production.product_type='Huevo'
                AND production.species='Aves' AND egg_production.id_production=production.id_production AND production.id_rodeo=rodeos.id_rodeos
                AND egg_production.status_eggs='Bueno' AND egg_production.status=1 AND (date_productions BETWEEN '$start' AND '$end')
            GROUP BY date_productions, rodeo_name
            ORDER BY
                production.date_productions
            ASC");
            } else {
                $query = $this->db->query("SELECT
                            production.date_productions AS date_productions,
                            sum(egg_production.quantity_kg) as total_kg,
                            sum(egg_production.num_pieces) as total_pz,
                            rodeos.name_rodeo as rodeo_name
                        FROM
                            production as production, 
                            egg_production as egg_production,
                            rodeos as rodeos
                        WHERE
                            production.status=1 AND production.product_type='Huevo'
                            AND production.species='Aves' AND egg_production.id_production=production.id_production AND production.id_rodeo=rodeos.id_rodeos
                            AND egg_production.status_eggs='Bueno' AND egg_production.status=1 AND (date_productions BETWEEN '$start' AND '$end')
                            AND production.id_rodeo='$rodeo'
                        GROUP BY date_productions
                        ORDER BY
                            production.date_productions
                        ASC");
            }
        } else {
            $query = $this->db->query("SELECT
            production.date_productions AS date_productions,
             sum(egg_production.quantity_kg) as total_kg,
             sum(egg_production.num_pieces) as total_pz
         FROM
             production as production, 
             egg_production as egg_production
         WHERE
             production.status=1 AND production.product_type='Huevo'
             AND production.species='Aves' AND egg_production.id_production=production.id_production 
             AND egg_production.status_eggs='Bueno' AND egg_production.status=1 AND (date_productions BETWEEN '$start' AND '$end')
         GROUP BY date_productions
         ORDER BY
            production.date_productions
         ASC");
        }
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }
    function customEggProductionByDay($date, $rodeo = NULL, $ud = NULL)
    {
        if ($rodeo) {
            if ($rodeo == 'all') {
                if ($ud == 'pz') {
                    $query = $this->db->query("SELECT production.date_productions AS date_productions,
                    MAX(CASE WHEN (egg_production.size_egg='Chico' AND egg_production.status_eggs='Bueno') THEN egg_production.num_pieces else 0 end) as 'chico',
                    MAX(CASE WHEN (egg_production.size_egg='Mediano' AND egg_production.status_eggs='Bueno') THEN egg_production.num_pieces else 0 end) as 'mediano',
                    MAX(CASE WHEN (egg_production.size_egg='Grande' AND egg_production.status_eggs='Bueno') THEN egg_production.num_pieces else 0 end) as 'grande',
                    MAX(CASE WHEN (egg_production.size_egg='Extra Grande' AND egg_production.status_eggs='Bueno') THEN egg_production.num_pieces else 0 end) as 'extra_grande',
                    MAX(CASE WHEN (egg_production.size_egg='Jumbo' AND egg_production.status_eggs='Bueno') THEN egg_production.num_pieces else 0 end) as 'jumbo',
                    MAX(CASE WHEN (egg_production.status_eggs='Quebrado') THEN egg_production.num_pieces else 0 end) as 'quebrado', 
                    rodeos.name_rodeo as rodeo_name FROM production as production, rodeos as rodeos, egg_production as egg_production 
                    WHERE production.status=1 AND production.product_type='Huevo' AND production.species='Aves' AND egg_production.id_production=production.id_production 
                    AND rodeos.id_rodeos=production.id_rodeo  AND egg_production.status=1 AND date_productions = '$date' GROUP BY rodeo_name ");
                } else {
                    $query = $this->db->query("SELECT production.date_productions AS date_productions,
                    MAX(CASE WHEN (egg_production.size_egg='Chico' AND egg_production.status_eggs='Bueno') THEN egg_production.quantity_kg else 0 end) as 'chico',
                    MAX(CASE WHEN (egg_production.size_egg='Mediano' AND egg_production.status_eggs='Bueno') THEN egg_production.quantity_kg else 0 end) as 'mediano',
                    MAX(CASE WHEN (egg_production.size_egg='Grande' AND egg_production.status_eggs='Bueno') THEN egg_production.quantity_kg else 0 end) as 'grande',
                    MAX(CASE WHEN (egg_production.size_egg='Extra Grande' AND egg_production.status_eggs='Bueno') THEN egg_production.quantity_kg else 0 end) as 'extra_grande',
                    MAX(CASE WHEN (egg_production.size_egg='Jumbo' AND egg_production.status_eggs='Bueno') THEN egg_production.quantity_kg else 0 end) as 'jumbo',
                    MAX(CASE WHEN (egg_production.status_eggs='Quebrado') THEN egg_production.quantity_kg else 0 end) as 'quebrado', 
                    rodeos.name_rodeo as rodeo_name FROM production as production, rodeos as rodeos, egg_production as egg_production 
                    WHERE production.status=1 AND production.product_type='Huevo' AND production.species='Aves' AND egg_production.id_production=production.id_production 
                    AND rodeos.id_rodeos=production.id_rodeo  AND egg_production.status=1 AND date_productions = '$date' GROUP BY rodeo_name ");
                }
            } else {
                $query = $this->db->query("SELECT production.date_productions AS date_productions,
                egg_production.size_egg as size_egg,
                sum(egg_production.quantity_kg) as total_kg,
                sum(egg_production.num_pieces) as total_pz, 
                rodeos.name_rodeo as rodeo_name
                FROM production as production, rodeos as rodeos,
                egg_production as egg_production WHERE production.status=1
                AND production.product_type='Huevo' 
                AND production.species='Aves' AND 
                egg_production.id_production=production.id_production AND rodeos.id_rodeos=production.id_rodeo AND
                egg_production.status_eggs='Bueno'
                AND egg_production.status=1
                AND date_productions = '$date' 
                AND production.id_rodeo='$rodeo' GROUP BY size_egg 
                UNION SELECT production.date_productions AS date_productions,
                egg_production.status_eggs as size_egg, 
                sum(egg_production.quantity_kg) as total_kg,
                sum(egg_production.num_pieces) as total_pz,
                rodeos.name_rodeo as rodeo_name FROM production as production, rodeos as rodeos,
                egg_production as egg_production
                WHERE production.status=1 AND production.product_type='Huevo'
                AND production.species='Aves' AND egg_production.id_production=production.id_production AND rodeos.id_rodeos=production.id_rodeo
                AND egg_production.status_eggs='Quebrado' AND egg_production.status=1 AND 
                date_productions = '$date' and production.id_rodeo='$rodeo' GROUP BY size_egg");
            }
        } else {
            $query = $this->db->query("SELECT production.date_productions AS date_productions,
            egg_production.size_egg as size_egg,
            sum(egg_production.quantity_kg) as total_kg,
            sum(egg_production.num_pieces) as total_pz
            FROM production as production, 
            egg_production as egg_production WHERE production.status=1
            AND production.product_type='Huevo' 
            AND production.species='Aves' AND 
            egg_production.id_production=production.id_production AND
            egg_production.status_eggs='Bueno'
            AND egg_production.status=1
            AND date_productions = '$date' GROUP BY size_egg 
            UNION SELECT production.date_productions AS date_productions,
            egg_production.status_eggs as size_egg, 
            sum(egg_production.quantity_kg) as total_kg,
            sum(egg_production.num_pieces) as total_pz FROM production as production,
            egg_production as egg_production
            WHERE production.status=1 AND production.product_type='Huevo'
            AND production.species='Aves' AND egg_production.id_production=production.id_production 
            AND egg_production.status_eggs='Quebrado' AND egg_production.status=1 AND 
            date_productions = '$date' GROUP BY size_egg");
        }
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }

    /* 
    
    */
    function dataLoggerByHourCurrentDay()
    {
        $rodeo = $this->db->query("SELECT rodeo FROM `data_logger` order by id ASC LIMIT 1")->row();
        if (@$rodeo) {
            $rodeo_name = $rodeo->rodeo;
        } else {
            $rodeo_name = '';
        }
        date_default_timezone_set('America/Mexico_City');
        $currentDate = date("Y-m-d");
        $query = $this->db->query("SELECT AVG(temperature) as temperature, 
        AVG(humidity) as humidity, 
        AVG(dew) as dew, time as time,
        date as date,
         HOUR(time) AS hour, rodeo as rodeo 
         FROM data_logger where date='$currentDate' AND
          rodeo='$rodeo_name' GROUP BY hour");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }
       function dataLoggerLastRegister()
    {
        $rodeo = $this->db->query("SELECT rodeo FROM `data_logger` order by id ASC LIMIT 1")->row();
        if (@$rodeo) {
            $rodeo_name = $rodeo->rodeo;
        } else {
            $rodeo_name = '';
        }
        date_default_timezone_set('America/Mexico_City');
        $query = $this->db->query("SELECT AVG(temperature) as temperature, 
        AVG(humidity) as humidity, 
        AVG(dew) as dew, time as time,
        date as date,
         HOUR(time) AS hour, rodeo as rodeo 
         FROM data_logger where date=(SELECT date FROM `data_logger` ORDER BY date DESC limit 1) AND
          rodeo='$rodeo_name' GROUP BY hour");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }
    //WHERE (start_date BETWEEN '$desde' AND '$hasta') and (status=1) 
    function customDataLoggerGeneral($start, $end, $rodeo)
    {
        $query = $this->db->query("SELECT AVG(temperature) as temperature,
        AVG(humidity) as humidity,
         AVG(dew) as dew, time as time,
        date as date,
         HOUR(time) AS hour, rodeo as rodeo FROM data_logger
         where (date BETWEEN '$start' AND '$end')  AND rodeo='$rodeo' GROUP BY date");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }

    function customDataLoggerByDay($date, $rodeo)
    {
        $query = $this->db->query("SELECT AVG(temperature) as temperature,
        AVG(humidity) as humidity,
         AVG(dew) as dew, time as time,
        date as date,
         HOUR(time) AS hour, rodeo as rodeo FROM data_logger
         where date='$date' AND rodeo='$rodeo' GROUP BY hour");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }

    /* SELECT production.id_production AS id_production, production.species AS species, production.date_productions AS date_productions, sum(egg_production.quantity_kg) as total_kg, sum(egg_production.num_pieces) as total_pz, YEAR(date_productions) AS year, DAY(date_productions) AS day FROM production as production, egg_production as egg_production WHERE production.status=1 AND production.product_type='Huevo' AND production.species='Aves' AND egg_production.id_production=production.id_production AND egg_production.status_eggs='Bueno' AND egg_production.status=1 AND MONTH(date_productions)= MONTH(curdate()) GROUP BY DAY(date_productions) ORDER BY DAY(production.date_productions) ASC  */
    /* SELECT production.species AS species, production.date_productions AS date_productions, egg_production.status_eggs as status_eggs, sum(egg_production.quantity_kg) as total_kg, sum(egg_production.num_pieces) as total_pz, MONTH(date_productions) AS month FROM production as production, egg_production as egg_production WHERE production.status=1 AND production.product_type='Huevo' AND production.species='Aves' AND egg_production.id_production=production.id_production AND egg_production.status_eggs='Quebrado' AND egg_production.status=1 AND MONTH(date_productions) = MONTH(NOW()) GROUP BY status_eggs ORDER BY production.date_productions DESC  */
}
