/**
  * Exports CSV
  */
 protected function renderCSV()
 {
     // exports orders
     if (Tools::isSubmit('csv_orders')) {
         $ids = array();
         foreach ($this->_list as $entry) {
             $ids[] = $entry['id_supply_order'];
         }
         if (count($ids) <= 0) {
             return;
         }
         $id_lang = Context::getContext()->language->id;
         $orders = new PrestaShopCollection('SupplyOrder', $id_lang);
         $orders->where('is_template', '=', false);
         $orders->where('id_supply_order', 'in', $ids);
         $id_warehouse = $this->getCurrentWarehouse();
         if ($id_warehouse != -1) {
             $orders->where('id_warehouse', '=', $id_warehouse);
         }
         $orders->getAll();
         $csv = new CSV($orders, $this->l('supply_orders'));
         $csv->export();
     } elseif (Tools::isSubmit('csv_orders_details')) {
         // header
         header('Content-type: text/csv');
         header('Content-Type: application/force-download; charset=UTF-8');
         header('Cache-Control: no-store, no-cache');
         header('Content-disposition: attachment; filename="' . $this->l('supply_orders_details') . '.csv"');
         // echoes details
         $ids = array();
         foreach ($this->_list as $entry) {
             $ids[] = $entry['id_supply_order'];
         }
         if (count($ids) <= 0) {
             return;
         }
         // for each supply order
         $keys = array('id_product', 'id_product_attribute', 'reference', 'supplier_reference', 'ean13', 'upc', 'name', 'unit_price_te', 'quantity_expected', 'quantity_received', 'price_te', 'discount_rate', 'discount_value_te', 'price_with_discount_te', 'tax_rate', 'tax_value', 'price_ti', 'tax_value_with_order_discount', 'price_with_order_discount_te', 'id_supply_order');
         echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $keys)));
         // overrides keys (in order to add FORMAT calls)
         $keys = array('sod.id_product', 'sod.id_product_attribute', 'sod.reference', 'sod.supplier_reference', 'sod.ean13', 'sod.upc', 'sod.name', 'FORMAT(sod.unit_price_te, 2)', 'sod.quantity_expected', 'sod.quantity_received', 'FORMAT(sod.price_te, 2)', 'FORMAT(sod.discount_rate, 2)', 'FORMAT(sod.discount_value_te, 2)', 'FORMAT(sod.price_with_discount_te, 2)', 'FORMAT(sod.tax_rate, 2)', 'FORMAT(sod.tax_value, 2)', 'FORMAT(sod.price_ti, 2)', 'FORMAT(sod.tax_value_with_order_discount, 2)', 'FORMAT(sod.price_with_order_discount_te, 2)', 'sod.id_supply_order');
         foreach ($ids as $id) {
             $query = new DbQuery();
             $query->select(implode(', ', $keys));
             $query->from('supply_order_detail', 'sod');
             $query->leftJoin('supply_order', 'so', 'so.id_supply_order = sod.id_supply_order');
             $id_warehouse = $this->getCurrentWarehouse();
             if ($id_warehouse != -1) {
                 $query->where('so.id_warehouse = ' . (int) $id_warehouse);
             }
             $query->where('sod.id_supply_order = ' . (int) $id);
             $query->orderBy('sod.id_supply_order_detail DESC');
             $resource = Db::getInstance()->query($query);
             // gets details
             while ($row = Db::getInstance()->nextRow($resource)) {
                 echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row)));
             }
         }
     } elseif (Tools::isSubmit('csv_order_details') && Tools::getValue('id_supply_order')) {
         $supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
         if (Validate::isLoadedObject($supply_order)) {
             $details = $supply_order->getEntriesCollection();
             $details->getAll();
             $csv = new CSV($details, $this->l('supply_order') . '_' . $supply_order->reference . '_details');
             $csv->export();
         }
     }
 }
    $titlepage[] = addslashes($row[title_page]);
    $donnees_initiales = array_map(create_function('$idmail, $pagetitle', 'return "SELECT c.mailing_id, table_mailing_subject, table_mailing_mailgroup, table_mailing_sent, DATE_FORMAT(table_mailing_started, \'%d/%m/%Y %Hh%imn%ss\') AS started, ROUND((TIMESTAMPDIFF(SECOND,started,FROM_UNIXTIME(AVG(DISTINCT(UNIX_TIMESTAMP(c.atime)))))/3600),2) AS latence, COUNT(DISTINCT(c.subscriber_id)) AS clics_uniques, ROUND(((COUNT(DISTINCT(o.subscriber_id))/table_mailing_sent)*100),2) AS taux_ouvertures, ROUND(((COUNT(DISTINCT(c.subscriber_id))/table_mailing_sent)*100),2) AS taux_clics, c.title_page FROM table_clickedurl AS c INNER JOIN (SELECT subscriber_id, MIN(atime) AS minatime FROM table_clickedurl WHERE mailing_id = ".$idmail." AND title_page LIKE \\"".$pagetitle."\\" GROUP BY subscriber_id) AS z ON c.atime = z.minatime AND c.subscriber_id = z.subscriber_id INNER JOIN table_subscribers ON (c.subscriber_id = table_subscriber_id) INNER JOIN table_mailings ON (table_mailing_id = c.mailing_id) LEFT JOIN table_opentracking AS o ON (c.mailing_id = o.mailing_id) UNION ";'), array_values($mailingid), array_values($titlepage));
}
$requetebrute = implode($donnees_initiales);
$requeteintermediaire1 = substr($requetebrute, 0, -7);
$requeteintermediaire2 = $requeteintermediaire1 . " ORDER BY mailing_id DESC";
$requetefinale = str_replace(table_subscribers, $prefix . "subscribers", $requeteintermediaire2);
$requetefinale = str_replace(table_subscriber_id, $prefix . "subscribers.subscriber_id", $requetefinale);
$requetefinale = str_replace(table_mailings, $prefix . "mailings", $requetefinale);
$requetefinale = str_replace(table_mailing_id, $prefix . "mailings.mailing_id", $requetefinale);
$requetefinale = str_replace(table_mailing_started, $prefix . "mailings.started", $requetefinale);
$requetefinale = str_replace(table_mailing_sent, $prefix . "mailings.sent", $requetefinale);
$requetefinale = str_replace(table_mailing_subject, $prefix . "mailings.subject", $requetefinale);
$requetefinale = str_replace(table_mailing_mailgroup, $prefix . "mailings.mailgroup", $requetefinale);
$requetefinale = str_replace(table_opentracking, $prefix . "opentracking", $requetefinale);
$requetefinale = str_replace(table_clickedurl, $prefix . "clickedurl", $requetefinale);
$result = NULL;
$allclicktracking = $id_connex->query($requetefinale);
if (!$allclicktracking) {
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_url_menu.php\">";
}
$data = $allclicktracking->fetchAll();
// print_r($data);
$datas = array();
foreach ($data as $d) {
    $datas[] = array('ID Mail' => $d[mailing_id], 'Titre du message' => $d[subject], 'Heure d\'envoi' => $d[started], 'Groupe' => $d[mailgroup], 'Envois' => $d[sent], 'Visites uniques' => $d[clics_uniques], 'CTR-1 (% ouvertures)' => $d[taux_ouvertures], 'CTR-2 (% clics)' => $d[taux_clics], 'Latence (heures)' => $d[latence], 'Pages ouvertes' => utf8_decode($d[title_page]));
}
require 'exportcsv.class.php';
CSV::export($datas, 'pommo_tracking_url');
// print_r($datas);
Pommo::kill();
    $requetefinale = str_replace(table_subscribers, $prefix . "subscribers", $requetebrute);
    $requetefinale = str_replace(table_subscriber_id, $prefix . "subscribers.subscriber_id", $requetefinale);
    $requetefinale = str_replace(table_subscriber_email, $prefix . "subscribers.email", $requetefinale);
    $requetefinale = str_replace(table_mailings, $prefix . "mailings", $requetefinale);
    $requetefinale = str_replace(table_mailing_id, $prefix . "mailings.mailing_id", $requetefinale);
    $requetefinale = str_replace(table_mailing_started, $prefix . "mailings.started", $requetefinale);
    $requetefinale = str_replace(table_mailing_sent, $prefix . "mailings.sent", $requetefinale);
    $requetefinale = str_replace(table_mailing_subject, $prefix . "mailings.subject", $requetefinale);
    $requetefinale = str_replace(table_mailing_mailgroup, $prefix . "mailings.mailgroup", $requetefinale);
    $requetefinale = str_replace(table_opentracking, $prefix . "opentracking", $requetefinale);
    // Pour vérifier la requete, décommenter la ligne ci-dessous.
    // print $requetefinale;
    // La 2nde requete est fin prete: nous allons maintenant l'exécuter...
    $result = NULL;
    $result = $id_connex->query($requetefinale);
    if (!$result) {
        echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_opening_menu.php\">";
    }
    $data = $result->fetchAll();
    // print_r($data);
    $datas = array();
    foreach ($data as $d) {
        $datas[] = array('ID Mail' => $d[mailing_id], 'Titre du message' => $d[subject], 'Groupe' => $d[mailgroup], 'Heure d\'envoi' => $d[started], 'Heure d\'ouverture' => $d[dateouverture], 'Latence (heures)' => $d[latence], 'Email' => $d[email]);
    }
    require 'exportcsv.class.php';
    CSV::export($datas, 'pommo_ouvertures_details');
    // print_r($datas);
} else {
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_opening_menu.php\">";
}
Pommo::kill();
    $requetefinale = str_replace(table_subscriber_id, $prefix . "subscribers.subscriber_id", $requetefinale);
    $requetefinale = str_replace(table_subscriber_email, $prefix . "subscribers.email", $requetefinale);
    $requetefinale = str_replace(table_mailings, $prefix . "mailings", $requetefinale);
    $requetefinale = str_replace(table_mailing_id, $prefix . "mailings.mailing_id", $requetefinale);
    $requetefinale = str_replace(table_mailing_started, $prefix . "mailings.started", $requetefinale);
    $requetefinale = str_replace(table_mailing_sent, $prefix . "mailings.sent", $requetefinale);
    $requetefinale = str_replace(table_mailing_subject, $prefix . "mailings.subject", $requetefinale);
    $requetefinale = str_replace(table_mailing_mailgroup, $prefix . "mailings.mailgroup", $requetefinale);
    $requetefinale = str_replace(table_opentracking, $prefix . "opentracking", $requetefinale);
    $requetefinale = str_replace(table_clickedurl, $prefix . "clickedurl", $requetefinale);
    // Pour vérifier la requete, décommenter la ligne ci-dessous.
    // print $requetefinale;
    // La 2nde requete est fin prete: nous allons maintenant l'exécuter...
    $result = NULL;
    $result = $id_connex->query($requetefinale);
    if (!$result) {
        echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_url_menu.php\">";
    }
    $data = $result->fetchAll();
    // print_r($data);
    $datas = array();
    foreach ($data as $d) {
        $datas[] = array('ID Mail' => $d[mailing_id], 'Titre du message' => $d[subject], 'Groupe' => $d[mailgroup], 'Heure d\'envoi' => $d[started], 'Heure d\'ouverture' => $d[dateouverture], 'Heure du 1er clic' => $d[dateclic], 'Latence (heures)' => $d[latence], 'Email' => $d[email], 'Frequence' => $d[occurrence], 'Pages ouvertes' => utf8_decode($d[title_page]));
    }
    require 'exportcsv.class.php';
    CSV::export($datas, 'pommo_tracking_details');
    // print_r($datas);
} else {
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_url_menu.php\">";
}
Pommo::kill();
/*********************************
PARAMÈTRES DE CONNEXION À LA BASE 
**********************************/
include '../../specific/inc/inc-connex-PDO.php';
$titre_page = "";
if (isset($_GET['criteria'])) {
    $where = $_GET['criteria'];
    $where = substr($where, 0, -1);
    $where = substr($where, 1);
    $where = $where . "'";
    //print_r($where);
    $sql = "SELECT \r\n" . $prefix . "subscribers.email,\r\nDATE_FORMAT(" . $prefix . "opentracking.atime, '%d/%m/%Y %Hh%imn%ss') AS openingtime,\r\nDATE_FORMAT(" . $prefix . "clickedurl.atime, '%d/%m/%Y %Hh%imn%ss') AS clicktime,\r\n" . $prefix . "clickedurl.title_page,\r\n" . $prefix . "mailings.subject,\r\n" . $prefix . "mailings.mailing_id, \r\nDATE_FORMAT(" . $prefix . "mailings.started, '%d/%m/%Y %Hh%imn%ss') AS started,\r\nROUND((TIMESTAMPDIFF(SECOND,started," . $prefix . "clickedurl.atime)/3600),2) AS latence,\r\n" . $prefix . "mailings.mailgroup \r\nFROM " . $prefix . "clickedurl\r\nINNER JOIN " . $prefix . "subscribers ON (" . $prefix . "clickedurl.subscriber_id = " . $prefix . "subscribers.subscriber_id) \r\nINNER JOIN " . $prefix . "mailings ON (" . $prefix . "mailings.mailing_id = " . $prefix . "clickedurl.mailing_id) \r\nLEFT JOIN " . $prefix . "opentracking ON (" . $prefix . "opentracking.mailing_id = " . $prefix . "clickedurl.mailing_id) AND (" . $prefix . "opentracking.subscriber_id = " . $prefix . "clickedurl.subscriber_id)\r\nWHERE {$where}\r\nORDER BY latence DESC";
    //echo $sql;
    $result = NULL;
    $result = $id_connex->query($sql);
    if (!$result) {
        echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_url_menu.php\">";
    }
    $data = $result->fetchAll();
    //print_r($data);
    $datas = array();
    foreach ($data as $d) {
        $datas[] = array('ID Mail' => $d[mailing_id], 'Titre du message' => $d[subject], 'Groupe' => $d[mailgroup], 'Heure d\'envoi' => $d[started], 'Heure d\'ouverture' => $d[openingtime], 'Heure du clic' => $d[clicktime], 'Latence (heures)' => $d[latence], 'Emails' => $d[email], 'Pages ouvertes' => utf8_decode($d[title_page]));
    }
    require 'exportcsv.class.php';
    CSV::export($datas, 'pommo_tracking_par_critere');
    //print_r($datas);
} else {
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_url_menu.php\">";
}
Pommo::kill();
 /**
  * Exports CSV
  */
 protected function renderCSV()
 {
     // exports orders
     if (Tools::isSubmit('csv_orders')) {
         // header
         header('Content-type: text/csv; charset=utf-8');
         header('Cache-Control: no-store, no-cache');
         header('Content-disposition: attachment; filename="supply_orders.csv"');
         // write headers column
         $keys = array('id_supplier', 'supplier_name', 'id_lang', 'id_warehouse', 'id_supply_order_state', 'id_currency', 'reference', 'date_add', 'date_upd', 'date_delivery_expected', 'total_te', 'total_with_discount_te', 'total_ti', 'total_tax', 'discount_rate', 'discount_value_te', 'is_template', 'escompte', 'invoice_number', 'date_to_invoice', 'global_discount_amount', 'global_discount_type', 'shipping_amount', 'description');
         echo sprintf("%s\n", implode(';', $keys));
         $query = null;
         $query = new DbQuery();
         $query->select('so.*, ipso.*');
         $query->from('supply_order', 'so');
         $query->leftjoin('erpip_supply_order', 'ipso', 'ipso.id_supply_order = so.id_supply_order');
         if ($this->controller_status == STATUS1) {
             $query->limit(ERP_STCKMGTFR);
         }
         // FILTERS SUPPLIER & WAREHOUSE
         $id_warehouse = $this->getCurrentWarehouse();
         if ($id_warehouse != -1) {
             $query->where('so.id_warehouse = ' . (int) $id_warehouse);
         }
         $id_supplier = $this->getCurrentSupplier();
         if ($id_supplier != -1) {
             $query->where('so.id_supplier = ' . (int) $id_supplier);
         }
         // Execute query
         $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
         // write datas
         foreach ($res as $order) {
             $content_csv = array($order['id_supplier'], self::transformText($order['supplier_name']), $order['id_lang'], $order['id_warehouse'], $order['id_supply_order_state'], $order['id_currency'], $order['reference'], $order['date_add'], $order['date_upd'], $order['date_delivery_expected'], $order['total_te'], $order['total_with_discount_te'], $order['total_ti'], $order['total_tax'], $order['discount_rate'], $order['discount_value_te'], $order['is_template'], $order['escompte'], $order['invoice_number'], $order['date_to_invoice'] == '0000-00-00' ? '' : $order['date_to_invoice'], $order['global_discount_amount'], $order['global_discount_type'], $order['shipping_amount'], self::transformText($order['description']), PHP_EOL);
             echo implode(';', $content_csv);
         }
         if ($this->controller_status == STATUS1) {
             echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
         }
         die;
     } else {
         if (Tools::isSubmit('csv_orders_details')) {
             // header
             header('Content-type: text/csv');
             header('Content-Type: application/force-download; charset=UTF-8');
             header('Cache-Control: no-store, no-cache');
             header('Content-disposition: attachment; filename="' . $this->l('supply_orders_details') . '.csv"');
             // echoes details
             $ids = array();
             foreach ($this->_list as $entry) {
                 $ids[] = $entry['id_supply_order'];
             }
             if ($this->controller_status == STATUS1) {
                 $ids = array_splice($ids, 0, ERP_STCKMGTFR);
             }
             if (count($ids) <= 0) {
                 return;
             }
             // for each supply order
             $keys = array('id_product', 'id_product_attribute', 'reference', 'supplier_reference', 'ean13', 'upc', 'name', 'unit_price_te', 'quantity_expected', 'quantity_received', 'price_te', 'discount_rate', 'discount_value_te', 'price_with_discount_te', 'tax_rate', 'tax_value', 'price_ti', 'tax_value_with_order_discount', 'price_with_order_discount_te', 'id_supply_order', 'comment');
             echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $keys)));
             // overrides keys (in order to add FORMAT calls)
             $keys = array('sod.id_product', 'sod.id_product_attribute', 'sod.reference', 'sod.supplier_reference', 'sod.ean13', 'sod.upc', 'sod.name', 'FORMAT(sod.unit_price_te, 2)', 'sod.quantity_expected', 'sod.quantity_received', 'FORMAT(sod.price_te, 2)', 'FORMAT(sod.discount_rate, 2)', 'FORMAT(sod.discount_value_te, 2)', 'FORMAT(sod.price_with_discount_te, 2)', 'FORMAT(sod.tax_rate, 2)', 'FORMAT(sod.tax_value, 2)', 'FORMAT(sod.price_ti, 2)', 'FORMAT(sod.tax_value_with_order_discount, 2)', 'FORMAT(sod.price_with_order_discount_te, 2)', 'sod.id_supply_order', 'ipsod.comment');
             foreach ($ids as $id) {
                 $query = new DbQuery();
                 $query->select(implode(', ', $keys));
                 $query->from('supply_order_detail', 'sod');
                 $query->leftJoin('supply_order', 'so', 'so.id_supply_order = sod.id_supply_order');
                 $query->leftJoin('erpip_supply_order_detail', 'ipsod', 'ipsod.id_supply_order_detail = sod.id_supply_order_detail');
                 // FILTERS SUPPLIER & WAREHOUSE
                 $id_warehouse = $this->getCurrentWarehouse();
                 if ($id_warehouse != -1) {
                     $query->where('so.id_warehouse = ' . (int) $id_warehouse);
                 }
                 $id_supplier = $this->getCurrentSupplier();
                 if ($id_supplier != -1) {
                     $query->where('so.id_supplier = ' . (int) $id_supplier);
                 }
                 $query->where('sod.id_supply_order = ' . (int) $id);
                 $query->orderBy('sod.id_supply_order_detail DESC');
                 $resource = Db::getInstance()->query($query);
                 // gets details
                 while ($row = Db::getInstance()->nextRow($resource)) {
                     $row = array_map(array('CSVCore', 'wrap'), $row);
                     $row['name'] = self::transformText($row['name']);
                     $row['reference'] = self::transformText($row['reference']);
                     $row['supplier_reference'] = self::transformText($row['supplier_reference']);
                     echo sprintf("%s\n", implode(';', $row));
                 }
             }
             if ($this->controller_status == STATUS1) {
                 echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
             }
         } else {
             if (Tools::isSubmit('csv_order_details') && Tools::getValue('id_supply_order')) {
                 $supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
                 if (Validate::isLoadedObject($supply_order)) {
                     $details = $supply_order->getEntriesCollection();
                     $details->getAll();
                     $csv = new CSV($details, $this->l('supply_order') . '_' . $supply_order->reference . '_details');
                     $csv->export();
                 }
             } else {
                 if (Tools::isSubmit('export_csv')) {
                     // get id lang
                     $id_lang = Context::getContext()->language->id;
                     // header
                     header('Content-type: text/csv');
                     header('Cache-Control: no-store, no-cache');
                     header('Content-disposition: attachment; filename="Supply order detail.csv"');
                     // puts hearder of CSV
                     $keys = array('supplier_reference', 'quantity_expected');
                     echo sprintf("%s\n", implode(';', $keys));
                     // gets global order information
                     $supply_order = new SupplyOrder((int) Tools::getValue('id_supply_order'));
                     // get supply order detail
                     $supply_order_detail = $supply_order->getEntries($id_lang);
                     // puts data
                     foreach ($supply_order_detail as $product) {
                         $row_csv = array($product['supplier_reference'], $product['quantity_expected']);
                         // puts one row
                         echo sprintf("%s\n", implode(';', array_map(array('CSVCore', 'wrap'), $row_csv)));
                     }
                     if ($this->controller_status == STATUS1) {
                         echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
                     }
                     die;
                 } else {
                     if (Tools::isSubmit('export_history')) {
                         // header
                         header('Content-type: text/csv; charset=utf-8');
                         header('Cache-Control: no-store, no-cache');
                         header('Content-disposition: attachment; filename="supply_orders_history.csv"');
                         // write headers column
                         $keys = array('id_supply_order_history', 'id_supply_order', 'id_employee', 'employee_lastname', 'employee_firstname', 'id_state', 'state', 'unit_price', 'discount_rate', 'is_canceled');
                         echo sprintf("%s\n", implode(';', $keys));
                         $query = null;
                         $query = new DbQuery();
                         $query->select('sorh.*, ipsorh.*, "state" as state');
                         $query->from('supply_order_receipt_history', 'sorh');
                         $query->leftjoin('erpip_supply_order_receipt_history', 'ipsorh', 'ipsorh.id_supply_order_receipt_history = sorh.id_supply_order_receipt_history');
                         $query->leftjoin('supply_order_detail', 'sod', 'sod.id_supply_order_detail = sorh.id_supply_order_detail');
                         $query->where('sod.id_supply_order = ' . (int) Tools::getValue('id_supply_order'));
                         if ($this->controller_status == STATUS1) {
                             $query->limit(ERP_STCKMGTFR);
                         }
                         // Execute query
                         $res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
                         // write datas
                         foreach ($res as $history) {
                             $content_csv = array($history['id_supply_order_history'], $history['id_supply_order'], $history['id_employee'], $history['employee_lastname'], $history['employee_firstname'], $history['id_state'], $history['state'], $history['unit_price'], $history['discount_rate'], $history['is_canceled'], PHP_EOL);
                             echo implode(';', $content_csv);
                         }
                         if ($this->controller_status == STATUS1) {
                             echo sprintf($this->l('Your are using a free version of 1-Click ERP which limits the export to %d lines.'), ERP_STCKMGTFR);
                         }
                         die;
                     }
                 }
             }
         }
     }
 }
