bam.php

    static function generalNewsletterManagement()
    {
        user::needToBeLogged();
        user::needToBeLevel(get_class());




        @require_once 'classes/CConfig.php';
        $link = CConfig::Conectar('media_new');
        mysql_query('SET NAMES utf8');

        //newsletter log estado: 0 - espera | 1 - enviada | 2 - abierta (pixel)

        $query = <<<EOT
            SELECT
                N.id_newsletter, N.tipo, N.datos, N.iduser, N.fecha_crea, N.fecha_inicio,
                U.nombre AS creador,
                SUM(L.estado=0) AS pendientes,
                SUM(L.estado=1) AS enviadas,
                SUM(L.estado=2) AS abiertas,
                SUM(L.estado=-1) AS fallidas,
                COUNT(*) AS totales
            FROM media.newsletter N
            JOIN media.usuarios U ON U.iduser = N.iduser
            JOIN media.newsletter_log L ON N.id_newsletter = L.id_newsletter
            WHERE N.tipo = 'NEW'
            GROUP BY N.id_newsletter
            ORDER BY N.fecha_crea DESC
EOT;

        $result = CConfig::PConsulta($query, $link);

        $newsletters = array();
        if($result){
            while ($row = mysql_fetch_assoc($result)) {

                //echo "<pre>", print_r($row, 1), "</pre>";

                $fechaCreacion = date('d-m-Y h:m', $row['fecha_crea']);
                $fechaInicio = date('d-m-Y h:m', $row['fecha_inicio']);

                $row['fecha_crea'] = $fechaCreacion;
                $row['fecha_inicio'] = $fechaInicio;

                $JSONData = json_decode($row['datos'], true);

                $row['companies'] = $JSONData['companies'];
                //$row['offers'] = $JSONData['offers'];

                $row['HTML'] = $JSONData['HTML'];
                $row['subject'] = $JSONData['subject'];

                $newsletters[$row['id_newsletter']] = $row;

                $newsletters[$row['id_newsletter']]['logs_pendiente'] = array();
                $newsletters[$row['id_newsletter']]['logs_enviada'] = array();
                $newsletters[$row['id_newsletter']]['logs_abierta'] = array();
                $newsletters[$row['id_newsletter']]['logs_error'] = array();
            }
        }

        $query = <<<EOT
            SELECT
                L.id_newsletter, L.id_empresa, E.empresa, L.estado, L.info
            FROM media.newsletter_log L
            JOIN media_new.adt_empresa E ON L.id_empresa = E.id_empresa
            ORDER BY L.estado DESC, E.empresa ASC
EOT;

        $resultEstados = CConfig::PConsulta($query, $link);

        $estadosLog = array('0' =>'pendiente', '1' => 'enviada', '2' => 'abierta', '-1' => 'error');

        if($resultEstados){
            while ($row = mysql_fetch_assoc($resultEstados)) {

                $estado = $estadosLog[$row['estado']];

                $rowAsArray = array(
                    'id' => $row['id_empresa'],
                    'name' => $row['empresa'],
                    'estado' => $estadosLog[$row['estado']],
                    'info' => json_decode($row['info'],true)
                );
                $newsletterId = $row['id_newsletter'];

                if ($newsletters[$newsletterId]) {
                    array_push($newsletters[$newsletterId]['logs_' . $estado], $rowAsArray);
                }
            }
        }

        $params = array();
        $params['newsletters'] = $newsletters;
        $params['links'] = utils::getMenuConfig();

        template::draw('config.generalNewsletterManagement', $params);

        CConfig::DesConectar($link);
    }