<?php
require_once 'Ingroup.php';
require_once RUTA_APP . 'Conexion.php';
class IngroupDatos
{
    public static function getIngroupById($id)
    {
        $con = getConnection();
        $query = "SELECT group_id, group_name, user_group, active,next_agent_call, call_time_id FROM vicidial_inbound_groups WHERE group_id = '" . limpia($id) . "'";
        $resp = null;
        $result = sv_mysqli_query($con, $query);
        if ($row = sv_mysqli_fetch_array($result)) {
            $resp = new Ingroup(
                $row["group_id"],
                ClienteDatos::getClienteById($row["user_group"]),
                $row["group_name"],
                $row["active"],
                $row["next_agent_call"],
                $row["call_time_id"]
            );
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function getLastId()
    {
        $con = getConnection();
        $resp = 101;
        $query = "SELECT group_id FROM vicidial_inbound_groups WHERE group_id REGEXP '^[0-9]+$' ORDER BY CAST(group_id AS INT) DESC LIMIT 1";
        $result = sv_mysqli_query($con, $query);
        if ($row = sv_mysqli_fetch_array($result)) {
            $resp = $row["group_id"];
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function addIngroup($ingroup)
    {
        $con = getConnection();
        $query = "INSERT INTO vicidial_inbound_groups (group_id, group_name, group_color, active, next_agent_call,user_group,ingroup_recording_override,ingroup_rec_filename,call_time_id) VALUES('" . limpia(self::getLastId() + 1) . "', '" . limpia($ingroup->getNombre()) . "', 'RED', '" . limpia($ingroup->getActivo()) . "', '" . limpia($ingroup->getNextAgentCall()) . "','" . limpia($ingroup->getCliente()->getId()) . "','DISABLED','IN_AGENT_CAMPAIGN_FULLDATE_CUSTPHONE_" . limpia($ingroup->getCliente()->getId()) . "','" . limpia($ingroup->getCallTime()) . "')";
        $resp = null;
        if (sv_mysqli_query($con, $query)) {
            $resp = 0;
        } else {
            $resp = -1;
        }
        echo mysqli_error($con);
        sv_mysqli_close($con);
        return $resp;
    }
    public static function getIngroupsByCliente($cliente, $texto, $pagina, $limite)
    {
        $con = getConnection();
        $query = "SELECT group_id, group_name, user_group, active, next_agent_call,call_time_id FROM vicidial_inbound_groups WHERE group_id='AGENTDIRECT' UNION SELECT group_id, group_name, user_group, active, next_agent_call,call_time_id FROM vicidial_inbound_groups WHERE group_name LIKE '%" . $texto . "%' AND user_group = '" . $cliente->getId() . "' LIMIT " . $pagina * $limite . "," . $limite;
        $result = sv_mysqli_query($con, $query);
        $resp = array();
        while ($row = sv_mysqli_fetch_array($result)) {
            if ($row["group_id"] != "AGENTDIRECT") {
                $resp[] = new Ingroup(
                    $row["group_id"],
                    ClienteDatos::getClienteById($row["user_group"]),
                    $row["group_name"],
                    $row["active"],
                    $row["next_agent_call"],
                    $row["call_time_id"]
                );
            } else {
                $resp[] = new Ingroup(
                    $row["group_id"],
                    new Cliente(-1, "AGENTDIRECT"),
                    $row["group_name"],
                    $row["active"],
                    $row["next_agent_call"],
                    $row["call_time_id"]
                );
            }

        }
        return $resp;
    }
    public static function countIngroupsByCliente($cliente, $texto)
    {
        $con = getConnection();
        $query = "SELECT COUNT(group_id) AS CUENTA FROM vicidial_inbound_groups WHERE group_name LIKE '%" . $texto . "%' AND user_group = '" . $cliente->getId() . "'";
        $result = sv_mysqli_query($con, $query);
        $resp = 0;
        if ($row = sv_mysqli_fetch_array($result)) {
            $resp = $row["CUENTA"];
        }
        return $resp;
    }
    public static function getIngroups($texto, $pagina, $limite)
    {
        $con = getConnection();
        $query = "SELECT group_id, group_name, user_group, active, next_agent_call,call_time_id FROM vicidial_inbound_groups WHERE group_name LIKE '%" . $texto . "%' LIMIT " . $pagina * $limite . "," . $limite;
        $result = sv_mysqli_query($con, $query);
        $resp = array();
        while ($row = sv_mysqli_fetch_array($result)) {
            $resp[] = new Ingroup(
                $row["group_id"],
                ClienteDatos::getClienteById($row["user_group"]),
                $row["group_name"],
                $row["active"],
                $row["next_agent_call"],
                $row["call_time_id"]
            );
        }
        return $resp;
    }
    public static function countIngroups($texto)
    {
        $con = getConnection();
        $query = "SELECT COUNT(group_id) AS CUENTA FROM vicidial_inbound_groups WHERE user_group = '" . $cliente->getId() . "'";
        $result = sv_mysqli_query($con, $query);
        $resp = 0;
        if ($row = sv_mysqli_fetch_array($result)) {
            $resp = $row["CUENTA"];
        }
        return $resp;
    }
    public static function getAllIngroupsByCliente($cliente)
    {
        $con = getConnection();
        $query = "SELECT group_id, group_name FROM vicidial_inbound_groups WHERE group_id='AGENTDIRECT' UNION SELECT group_id, group_name FROM vicidial_inbound_groups WHERE user_group = '" . $cliente->getId() . "'";
        $result = sv_mysqli_query($con, $query);
        $resp = array();
        while ($row = sv_mysqli_fetch_array($result)) {
            $resp[] = new Ingroup(
                $row["group_id"],
                $cliente,
                $row["group_name"],
                null,
                null,
                null
            );
        }
        return $resp;
    }
    public static function getAllIngroupsByCampana($cliente, $campana)
    {
        $con = getConnection();
        $query = "SELECT closer_campaigns FROM vicidial_campaigns WHERE campaign_id = '" . $campana . "'";
        $result = sv_mysqli_query($con, $query);
        $resp = array();
        if ($row = sv_mysqli_fetch_array($result)) {
            $xfers = explode(" ", substr($row["closer_campaigns"], 1, -2));
            foreach ($xfers as $xfer) {
                $query2 = "SELECT group_id, group_name FROM vicidial_inbound_groups WHERE group_id = '" . $xfer . "'";
                $result2 = sv_mysqli_query($con, $query2);
                if ($row2 = sv_mysqli_fetch_array($result2)) {
                    $resp[] = new Ingroup(
                        $row2["group_id"],
                        $cliente,
                        $row2["group_name"],
                        null,
                        null,
                        null
                    );
                }
            }
        }
        return $resp;
    }

    public static function getAllIngroupsXTransferByCampana($cliente, $campana)
    {
        $con = getConnection();
        $query = "SELECT xfer_groups FROM vicidial_campaigns WHERE campaign_id = '" . $campana . "'";
        $result = sv_mysqli_query($con, $query);
        $resp = array();
        if ($row = sv_mysqli_fetch_array($result)) {
            $xfers = explode(" ", substr($row["xfer_groups"], 1, -2));
            foreach ($xfers as $xfer) {
                $query2 = "SELECT group_id, group_name FROM vicidial_inbound_groups WHERE group_id = '" . $xfer . "'";
                $result2 = sv_mysqli_query($con, $query2);
                if ($row2 = sv_mysqli_fetch_array($result2)) {
                    $resp[] = new Ingroup(
                        $row2["group_id"],
                        $cliente,
                        $row2["group_name"],
                        null,
                        null,
                        null
                    );
                }
            }
        }
        return $resp;
    }

    public static function getAllCampanasByIngroup($cliente, $ingroup)
    {
        $con = getConnection();
        $query = "SELECT group_id FROM vicidial_inbound_groups WHERE group_id = '" . $ingroup . "' AND user_group = '" . $cliente->getId() . "'";
        $result = sv_mysqli_query($con, $query);
        $resp = array();
        if ($row = sv_mysqli_fetch_array($result)) {
            $query2 = "SELECT campaign_id FROM vicidial_campaigns WHERE closer_campaigns LIKE '% " . $ingroup . " %' AND user_group='" . $cliente->getId() . "'";

            $result2 = sv_mysqli_query($con, $query2);
            while ($row2 = sv_mysqli_fetch_array($result2)) {
                $resp[] = $row2["campaign_id"];
            }
        }
        return $resp;
    }
    public static function getAllUsuariosByIngroup($cliente, $ingroup)
    {
        $con = getConnection();
        $query = "SELECT group_id FROM vicidial_inbound_groups WHERE group_id = '" . $ingroup . "' AND user_group='" . $cliente->getId() . "'";

        $result = sv_mysqli_query($con, $query);
        $resp = array();
        if ($row = sv_mysqli_fetch_array($result)) {
            $query2 = "SELECT user FROM vicidial_users WHERE user_group LIKE '%" . $cliente->getId() . "%' AND ( closer_campaigns LIKE '% " . $ingroup . " %' OR closer_campaigns LIKE '" . $ingroup . " %' )";

            $result2 = sv_mysqli_query($con, $query2);
            while ($row2 = sv_mysqli_fetch_array($result2)) {
                $resp[] = $row2["user"];
            }
        }
        return $resp;
    }
    public static function addIngroupByCampana($cliente, $ingroup, $campana)
    {
        $con = getConnection();
        $query = "SELECT closer_campaigns FROM vicidial_campaigns WHERE campaign_id='$campana' AND user_group='" . $cliente->getId() . "'";

        $result = sv_mysqli_query($con, $query);
        $resp = -1;
        if ($row = sv_mysqli_fetch_array($result)) {

            $xfers = " " . trim(str_replace("-", "", preg_replace('/\s{1,}/', " ", $row["closer_campaigns"]))) . " $ingroup -";
            $query2 = "UPDATE vicidial_campaigns SET closer_campaigns='$xfers' WHERE campaign_id='$campana' AND user_group='" . $cliente->getId() . "' LIMIT 1";

            sv_mysqli_query($con, $query2);
            $resp = mysqli_affected_rows($con);
        }
        return $resp;
    }
    public static function removeIngroupByCampana($cliente, $ingroup, $campana)
    {
        $con = getConnection();
        $query = "SELECT closer_campaigns FROM vicidial_campaigns WHERE campaign_id='$campana' AND user_group='" . $cliente->getId() . "'";

        $result = sv_mysqli_query($con, $query);
        $resp = -1;
        if ($row = sv_mysqli_fetch_array($result)) {
            $xfers = str_replace("$ingroup ", "", $row["closer_campaigns"]);
            $query2 = "UPDATE vicidial_campaigns SET closer_campaigns='$xfers' WHERE campaign_id='$campana' AND user_group='" . $cliente->getId() . "' LIMIT 1";

            sv_mysqli_query($con, $query2);
            $resp = mysqli_affected_rows($con);
        }
        return $resp;

    }
    public static function addIngroupByUsuario($cliente, $ingroup, $campana)
    {
        $con = getConnection();
        $query = "SELECT closer_campaigns FROM vicidial_users WHERE user='$campana' AND user_group LIKE '%" . $cliente->getId() . "%'";

        $result = sv_mysqli_query($con, $query);
        $resp = -1;
        if ($row = sv_mysqli_fetch_array($result)) {
            $xfers = " " . trim(str_replace("-", "", preg_replace('/\s{1,}/', " ", $row["closer_campaigns"]))) . " $ingroup -";
            $query2 = "UPDATE vicidial_users SET closer_campaigns='$xfers' WHERE user='$campana' AND user_group='" . $cliente->getId() . "_" . GRUPO_AGENTES . "' LIMIT 1";

            sv_mysqli_query($con, $query2);
            $resp = mysqli_affected_rows($con);
        }
        return $resp;
    }
    public static function removeIngroupByUsuario($cliente, $ingroup, $campana)
    {
        $con = getConnection();
        $query = "SELECT closer_campaigns FROM vicidial_users WHERE user='$campana' AND user_group LIKE '%" . $cliente->getId() . "%'";

        $result = sv_mysqli_query($con, $query);
        $resp = -1;
        if ($row = sv_mysqli_fetch_array($result)) {
            $xfers = str_replace("$ingroup ", "", $row["closer_campaigns"]);
            $query2 = "UPDATE vicidial_users SET closer_campaigns='$xfers' WHERE user='$campana' AND user_group='" . $cliente->getId() . "_" . GRUPO_AGENTES . "' LIMIT 1";
            sv_mysqli_query($con, $query2);
            $resp = mysqli_affected_rows($con);
        }
        return $resp;

    }
    public static function getAllIngroups()
    {
        $con = getConnection();
        $query = "SELECT group_id, group_name, user_group, active, next_agent_call FROM vicidial_inbound_groups WHERE group_name LIKE '%" . $texto . "%'";
        $result = sv_mysqli_query($con, $query);
        $resp = array();
        while ($row = sv_mysqli_fetch_array($result)) {
            $resp[] = new Ingroup(
                $row["group_id"],
                null,
                $row["group_name"],
                null,
                null,
                null
            );
        }
        return $resp;
    }
    public static function editIngroup($ingroup)
    {
        $con = getConnection();
        $query = "UPDATE vicidial_inbound_groups SET group_name = '" . limpia($ingroup->getNombre()) . "', active = '" . limpia($ingroup->getActivo()) . "', next_agent_call = '" . limpia($ingroup->getNextAgentCall()) . "', call_time_id = '" . limpia($ingroup->getCallTime()) . "' WHERE group_id = '" . limpia($ingroup->getId()) . "'";
        $resp = null;
        if ($result = sv_mysqli_query($con, $query)) {
            $resp = 0;
        } else {
            $resp = -1;
        }
        sv_mysqli_close($con);
        return $resp;
    }
    public static function deleteIngroup($ingroup)
    {
        $con = getConnection();
        $query = "DELETE FROM vicidial_inbound_groups WHERE group_id = '" . limpia($ingroup->getId()) . "' LIMIT 1";
        $resp = null;
        if ($result = sv_mysqli_query($con, $query)) {
            $resp = 0;
        } else {
            $resp = -1;
        }
        sv_mysqli_close($con);
        return $resp;
    }
}
