/** * 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();