/*********************************
PARAMÈTRES DE CONNEXION À LA BASE 
**********************************/
include '../../specific/inc/inc-connex-PDO.php';
$titre_page = "";
if (isset($_GET['criteria'])) {
    $where = $_GET['criteria'];
    $where = substr($where, 0, -1);
    $where = substr($where, 1);
    $where = $where . "'";
    //print_r($where);
    $sql = "SELECT \r\n" . $prefix . "subscribers.email,\r\nDATE_FORMAT(" . $prefix . "opentracking.atime, '%d/%m/%Y %Hh%imn%ss') AS openingtime,\r\n" . $prefix . "mailings.subject,\r\n" . $prefix . "mailings.mailing_id, \r\nDATE_FORMAT(" . $prefix . "mailings.started, '%d/%m/%Y %Hh%imn%ss') AS started,\r\nROUND((TIMESTAMPDIFF(SECOND,started," . $prefix . "opentracking.atime)/3600),2) AS latence,\r\n" . $prefix . "mailings.mailgroup \r\nFROM " . $prefix . "opentracking\r\nINNER JOIN " . $prefix . "subscribers ON (" . $prefix . "opentracking.subscriber_id = " . $prefix . "subscribers.subscriber_id) \r\nINNER JOIN " . $prefix . "mailings ON (" . $prefix . "mailings.mailing_id = " . $prefix . "opentracking.mailing_id) \r\nWHERE {$where}\r\nORDER BY latence DESC";
    //echo $sql;
    $result = NULL;
    $result = $id_connex->query($sql);
    if (!$result) {
        echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_opening_menu.php\">";
    }
    $data = $result->fetchAll();
    //print_r($data);
    $datas = array();
    foreach ($data as $d) {
        $datas[] = array('ID Mail' => $d[mailing_id], 'Titre du message' => $d[subject], 'Groupe' => $d[mailgroup], 'Heure d\'envoi' => $d[started], 'Heure d\'ouverture' => $d[openingtime], 'Latence (heures)' => $d[latence], 'Emails' => $d[email]);
    }
    require 'exportcsv.class.php';
    CSV::export($datas, 'pommo_ouvertures_par_critere');
    //print_r($datas);
} else {
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_opening_menu.php\">";
}
Pommo::kill();
function exportCSV($artistid)
{
    global $tableprefix;
    $adminCurrency = GetAdminCurrency();
    $currencycode = getSellerCurrencySybol($artistid);
    $currencySymbol = getCurrencySybol($currencycode);
    $csv = new CSV(array('Order #', 'Order Total', 'Commission (%)', 'Total Tax Amount', 'Total Shipping Amount', 'Product Total Amount', 'Amount Received [ Product Total Amount - Admin Commision  + Product Shipping + Tax ]', 'Date Paid(mm/dd/yyyy)', 'Payment Status'), ";");
    $qryopt = "";
    $qrybtw = "";
    $sql = " SELECT sp.*,s.artist_name FROM " . $tableprefix . "artist_payments sp\n\t\tINNER JOIN  " . $tableprefix . "artists s ON sp.artist_id = s.artist_id\n                INNER JOIN  " . $tableprefix . "orders o ON o.order_id = sp.order_id" . $qryopt . "\n\t\tWHERE sp.artist_id = '" . addslashes($artistid) . "' AND o.vpayment_status='C'" . $qrybtw . "order by sp.artist_payment_id DESC   ";
    $rs = mysql_query($sql) or die("qqqq" . mysql_error());
    $result = mysql_query($sql) or die("www" . mysql_error());
    if (mysql_num_rows($result) > 0) {
        $i = 1;
        $total = 0;
        while ($row = mysql_fetch_array($rs)) {
            $txtPaymentDate = isNotNull($row["payment_date"] and $row["payment_date"] != "0000-00-00") ? dateFormat($row["payment_date"], "Y-m-d", "m/d/Y") : " Not Yet Settled;";
            if ($row['payment_status'] == 'P') {
                $Paymentstatus = 'Pending';
            } else {
                $Paymentstatus = 'Paid';
            }
            $qryopt1 = " ";
            $taxtotal = 0;
            $sql1 = "SELECT o.*, u.user_name, u.email, od.product_discount,sum( (od.product_price-(od.product_price*product_discount/100)) * od.product_quantity) as total,sum(od.product_tax)as taxtotal\n\t\tFROM " . $tableprefix . "orders o\n\t\tINNER JOIN " . $tableprefix . "users u ON o.user_id = u.user_id\n\t\tINNER JOIN " . $tableprefix . "order_details od ON o.order_id = od.order_id\n\t\tWHERE od.artist_id = '" . $artistid . "'\n                AND o.order_id = " . $row['order_id'] . "\n\t\tAND o.vpayment_status != 'P'" . $qryopt1 . " GROUP BY o.order_id ORDER BY o.order_date,o.order_id DESC ";
            $sellerCurrency = getSellerCurrencySybol($artistid);
            $result1 = mysql_query($sql1);
            if (mysql_num_rows($result1) != 0) {
                $row1 = mysql_fetch_array($result1);
                $ordernumber = $row1["order_id"];
                $username = $row1["user_name"];
                if ($row1['vorder_currency'] != $sellerCurrency and $row1['vorder_currency'] != "USD") {
                    $get_conversion_price = getCurrencyrate($sellerCurrency);
                    $ordertotal = $row1["total"];
                } else {
                    $ordertotal = $row1["total"];
                }
                $taxtotal = $row1["taxtotal"];
            }
            $select_shipping_cost = "SELECT sd.vshipping_name, sd.nshipping_cost,od.product_name\n                           FROM " . $tableprefix . "shipping_details sd," . $tableprefix . "order_details  od\n                          WHERE od.product_id=sd.nshp_productid\n                            AND norder_id = " . $row['order_id'] . "\n                            AND nshp_status = 'C'\n                            AND od.artist_id = '" . $artistid . "'\n                       GROUP BY sd.nshp_productid order by  od.product_name";
            $result_shipping_cost = mysql_query($select_shipping_cost);
            if (mysql_num_rows($result_shipping_cost) > 0) {
                $totalshippingcost = 0;
                while ($shipping_row = mysql_fetch_array($result_shipping_cost)) {
                    $totalshippingcost += $shipping_row['nshipping_cost'];
                }
            }
            $csv->addRow(array($row["order_id"], html_entity_decode($adminCurrency['currency_symbol']) . " " . number_format($row["seller_order_total_amount"], 2, '.', ''), $row["commission_percentage"], html_entity_decode($adminCurrency['currency_symbol']) . " " . number_format($taxtotal, 2, '.', ''), html_entity_decode($adminCurrency['currency_symbol']) . " " . number_format($totalshippingcost, 2, '.', ''), html_entity_decode($adminCurrency['currency_symbol']) . " " . number_format($row["total_amount"], 2, '.', ''), html_entity_decode($adminCurrency['currency_symbol']) . " " . number_format($row["amount_paid"], 2, '.', ''), $txtPaymentDate, $Paymentstatus));
            $artistName = $row["artist_name"];
            $i++;
            $total += $row["amount_paid"];
        }
        if ($total > 0) {
            $csv->addRow(array(' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '));
            $csv->addRow(array(' ', ' ', ' ', ' ', ' ', ' ', 'Total Seller Amount Received', html_entity_decode($currencySymbol) . " " . number_format($total, 2, '.', ''), ' '));
        }
    }
    // export csv as a download
    $filename = 'order_payment_details_' . $artistName;
    //$filename = 'order_payment_details'.$artistid;
    $csv->export($filename);
    // *or* pass the csv data to a variable as a string
    //$string = $csv;
}
 protected function renderCSV()
 {
     if (Tools::isSubmit('export_csv')) {
         $stckmgtfr = ERP_STCKMGTFR;
         // get all filter
         //
         // category filer
         $id_category = Tools::isSubmit('id_category') ? (int) Tools::getValue('id_category') : -1;
         $query = new DbQuery();
         $query->select('id_product');
         $query->from('category_product');
         $query->where('id_category = ' . $id_category);
         $categories_exec = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
         $categories = null;
         foreach ($categories_exec as $category) {
             $categories[] = (int) $category['id_product'];
         }
         //If no data
         if ($id_category != -1 && $categories == null) {
             $this->displayWarning($this->l('No data to export in this category !'));
             return;
         }
         if ($categories != null) {
             $categories = array_unique($categories);
         }
         // supplier filter
         $id_supplier = Tools::isSubmit('id_supplier') ? (int) Tools::getValue('id_supplier') : -1;
         $query = null;
         $query = new DbQuery();
         $query->select('id_product');
         $query->from('product_supplier');
         $query->where('id_supplier = ' . $id_supplier);
         $suppliers_exec = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
         $suppliers = null;
         foreach ($suppliers_exec as $supplier) {
             $suppliers[] = (int) $supplier['id_product'];
         }
         //If no data
         if ($id_supplier != -1 && $suppliers == null) {
             $this->displayWarning($this->l('No data to export with this supplier !'));
             return;
         }
         if ($suppliers != null) {
             $suppliers = array_unique($suppliers);
         }
         // Filter by manufacturer
         $id_manufacturer = Tools::isSubmit('id_manufacturer') ? (int) Tools::getValue('id_manufacturer') : -1;
         $query = null;
         $query = new DbQuery();
         $query->select('id_product');
         $query->from('manufacturer', 'm');
         $query->innerjoin('product', 'p', 'm.id_manufacturer = p.id_manufacturer');
         $query->where('m.id_manufacturer = ' . $id_manufacturer);
         $manufacturers_exec = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
         $manufacturers = null;
         foreach ($manufacturers_exec as $manufacturer) {
             $manufacturers[] = (int) $manufacturer['id_product'];
         }
         //If no data
         if ($id_manufacturer != -1 && $manufacturers == null) {
             $this->displayWarning($this->l('No data to export with this manufacturer !'));
             return;
         }
         if ($manufacturers != null) {
             $manufacturers = array_unique($manufacturers);
         }
         // Quantity filter
         $table_quantity = 'quantity';
         $moreless = Tools::isSubmit('moreless') && in_array(Tools::getValue('moreless'), array('=', '>', '<')) ? Tools::getValue('moreless') : -1;
         $quantity = Tools::isSubmit('quantity_filter') ? (int) Tools::getValue('quantity_filter') : -1;
         if ($this->advanced_stock_management) {
             $id_warehouse = $this->getCurrentCoverageWarehouse();
             header('Content-type: text/csv');
             header('Cache-Control: no-store, no-cache');
             header('Content-disposition: attachment; filename=stock_' . date('Y-m-d_His') . '.csv');
             header('charset=iso-8859-1');
             // product attribute
             $combination = new DbQuery();
             $combination->select('
                         pa.id_product as id_product, 
                         pa.id_product_attribute as id_product_attribute, 
                         pa.reference as reference,
                         pa.ean13 as ean13, 
                         w.name as warehouse, 
                         wpl.id_warehouse_product_location,
                         wpl.location as location, 
                         area.name as areaname, 
                         subarea.name as subareaname');
             //if ($id_warehouse != -1)
             $combination->select('IFNULL(s.physical_quantity, 0) as physical_quantity, IFNULL(s.usable_quantity, 0) as usable_quantity');
             //else
             //$combination->select('SUM(IFNULL(s.physical_quantity, 0)) as physical_quantity, SUM(IFNULL(s.usable_quantity, 0)) as usable_quantity');
             $combination->from('product_attribute', 'pa');
             $combination->innerjoin('product', 'p', 'pa.id_product = p.id_product');
             $combination->leftjoin('warehouse_product_location', 'wpl', '(wpl.id_product = p.id_product AND wpl.id_product_attribute = IFNULL(pa.id_product_attribute, 0)' . ($id_warehouse != -1 ? ' AND wpl.id_warehouse = ' . (int) $id_warehouse : '') . ')');
             $combination->leftjoin('stock', 's', '(s.id_product = pa.id_product AND s.id_product_attribute = IFNULL(pa.id_product_attribute, 0))');
             $combination->leftjoin('warehouse', 'w', 's.id_warehouse = w.id_warehouse');
             $combination->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
             $combination->leftjoin('erpip_zone', 'area', '(area.id_erpip_zone = ewpl.id_zone_parent)');
             $combination->leftjoin('erpip_zone', 'subarea', '(subarea.id_erpip_zone = ewpl.id_zone)');
             // apply filters
             // warehouse
             if ($id_warehouse != -1) {
                 $combination->where('s.id_warehouse = ' . (int) $id_warehouse . ' OR wpl.id_warehouse = ' . (int) $id_warehouse);
                 // area
                 if (Tools::isSubmit('area')) {
                     $combination->where('ewpl.id_zone_parent= ' . Tools::getValue('area'));
                     // sub area
                     if (Tools::isSubmit('subarea')) {
                         $combination->where('ewpl.id_zone= ' . Tools::getValue('subarea'));
                     }
                 }
             }
             if ($id_category != -1) {
                 $combination->where('pa.id_product IN (' . implode(',', array_map('intval', $categories)) . ')');
             }
             if ($id_supplier != -1) {
                 $combination->where('pa.id_product IN (' . implode(',', array_map('intval', $suppliers)) . ')');
             }
             if ($id_manufacturer != -1) {
                 $combination->where('pa.id_product IN (' . implode(',', array_map('intval', $manufacturers)) . ')');
             }
             if ($moreless != -1) {
                 if ($quantity > 0) {
                     $where_quantity_filter = ' physical_quantity ' . $moreless . ' ' . (int) $quantity;
                     if ($moreless == '=' && $quantity == 0 || $moreless == "<" && $quantity > 0) {
                         $where_quantity_filter .= ' OR physical_quantity IS NULL ';
                     }
                 }
                 $combination->where($where_quantity_filter);
             }
             $combination->groupBy('pa.id_product, pa.id_product_attribute, w.id_warehouse');
             $combination->orderBy('pa.id_product, pa.id_product_attribute');
             if ($this->controller_status == STATUS1) {
                 $combination->limit($stckmgtfr);
                 $this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $stckmgtfr);
             }
             $combinations = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($combination);
             // List of product id
             $ids = array();
             foreach ($combinations as $combination) {
                 $ids[] = $combination['id_product'];
             }
             $ids = array_unique($ids);
             // Product without attribute
             $product = new DbQuery();
             $product->select('p.id_product as id_product, 
                         0 as id_product_attribute, 
                         p.reference as reference,
                         p.ean13 as ean13, 
                         w.name as warehouse, 
                         wpl.id_warehouse_product_location,
                         wpl.location as location, 
                         area.name as areaname, 
                         subarea.name as subareaname');
             //if ($id_warehouse != -1)
             $product->select('IFNULL(s.physical_quantity, 0) as physical_quantity, IFNULL(s.usable_quantity, 0) as usable_quantity');
             // else
             //$product->select('SUM(IFNULL(s.physical_quantity, 0)) as physical_quantity, SUM(IFNULL(s.usable_quantity, 0)) as usable_quantity');
             $product->from('product', 'p');
             $product->leftjoin('warehouse_product_location', 'wpl', '(wpl.id_product = p.id_product AND wpl.id_product_attribute = 0' . ($id_warehouse != -1 ? ' AND wpl.id_warehouse = ' . (int) $id_warehouse : '') . ')');
             $product->leftjoin('stock', 's', '(s.id_product = p.id_product)');
             $product->leftjoin('erpip_warehouse_product_location', 'ewpl', '(wpl.id_warehouse_product_location = ewpl.id_warehouse_product_location)');
             $product->leftjoin('warehouse', 'w', 's.id_warehouse = w.id_warehouse');
             $product->leftjoin('erpip_zone', 'area', '(area.id_erpip_zone = ewpl.id_zone_parent)');
             $product->leftjoin('erpip_zone', 'subarea', '(subarea.id_erpip_zone = ewpl.id_zone)');
             // apply filters
             if ($id_warehouse != -1) {
                 $product->where('s.id_warehouse = ' . (int) $id_warehouse . ' OR wpl.id_warehouse = ' . (int) $id_warehouse);
                 // Area
                 if (Tools::isSubmit('area')) {
                     $product->where('ewpl.id_zone_parent= ' . intval(Tools::getValue('area')));
                     // sub area
                     if (Tools::isSubmit('subarea')) {
                         $product->where('ewpl.id_zone= ' . intval(Tools::getValue('subarea')));
                     }
                 }
             }
             if ($id_category != -1) {
                 $product->where('p.id_product IN (' . implode(', ', array_map('intval', $categories)) . ')');
             }
             if ($id_supplier != -1) {
                 $product->where('p.id_product IN (' . implode(', ', array_map('intval', $suppliers)) . ')');
             }
             if ($id_manufacturer != -1) {
                 $product->where('p.id_product IN (' . implode(', ', array_map('intval', $manufacturers)) . ')');
             }
             if ($moreless != -1) {
                 if ($quantity > 0) {
                     $where_quantity_filter = ' physical_quantity ' . $moreless . ' ' . (int) $quantity;
                     if ($moreless == '=' && $quantity == 0 || $moreless == "<" && $quantity > 0) {
                         $where_quantity_filter .= ' OR physical_quantity IS NULL';
                     }
                 }
                 $product->where($where_quantity_filter);
             }
             if (count($ids) > 0) {
                 $product->where("p.id_product NOT IN (" . implode(',', array_map('intval', $ids)) . ")");
             }
             $product->groupBy('p.id_product, w.id_warehouse');
             $product->orderBy('p.id_product');
             if ($this->controller_status == STATUS1) {
                 $product->limit($stckmgtfr);
                 $this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $stckmgtfr);
             }
             $products = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($product);
             // $query = array_merge($products, $combinations);
             $query = array_merge($products, $combinations);
             // we sort by id_product and id_product_attribute
             usort($query, array($this, "idproductSort"));
             if ($this->controller_status == STATUS1) {
                 $query = array_splice($query, 0, $stckmgtfr);
             }
             $csv_header_columns = array($this->l('EAN13'), $this->l('ID_PRODUCT'), $this->l('ID_PRODUCT_ATTRIBUTE'), $this->l('REFERENCE'), $this->l('PHYSICAL_QUANTITY'), $this->l('USABLE_QUANTITY'), self::transformText($this->l('WAREHOUSE')), $this->l('AREA'), $this->l('SUBAREA'), $this->l('LOCATION'), $this->l('WARNING'));
             echo implode(';', $csv_header_columns) . "\r\n";
             // generate csv file
             foreach ($query as $product) {
                 //alert in the case where the product has stock in warehouse without be located in this warehouse
                 $warning = '';
                 if (is_null($product['id_warehouse_product_location'])) {
                     $warning = sprintf($this->l('Product has stock in %s warehouse without being registered in this warehouse !'), $product['warehouse']);
                 }
                 $csv_value_columns = array(self::transformText($product['ean13']), $product['id_product'], $product['id_product_attribute'], self::transformText($product['reference']), self::transformText($product['physical_quantity']), self::transformText($product['usable_quantity']), self::transformText($product['warehouse']), self::transformText($product['areaname']), self::transformText($product['subareaname']), self::transformText($product['location']), self::transformText($warning));
                 echo implode(';', $csv_value_columns) . "\r\n";
             }
             echo sprintf($this->l('You are using the free version of 1-Click ERP which limits the export to %d products'), $stckmgtfr);
         } else {
             // we work in different stock table while advanced stock is disabled
             $table_stock = 'StockAvailable';
             $table_quantity = 'quantity';
             // create collection width current filter
             $id_lang = Context::getContext()->language->id;
             $stock = new Collection($table_stock, $id_lang);
             if ($id_category != -1) {
                 $stock->where('id_product', 'in', $categories);
             }
             if ($id_supplier != -1) {
                 $stock->where('id_product', 'in', $suppliers);
             }
             if ($id_manufacturer != -1) {
                 $stock->where('id_product', 'in', $manufacturers);
             }
             if ($moreless != -1) {
                 if ($quantity > 0) {
                     $where_quantity_filter = $table_quantity . ' ' . $moreless . ' ' . $quantity;
                     if ($moreless == '=' && $quantity == 0 || $moreless == "<" && $quantity > 0) {
                         $where_quantity_filter .= ' OR ' . $table_quantity . ' IS NULL';
                     }
                 }
                 $stock->sqlWhere($where_quantity_filter);
             }
             $stock->orderBy('id_product');
             $stock->orderBy('id_product_attribute');
             $stock->getAll();
             if ($this->controller_status == STATUS1) {
                 $stock = array_splice($stock, 0, $stckmgtfr);
                 $this->informations[] = sprintf($this->l('You are using the free version of 1-Click ERP which limits document editing to %d products'), $stckmgtfr);
             }
             // generation of CSV
             $csv = new CSV($stock, $this->l('stock') . '_' . date('Y-m-d_His'));
             $csv->export();
         }
         die;
     }
 }
