<?php

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

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


    function customMortalityByDay($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;
    }
    function customMortalityDate($start, $end)
    {
        $query = $this->db->query("SELECT earring, date_mortality ,cause, COUNT(date_mortality) AS mortality_day, SUM(CASE WHEN (specie = 'Ovino') THEN 1 else 0 END) as mortality_ovinos, SUM(CASE WHEN (specie = 'Aves') THEN 1 else 0 END) as mortality_aves FROM vw_mortality where (date_mortality BETWEEN '2022-01-01' AND '2022-05-10') GROUP BY date_mortality");
        $reponseDB = array(
            "status" => "success",
            "status_code" => 200,
            "message" => "Data loaded, successful",
            "validations" => null,
            "data" => $query->result()
        );
        return $reponseDB;
    }


    function mortalityCause($cause = NULL)
    {
        if ($cause) {

            $query = $this->db->query("SELECT *, COUNT(earring) AS total_muertes FROM `mortality` WHERE MONTH(date_mortality) = MONTH(CURDATE()) and status=1 AND cause= '$cause'");
            $reponseDB = array(
                "status" => "success",
                "status_code" => 200,
                "message" => "Data loaded, successful",
                "validations" => null,
                "data" => $query->row()
            );
        } else {
            $query = $this->db->query("SELECT *, COUNT(earring) AS total_muertes FROM `mortality` WHERE MONTH(date_mortality) = MONTH(CURDATE()) and status=1");
            $reponseDB = array(
                "status" => "success",
                "status_code" => 200,
                "message" => "Data loaded, successful",
                "validations" => null,
                "data" => $query->row()
            );
        }
        return $reponseDB;
    }





    function recentBirths($recentBirths = NULL)
    {
        if ($recentBirths) {

            $query = $this->db->query("SELECT * FROM `births` WHERE earring= '$recentBirths' and date_birth BETWEEN (CURDATE() - INTERVAL 30 DAY) AND CURDATE() ");
            $reponseDB = array(
                "status" => "success",
                "status_code" => 200,
                "message" => "Data loaded, successful",
                "validations" => null,
                "data" => $query->row()
            );
        } else {
            $query = $this->db->query("SELECT * FROM `births` WHERE earring= '$recentBirths' and date_birth BETWEEN (CURDATE() - INTERVAL 30 DAY) AND CURDATE() ");
            $reponseDB = array(
                "status" => "success",
                "status_code" => 200,
                "message" => "Data loaded, successful",
                "validations" => null,
                "data" => $query->row()
            );
        }
        return $reponseDB;
    }

    

}