while ($row = $result->fetch()) {
    $mailingid[] = $row[mailing_id];
    $donnees_initiales = array_map(create_function('$idmail', 'return "SELECT o.mailing_id, table_mailing_subject, table_mailing_mailgroup, table_mailing_sent, DATE_FORMAT(table_mailing_started, \'%d/%m/%Y %Hh%imn%ss\') AS started, ROUND((TIMESTAMPDIFF(SECOND,started,FROM_UNIXTIME(AVG(DISTINCT(UNIX_TIMESTAMP(o.atime)))))/3600),2) AS latence, COUNT(DISTINCT(o.subscriber_id)) AS messages_ouverts, ROUND(((COUNT(DISTINCT(o.subscriber_id))/table_mailing_sent)*100),2) AS taux_ouvertures FROM table_opentracking AS o INNER JOIN (SELECT subscriber_id, MIN(atime) AS minatime FROM table_opentracking WHERE mailing_id = ".$idmail." GROUP BY subscriber_id) AS z ON (o.atime = z.minatime) AND (o.subscriber_id = z.subscriber_id) INNER JOIN table_subscribers ON (o.subscriber_id = table_subscriber_id) INNER JOIN table_mailings ON (table_mailing_id = o.mailing_id) UNION ";'), array_values($mailingid));
}
$requetebrute = implode($donnees_initiales);
$requeteintermediaire1 = substr($requetebrute, 0, -7);
$requeteintermediaire2 = $requeteintermediaire1 . " ORDER BY mailing_id DESC";
$requetefinale = str_replace(table_subscribers, $prefix . "subscribers", $requeteintermediaire2);
$requetefinale = str_replace(table_subscriber_id, $prefix . "subscribers.subscriber_id", $requetefinale);
$requetefinale = str_replace(table_mailings, $prefix . "mailings", $requetefinale);
$requetefinale = str_replace(table_mailing_id, $prefix . "mailings.mailing_id", $requetefinale);
$requetefinale = str_replace(table_mailing_started, $prefix . "mailings.started", $requetefinale);
$requetefinale = str_replace(table_mailing_sent, $prefix . "mailings.sent", $requetefinale);
$requetefinale = str_replace(table_mailing_subject, $prefix . "mailings.subject", $requetefinale);
$requetefinale = str_replace(table_mailing_mailgroup, $prefix . "mailings.mailgroup", $requetefinale);
$requetefinale = str_replace(table_opentracking, $prefix . "opentracking", $requetefinale);
$result = NULL;
$allclicktracking = $id_connex->query($requetefinale);
if (!$allclicktracking) {
    echo "<meta http-equiv=\"refresh\" content=\"0;URL=tracking_opening_menu.php\">";
}
$data = $allclicktracking->fetchAll();
// print_r($data);
$datas = array();
foreach ($data as $d) {
    $datas[] = array('ID Mail' => $d[mailing_id], 'Titre du message' => $d[subject], 'Heure d\'envoi' => $d[started], 'Groupe' => $d[mailgroup], 'Envois' => $d[sent], 'Messages ouverts' => $d[messages_ouverts], 'CTR (% ouvertures)' => $d[taux_ouvertures], 'Latence (heures)' => $d[latence]);
}
require 'exportcsv.class.php';
CSV::export($datas, 'pommo_tracking_ouvertures');
// print_r($datas);
Pommo::kill();