function ImportData($sql, $fileName = "har_excel.xls", $download = false)
{
$excel = new ExcelWriter($fileName);
if ($excel == false) {
$this->error = $excel->error;
return false;
}
$this->db->query($sql);
if ($this->db->numRows() == 0) {
$this->error = "No data found in the table";
return false;
}
if ($row = $this->db->fetchAssoc()) {
for ($i = 0; $i < count($row); $i++) {
$fields[] = $this->db->fieldName($i);
}
$excel->writeLine($fields);
do {
$excel->writeLine($row);
} while ($row = $this->db->fetchAssoc());
}
$excel->close();
$this->db->close();
if ($download) {
if (!headers_sent()) {
$this->download_file($fileName, true);
} else {
$this->error = "Error :Headers already Sent.Can't Download file.";
}
}
return;
}
/**
* Function generates a excel product report for google base
*
*
* @return file
*/
function googleProduct()
{
include "classes/Lib/excelwriter.inc.php";
$excel = new ExcelWriter("GoogleBase_Product_Feed.xls");
if ($excel == false) {
echo $excel->error;
}
$myArr = array("Product Id", "Product Title", "Description", "Product Price", "Link", "Brand", "Image Link", "Weight");
$excel->writeLine($myArr);
$sql = 'select product_id,title,description,price,brand,thumb_image,weight from products_table';
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
$product_id = $obj->records[$i]['product_id'];
$title = $obj->records[$i]['title'];
$description = strip_tags($obj->records[$i]['description']);
$price = $obj->records[$i]['price'];
$brand = $obj->records[$i]['brand'];
$thumb_image = $obj->records[$i]['thumb_image'];
$image_link = $_SERVER['SERVER_NAME'] . '/' . $thumb_image;
$weight = $obj->records[$i]['weight'];
$link = $_SERVER['SERVER_NAME'] . '/?do=prodetail&action=showprod&id=' . $product_id;
$excel->writeRow();
$excel->writeCol($product_id);
$excel->writeCol($title);
$excel->writeCol($description);
$excel->writeCol($price);
$excel->writeCol($link);
$excel->writeCol($brand);
$excel->writeCol($image_link);
$excel->writeCol($weight);
}
$excel->close();
}
if (strpos($_SERVER['USER'], 'MSIE')) {
// IE cannot download from sessions without a cache
header('Cache-Control: public');
} else {
//header("Cache-Control: no-cache, must-revalidate");
header("Cache-Control: no-cache");
}
$file = "GoogleBase_Product_Feed.xls";
//chmod ($file, 0755);
header("Pragma: no-cache");
header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
header("Connection: close");
header("Content-Disposition: attachment; filename=" . $file . "\n");
header("Content-Transfer-Encoding: binary\n");
header("Content-length: " . (string) filesize("{$file}"));
$fd = fopen($file, "rb");
fpassthru($fd);
}
$arquivoDiretorio = "../relatorios/conciliacao_franquia/" . $nomeArquivo;
$excel = new ExcelWriter($arquivoDiretorio);
if ($excel == false) {
echo $excel->error;
exit;
}
//Unidade
$myArr = array('Relação de pedidos em conciliação franquia:' . $emp->fantasia);
$excel->writeLine($myArr);
//periodo
$myArr = array('Até: ' . date('d') . '/' . date('m') . '/' . date('Y'));
$excel->writeLine($myArr);
//espaço
$myArr = array(' ');
$excel->writeLine($myArr);
//Escreve o nome dos campos de uma tabela
$excel->writeLine(array('PEDIDO', 'ABERTO HÁ (dias) ', 'ATENDENTE'));
$total_comissao = 0;
$total = 0;
$cont = 0;
$old_id_usuario = '';
$pedidos = $pedidoDAO->listarConciliacaoFranquia($emp->id_empresa);
foreach ($pedidos as $pe) {
$diff = time() - strtotime($pe->data_atividade);
$diff = number_format($diff / 60 / 60 / 24, 0);
$excel->writeLine(array('#' . $pe->id_pedido . '/' . $pe->ordem, $diff, $pe->atendente, date("d/m/Y", strtotime($pe->data_atividade))));
}
$excel->close();
$relatorioDAO->registraRel($id_empresa, $arquivoDiretorio, 'conciliação franquia');
}
echo '</pre>';
/**
* Function exports the admin activity report from the database into various file format
* Excel/CSV/TSV/XML Formats
*
* @return file
*/
function adminActivityDataExport()
{
if ($_POST['export'] == 'excel') {
include "classes/Lib/excelwriter.inc.php";
$excel = new ExcelWriter("adminactivity_Detail.xls");
if ($excel == false) {
echo $excel->error;
}
$myArr = array("SlNo", "UserId", "Url", "VisitedOn");
$excel->writeLine($myArr);
$j = 1;
$sql = 'select user_id,url,visited_on from admin_activity_table';
$obj = new Bin_Query();
$obj->executeQuery($sql);
if ($obj->executeQuery($sql)) {
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
$user_id = $obj->records[$i]['user_id'];
$url = $obj->records[$i]['url'];
$visited_on = $obj->records[$i]['visited_on'];
$excel->writeRow();
$excel->writeCol($j);
$excel->writeCol($user_id);
$excel->writeCol($url);
$excel->writeCol($visited_on);
$j++;
}
$excel->close();
}
if (strpos($_SERVER['USER'], 'MSIE')) {
// IE cannot download from sessions without a cache
header('Cache-Control: public');
} else {
//header("Cache-Control: no-cache, must-revalidate");
header("Cache-Control: no-cache");
}
$file = "adminactivity_Detail.xls";
//chmod ($file, 0755);
header("Pragma: no-cache");
header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
header("Connection: close");
header("Content-Disposition: attachment; filename=" . $file . "\n");
header("Content-Transfer-Encoding: binary\n");
header("Content-length: " . (string) filesize("{$file}"));
$fd = fopen($file, "rb");
fpassthru($fd);
/*function xlsBOF()
{
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF()
{
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value)
{
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value )
{
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
//Send header
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=user_report.xls");
header("Content-Transfer-Encoding: binary ");
xlsBOF();
xlsWriteLabel(1,0,"No");
xlsWriteLabel(1,1,"First Name");
xlsWriteLabel(1,2,"Last Name");
xlsWriteLabel(1,3,"Display Name");
xlsWriteLabel(1,4,"Email");
xlsWriteLabel(1,5,"Date of Joining");
$xlsRow = 2;
$j=1;
//Query
$sqlselect = "select user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if($obj->executeQuery($sqlselect))
{
$count=count($obj->records);
for($i=0;$i<$count;$i++)
{
$url = $obj->records[$i]['user_fname'];
$last_name = $obj->records[$i]['user_lname'];
$display_name = $obj->records[$i]['user_display_name'];
$email = $obj->records[$i]['user_email'];
$doj = $obj->records[$i]['user_doj'];
xlsWriteLabel($xlsRow,0,"$j");
xlsWriteLabel($xlsRow,1,$url);
xlsWriteLabel($xlsRow,2,$last_name);
xlsWriteLabel($xlsRow,3,$display_name);
xlsWriteLabel($xlsRow,4,$email);
xlsWriteLabel($xlsRow,5,$doj);
$xlsRow++;
$j++;
}
}
xlsEOF();
exit(); */
} else {
if ($_POST['export'] == 'xml') {
$sqlselect = "select user_id,url,visited_on from admin_activity_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
header("Content-Type: text/xml");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: xml/force-download");
header("Content-Type: xml/octet-stream");
header("Content-Type: xml/download");
header("Content-Disposition: attachment;filename=adminactivity_report.xml");
header("Content-Transfer-Encoding: binary ");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
echo "<activitydetails>\n";
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
echo "<user_id>" . $obj->records[$i]['user_id'] . "</user_id>\n";
$url = str_replace("&", "&", $obj->records[$i]['url']);
echo "<url>" . $url . "</url>\n";
echo "<visitedon>" . $obj->records[$i]['visited_on'] . "</visitedon>\n";
}
echo "</activitydetails>\n";
exit;
}
} else {
if ($_POST['export'] == 'csv') {
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sqlselect = "select user_id,url,visited_on from admin_activity_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $csv_enclosed . user_id . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . url . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . VistedOn . $csv_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_id'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['url'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['visited_on'] . $csv_enclosed;
}
$out .= $schema_insert;
$out .= $csv_terminated;
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=adminactivity_report.csv");
echo $out;
exit;
} else {
if ($_POST['export'] == 'tab') {
$tab_terminated = "\n";
$tab_separator = "->";
$tab_enclosed = '"';
$tab_escaped = "\\";
$sqlselect = "select user_id,url,visited_on from admin_activity_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $tab_enclosed . UserId . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Url . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . VisitedOn . $tab_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_id'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['url'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['visited_on'] . $tab_enclosed;
}
$out .= $schema_insert;
$out .= $tab_terminated;
}
/*header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: application/tab");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=user_report.tab");*/
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-Type: tab/force-download");
header("Content-Type: tab/octet-stream");
header("Content-Type: tab/download");
header("Content-Disposition: attachment;filename=adminactivity_report.tsv");
header("Content-Transfer-Encoding: binary ");
echo $out;
exit;
}
}
}
}
}
public function index()
{
$this->load->language('report/export_reports');
$this->load->model('report/export_reports');
$this->document->setTitle($this->language->get('heading_title'));
$data['heading_title'] = $this->language->get('heading_title');
$data['token'] = $this->request->get['token'];
if (isset($this->request->get['error_warning'])) {
$data['error_warning'] = $this->request->get['error_warning'];
} else {
$data['error_warning'] = '';
}
if (isset($this->request->get['success'])) {
$data['success'] = $this->request->get['success'];
} else {
$data['success'] = '';
}
$data['entry_date_start'] = $this->language->get('entry_date_start');
$data['entry_date_end'] = $this->language->get('entry_date_end');
$data['entry_group'] = $this->language->get('entry_group');
$data['entry_status'] = $this->language->get('entry_status');
$data['entry_type'] = $this->language->get('entry_type');
$data['entry_payment_method'] = $this->language->get('entry_payment_method');
$data['entry_shipping_method'] = $this->language->get('entry_shipping_method');
$data['entry_zone'] = $this->language->get('entry_zone');
$data['entry_options'] = $this->language->get('entry_options');
$data['text_list'] = $this->language->get('text_list');
$data['text_select'] = $this->language->get('text_select');
$data['text_all_status'] = $this->language->get('text_all_status');
$data['text_all_methods'] = $this->language->get('text_all_methods');
$data['text_all_zones'] = $this->language->get('text_all_zones');
$data['text_all_options'] = $this->language->get('text_all_options');
$data['text_all_shipping_methods'] = $this->language->get('text_all_shipping_methods');
$data['button_generate'] = $this->language->get('button_generate');
$data['button_delete'] = $this->language->get('button_delete');
$data['column_date_generation'] = $this->language->get('column_date_generation');
$data['column_type'] = $this->language->get('column_type');
$data['column_link'] = $this->language->get('column_link');
$url = '';
if (isset($this->request->get['filter_date_start'])) {
$url .= '&filter_date_start=' . $this->request->get['filter_date_start'];
$filter_date_start = $this->request->get['filter_date_start'];
} else {
$filter_date_start = '';
}
if (isset($this->request->get['filter_date_end'])) {
$url .= '&filter_date_end=' . $this->request->get['filter_date_end'];
$filter_date_end = $this->request->get['filter_date_end'];
} else {
$filter_date_end = '';
}
if (isset($this->request->get['filter_store'])) {
$url .= '&filter_store=' . $this->request->get['filter_store'];
$filter_store = $this->request->get['filter_store'];
} else {
$filter_store = '';
}
if (isset($this->request->get['filter_type'])) {
$url .= '&filter_type=' . $this->request->get['filter_type'];
$filter_type = $this->request->get['filter_type'];
} else {
$filter_type = '';
}
if (isset($this->request->get['filter_order_status_id'])) {
$url .= '&filter_order_status_id=' . $this->request->get['filter_order_status_id'];
$filter_order_status_id = $this->request->get['filter_order_status_id'];
} else {
$filter_order_status_id = '';
}
if (isset($this->request->get['filter_payment_method'])) {
$url .= '&filter_payment_method=' . $this->request->get['filter_payment_method'];
$filter_payment_method = $this->request->get['filter_payment_method'];
} else {
$filter_payment_method = '';
}
if (isset($this->request->get['filter_shipping_method'])) {
$url .= '&filter_shipping_method=' . $this->request->get['filter_shipping_method'];
$filter_shipping_method = $this->request->get['filter_shipping_method'];
} else {
$filter_shipping_method = '';
}
if (isset($this->request->get['filter_zone'])) {
$url .= '&filter_zone=' . $this->request->get['filter_zone'];
$filter_zone = $this->request->get['filter_zone'];
} else {
$filter_zone = '';
}
if (isset($this->request->get['filter_sizes'])) {
$url .= '&filter_sizes=' . $this->request->get['filter_sizes'];
$filter_options = $this->request->get['filter_sizes'];
} else {
$filter_options = '';
}
$data['breadcrumbs'] = array();
$data['breadcrumbs'][] = array('text' => $this->language->get('text_home'), 'href' => $this->url->link('common/dashboard', 'token=' . $this->session->data['token'], 'SSL'));
$data['breadcrumbs'][] = array('text' => $this->language->get('heading_title'), 'href' => $this->url->link('report/export_reports', 'token=' . $this->session->data['token'] . $url, 'SSL'));
//DEFINI OS TIPOS DE RELATORIOS
$data['types'][] = array('name' => 'Aniversariantes', 'type' => 'customers_birthday');
$data['types'][] = array('name' => 'Balanço', 'type' => 'balance');
$data['types'][] = array('name' => 'Clientes', 'type' => 'customers');
$data['types'][] = array('name' => 'Clientes Compradores', 'type' => 'customers_order');
$data['types'][] = array('name' => 'Clientes Inativos', 'type' => 'inactive_customers');
$data['types'][] = array('name' => 'Clientes do Carrinho Abandonado', 'type' => 'customers_abandoned_cart');
$data['types'][] = array('name' => 'Estoque Geral', 'type' => 'general_stock');
$data['types'][] = array('name' => 'Newsletter', 'type' => 'newsletter');
$data['types'][] = array('name' => 'Notas Fiscais Geradas', 'type' => 'nfe');
$data['types'][] = array('name' => 'Pedidos', 'type' => 'orders');
$data['types'][] = array('name' => 'Produtos Avise-me', 'type' => 'products_notifyme');
$data['types'][] = array('name' => 'Produtos Notificados', 'type' => 'products_notified');
$data['types'][] = array('name' => 'Produtos e Pedidos', 'type' => 'products_orders');
$data['types'][] = array('name' => 'Produtos em Estoque', 'type' => 'positive_stock_products');
$data['types'][] = array('name' => 'Produtos do Carrinho Abandonado', 'type' => 'products_abandoned_cart');
$data['types'][] = array('name' => 'Produtos sem Estoque', 'type' => 'out_stock_products');
$data['types'][] = array('name' => 'Promoções Ativas', 'type' => 'active_sales');
$data['types'][] = array('name' => 'Promoções Vencidas', 'type' => 'expired_sales');
$data['types'][] = array('name' => 'Ranking de Produtos', 'type' => 'products_rank');
$data['types'][] = array('name' => 'Produtos URL', 'type' => 'products_URL');
//RECUPERA AS OPÇÕES HABILITADAS NA LOJA
$option_values = $this->model_report_export_reports->getOptions();
$data['options'] = array();
foreach ($option_values as $option) {
$data['options'][] = array('option_value_id' => $option['option_value_id'], 'name' => $option['name']);
}
//DEFINE OS TIPOS DE RELATORIOS
$data['filter_date_start'] = $filter_date_start;
$data['filter_date_end'] = $filter_date_end;
$data['filter_type'] = $filter_type;
$data['filter_zone'] = $filter_zone;
$data['filter_order_status_id'] = $filter_order_status_id;
$data['filter_payment_method'] = $filter_payment_method;
$data['filter_shipping_method'] = $filter_shipping_method;
$data['filter_options'] = $filter_options;
if ($filter_type == '0') {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=É necessário selecionar o tipo de relatório!&token=' . $this->request->get['token'], 'SSL'));
}
//Incluir a classe excelwriter
require_once DIR_SYSTEM . "library/excel/excelwriter.inc.php";
$name_file = DIR_APPLICATION . 'controller/report/reports/report_' . $filter_type . '_' . date('d-m-Y-H-i-s') . '.xls';
//GERA OS RELATORIOS
//PRODUTOS E PEDIDOS
if ($data['filter_type'] == 'products_orders') {
$results = $this->model_report_export_reports->getProductsOrders($filter_date_start, $filter_date_end);
$excel = new ExcelWriter($name_file);
$myArr = array('Pedido', 'Cliente', 'Email', 'Data', 'Status', 'Código', 'Tamanho', 'Descrição', 'Marca', 'Qtde', 'Preço Un', 'Total Item');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$manufacturer = $this->db->query("SELECT m.name AS manufacturer FROM `" . DB_PREFIX . "product` p LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '" . $result['product_id'] . "'");
if ($manufacturer->rows) {
$manufacturer = $manufacturer->row['manufacturer'];
} else {
$manufacturer = '';
}
$myArr = array($result['order_id'], $result['customer'], $result['email'], date('d/m/Y', strtotime($result['date_added'])), $result['STATUS'], $result['model'], $result['value'], $result['name'], $manufacturer, $result['quantity'], $this->currency->format($result['price']), $this->currency->format($result['total']));
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Produtos e Pedidos', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
//ProdutosURL
if ($data['filter_type'] == 'products_URL') {
$results = $this->model_report_export_reports->getProductsURL($filter_date_start, $filter_date_end);
$excel = new ExcelWriter($name_file);
$myArr = array('Descrição', 'Categoria', 'Url Amigável');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$url = $this->model_report_export_reports->rewrite('index.php?route=product/product&product_id=' . $result['product_id']);
$categories = $this->model_report_export_reports->getCategories($result['product_id']);
$myArr = array($result['name'], $categories, $url);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Produtos URL', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
//BALANÇO
if ($data['filter_type'] == 'balance') {
$results = $this->model_report_export_reports->getBalance($filter_date_start, $filter_date_end);
$total_geral = 0;
$excel = new ExcelWriter($name_file);
$myArr = array('Pedido', 'Nome', 'Data', 'Total', 'Método', 'Status');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['order_id'], $result['nome'], $result['data_compra'], $result['total'], $result['payment_method'], $result['status_pedido']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Balanço', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
//CLIENTES INATIVOS
if ($data['filter_type'] == 'inactive_customers') {
$results = $this->model_report_export_reports->getReportInactiveCustomers($filter_date_start, $filter_date_end);
$total_geral = 0;
$excel = new ExcelWriter($name_file);
$myArr = array('Nome', 'Email', 'Dias Inativos');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['nome'], $result['email'], $result['dias_inativo']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Clientes Inativos', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
//CLIENTES INATIVOS
//PRODUTOS AVISE-ME
if ($data['filter_type'] == 'products_notifyme') {
$results = $this->model_report_export_reports->getReportProductsNotifyme($filter_date_start, $filter_date_end);
$total_geral = 0;
$excel = new ExcelWriter($name_file);
$myArr = array('Código Loja', 'Nome', 'Nome do Cliente', 'Telefone', 'Email', 'Data Solicitação');
$excel->writeLine($myArr, 'title');
$this->load->model('catalog/product');
$this->load->model('catalog/manufacturer');
$this->load->model('sale/customer');
foreach ($results as $result) {
if ($result['product_id']) {
$product_info = $this->model_catalog_product->getProduct($result['product_id']);
$name = isset($product_info['name']) ? $product_info['name'] : '';
$model = isset($product_info['model']) ? $product_info['model'] : '';
$myArr = array($model, $name, $result['firstname'], $result['telephone'], $result['email'], $result['date_added']);
$excel->writeLine($myArr, 'rows');
}
}
$excel->close();
$this->model_report_export_reports->setReport('Produtos Avise-me', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
//PRODUTO AVISE-ME
//PEDIDOS
if ($data['filter_type'] == 'orders') {
$results = $this->model_report_export_reports->getReportOrders($filter_date_start, $filter_date_end, $filter_order_status_id, $filter_payment_method, $filter_shipping_method, $filter_zone);
if (count($results) > 0) {
$total_geral = 0;
$excel = new ExcelWriter($name_file);
$myArr = array('Numero do Pedido', 'Nome do Cliente', 'E-mail', 'Status', 'Método de Entrega', 'Método de Pagamento', 'Cidade', 'Estado', 'Adicionado em', 'Total');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$total_geral += $result['total'];
$myArr = array($result['order_id'], $result['firstname'] . ' ' . $result['lastname'], $result['email'], $result['name_order_status'], strtoupper(str_replace('_', ' ', $result['shipping_method'])), strtoupper(str_replace('_', ' ', $result['payment_method'])), $result['shipping_city'], $result['shipping_zone'], date("d/m/Y H:i:s", strtotime($result['date_added'])), $this->currency->format($result['total']));
$excel->writeLine($myArr, 'rows');
}
$myArr = array('', '', '', '', '', '', '', '', '<b>TOTAL GERAL:</b>', '<b>' . $this->currency->format($total_geral) . '</b>');
$excel->writeLine($myArr, 'rows');
$excel->close();
$this->model_report_export_reports->setReport('Pedidos', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//PEDIDOS
//RANK PRODUTOS
if ($data['filter_type'] == 'products_rank') {
$results = $this->model_report_export_reports->getReportProductsRank();
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Nome do Produto', 'Referência', 'Quantidade Vendida', 'Valor', 'Valor Total', 'Estoque');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$special = $this->model_report_export_reports->getProductSpecial($result['product_id']);
if ($special) {
//$total_order = number_format($special * $result['quanty_orders'],2,',','.');
$price = number_format($special, 2, ',', '.') . ' - PRO';
} else {
//$total_order = number_format($result['price'] * $result['quanty_orders'],2,',','.');
$price = number_format($result['price'], 2, ',', '.');
}
$value_total = number_format($result['total_price_product'], 2, ',', '.');
$myArr = array($result['name'], $result['model'], $result['quanty_orders'], $price, $value_total, $result['quantity']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Ranking de Produtos', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//RANK PRODUTOS
//ESTOQUE
if ($data['filter_type'] == 'general_stock') {
$results = $this->model_report_export_reports->getGeneralStock();
if ($results) {
$excel = new ExcelWriter($name_file);
$columns = array('MODELO', 'NOME', 'STATUS', 'PREÇO', 'FABRICANTE', 'TAMANHO', 'QUANTIDADE', 'PESO', 'COMPRIMENTO', 'LARGURA', 'ALTURA', 'STATUS DO ESTOQUE', 'FRETE GRÁTIS', 'ULTIMA VENDA');
$excel->writeLine($columns, 'title');
foreach ($results as $result) {
$special = $this->model_report_export_reports->getProductSpecial($result['product_id']);
if ($special) {
$preco = number_format($special, 2, ',', '.');
} else {
$preco = number_format($result['PRECO'], 2, ',', '.');
}
if ($result['STATUS'] == 1) {
$status = "Ativado";
} else {
$status = "Desativado";
}
if ($result['FRETE'] == 1) {
$frete = "Não";
} else {
$frete = "Sim";
}
$row = array($result['MODELO'], $result['NOME'], $status, $preco, $result['FABRICANTE'], $result['TAMANHO'], $result['QUANTIDADE'], $result['PESO'], $result['COMPRIMENTO'], $result['LARGURA'], $result['ALTURA'], $result['STOCK_STATUS'], $frete, $result['DATA_ULTIMA_VENDA']);
$excel->writeLine($row, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Estoque', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
}
}
//ESTOQUE
//PRODUTOS CARRINHO ABANDONADO
if ($data['filter_type'] == 'products_abandoned_cart') {
$results = $this->model_report_export_reports->getReportProductsAbandonedCart($filter_date_start, $filter_date_end);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Nome do Produto', 'Referência', 'Preço', 'Quantidade abandonada', 'Quantidade vendida', 'Ultima data');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$special = $this->model_report_export_reports->getProductSpecial($result['product_id']);
if ($special) {
$price = number_format($special, 2, ',', '.') . ' - PRO';
} else {
$price = number_format($result['price'], 2, ',', '.');
}
if (isset($result['quanty_orders']) && $result['quanty_orders'] != NULL) {
$quanty_orders = $result['quanty_orders'];
} else {
$quanty_orders = 0;
}
$date = date('d/m/Y H:i:s', strtotime($result['date_modified']));
$myArr = array($result['name'], $result['model'], $price, $result['qty_product'], $quanty_orders, $date);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Produtos do Carrinho Abandonado', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//PRODUTOS CARRINHO ABANDONADO
//CLIENTES CARRINHO ABANDONADO
if ($data['filter_type'] == 'customers_abandoned_cart') {
$results = $this->model_report_export_reports->getReportCustomersAbandonedCart($filter_date_start, $filter_date_end);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Nome do Produto', 'Referência', 'Quantidade adicionada', 'Data Adicionada', 'Nome', 'Email');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$date = date('d/m/Y H:i:s', strtotime($result['date_added']));
$myArr = array($result['name'], $result['model'], $result['qty_cart'], $date, $result['name_customer'], $result['email_customer']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Clientes do Carrinho Abandonado', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//CLIENTES CARRINHO ABANDONADO
//CLIENTES COMPRADORES
if ($data['filter_type'] == 'customers_order') {
$results = $this->model_report_export_reports->getReportCustomersOrder($filter_date_start, $filter_date_end);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Código', 'Nome/Razão Social', 'Sobrenome', 'Sexo', 'E-mail', 'Data Nascimento', 'Telefone', 'Celular', 'Status', 'Endereço', 'Numero', 'Complemento', 'Bairro', 'Cidade', 'Estado', 'Cep', 'Data Ultima Compra', 'Todos de Pedidos Concretizados', 'Total de Pedidos Cancelados', 'Total');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
if ($result['status'] == 1) {
$status = 'SIM';
} else {
$status = 'NAO';
}
if ($result['sex'] == 'M') {
$sex = 'Masculino';
} else {
$sex = 'Feminino';
}
$total_orders = $result['total_buy'];
$myArr = array($result['customer_id'], $result['firstname'], $result['lastname'], $sex, $result['email'], date("d/m/Y", strtotime($result['birthday'])), $result['telephone'], $result['cellphone'], $status, $result['address_1'], $result['number_home'], $result['address_2'], $result['neighborhood'], $result['city'], $result['name_estate'], $result['postcode'], date("d/m/Y H:i:s", strtotime($result['date_last_buy'])), $result['quanty_orders_entregue'], $result['quanty_orders_cancelado'], number_format($total_orders, 2, ',', '.'));
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Clientes Compradores', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//CLIENTES COMPRADORES
//PRODUTOS NOTIFICADOS
if ($data['filter_type'] == 'products_notified') {
$results = $this->model_report_export_reports->getReportProductsNotified($filter_date_start, $filter_date_end);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Nome', 'Código Loja', 'Total');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['name'], $result['model'], $result['total']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Produtos Notificados', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//PRODUTOS NOTIFICADOS
//CLIENTES
if ($data['filter_type'] == 'customers') {
$results = $this->model_report_export_reports->getReportCustomers($filter_date_start, $filter_date_end);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Código', 'Data de cadastro', 'Nome/Razão Social', 'Sobrenome', 'E-mail', 'Data Nascimento', 'Telefone', 'Celular', 'Status', 'Grupo de Vendas', 'Endereço', 'Numero', 'Complemento', 'Bairro', 'Cidade', 'Estado', 'Cep');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
if ($result['status'] == 1) {
$status = 'SIM';
} else {
$status = 'NAO';
}
if ($result['customer_group_id'] == 8) {
$customer_group = 'Varejo';
} else {
$customer_group = 'GDM';
}
if (isset($result['address_1'])) {
$myArr = array($result['customer_id'], date("d/m/Y H:i:s", strtotime($result['date_added'])), $result['firstname'], $result['lastname'], $result['email'], date("d/m/Y", strtotime($result['birthday'])), $result['telephone'], $result['cellphone'], $status, $customer_group, $result['address_1'], $result['number_home'], $result['address_2'], $result['neighborhood'], $result['city'], $result['name_estate'], $result['postcode']);
} else {
$myArr = array($result['customer_id'], date("d/m/Y H:i:s", strtotime($result['date_added'])), $result['firstname'], $result['lastname'], $result['email'], date("d/m/Y", strtotime($result['birthday'])), $result['telephone'], $result['cellphone'], $status, $customer_group, '', '', '', '', '', '', '');
}
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Clientes', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//CLIENTES
//ANIVERSARIANTES
if ($data['filter_type'] == 'customers_birthday') {
$results = $this->model_report_export_reports->getReportCustomersBirthday($filter_date_start, $filter_date_end);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Código', 'Nome/Razão Social', 'Sobrenome', 'E-mail', 'Data Nascimento', 'Telefone', 'Celular', 'Status', 'Endereço', 'Numero', 'Complemento', 'Bairro', 'Cidade', 'Estado', 'Cep');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
if ($result['status'] == 1) {
$status = 'SIM';
} else {
$status = 'NAO';
}
if (isset($result['address_1'])) {
$myArr = array($result['customer_id'], $result['firstname'], $result['lastname'], $result['email'], date("d/m/Y", strtotime($result['birthday'])), $result['telephone'], $result['cellphone'], $status, $result['address_1'], $result['number_home'], $result['address_2'], $result['neighborhood'], $result['city'], $result['name_estate'], $result['postcode']);
} else {
$myArr = array($result['customer_id'], $result['firstname'], $result['lastname'], $result['email'], date("d/m/Y", strtotime($result['birthday'])), $result['telephone'], $result['cellphone'], $status, '', '', '', '', '', '', '');
}
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Aniversariantes', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//ANIVERSARIANTES
//NEWSLETTER
if ($data['filter_type'] == 'newsletter') {
$results = $this->model_report_export_reports->getReportNewsletter();
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Name', 'Email');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['name'], $result['email']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Newsletter', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//NEWSLETTER
//CLIENTES COMPRADORES
if ($data['filter_type'] == 'nfe') {
$this->load->model('module/nfe');
$filter['date_start'] = $filter_date_start;
$filter['date_end'] = $filter_date_end;
$results = $this->model_module_nfe->getNfes($filter);
$data['total_produtos'] = '0';
$data['descontos'] = '0';
$data['valor_frete'] = '0';
$data['valor_nota'] = '0';
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
foreach ($results as $result) {
$myArr = array('Código do Pedido', 'Número da Nota', 'CFOP', 'Data Emissão', 'Nome do Cliente', 'Protocolo da Nota', 'Total Produtos', 'Frete', 'Descontos', 'Valor Nota');
$excel->writeLine($myArr, 'title');
$nome_cliente = $this->model_module_nfe->getCustomerNota($result['codigo_cliente']);
$data['total_produtos'] += (double) $result['total_produtos'];
$data['valor_frete'] += (double) $result['valor_frete'];
$data['descontos'] += (double) $result['descontos'];
$data['valor_nota'] += (double) $result['valor_nota'];
$name = isset($nome_cliente['firstname']) ? $nome_cliente['firstname'] . ' ' . $nome_cliente['lastname'] : '';
$myArr = array($result['codigo_pedido'], $result['numero_nota'], $result['cfop'], date("d/m/Y H:i:s", strtotime($result['data_emissao'])), $name, $result['protocolo'], number_format($result['total_produtos'], 2, ',', '.'), number_format($result['valor_frete'], 2, ',', '.'), number_format($result['descontos'], 2, ',', '.'), number_format($result['valor_nota'], 2, ',', '.'));
$excel->writeLine($myArr, 'rows');
$myArr = array('Nome do Produto', 'Modelo', 'NCM', 'Quantidade', 'Preço Unit.', 'Total');
$excel->writeLine($myArr, 'title');
$itens = $this->model_module_nfe->getNfeItens($result['numero_nota']);
foreach ($itens as $item) {
$myArr = array($item['nome_produto_item'], $item['model'], $item['ncm'], $item['quantidade'], number_format($item['preco_unitario'], 2, ',', '.'), number_format($item['quantidade'] * $item['preco_unitario'], 2, ',', '.'));
$excel->writeLine($myArr, 'rows');
}
$myArr = array('');
$excel->writeLine($myArr, 'rows');
}
$myArr = array('', '', '', '', '', 'Totais:', number_format($data['total_produtos'], 2, ',', '.'), number_format($data['valor_frete'], 2, ',', '.'), number_format($data['descontos'], 2, ',', '.'), number_format($data['valor_nota'], 2, ',', '.'));
$excel->writeLine($myArr, 'title');
$excel->close();
$this->model_report_export_reports->setReport('Notas Fiscais Geradas', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//CLIENTES COMPRADORES
//ESTOQUE PRODUTOS
if ($data['filter_type'] == 'positive_stock_products') {
$results = $this->model_report_export_reports->getPositiveStockProducts($data['filter_options']);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Código do Produto', 'Código da Loja', 'Nome do Produto', 'Peso', 'Preço', 'Tamanho', 'Estoque', 'Status', 'Última Modificação');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['product_id'], $result['model'], $result['product_name'], $result['weight'], $result['price'], $result['size_name'], $result['quantity'], $result['status'], $result['date_modified']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Produtos Em Estoque', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
if ($data['filter_type'] == 'out_stock_products') {
$results = $this->model_report_export_reports->getOutStockProducts($data['filter_options']);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Código do Produto', 'Código da Loja', 'Nome do Produto', 'Peso', 'Preço', 'Tamanho', 'Estoque', 'Status', 'Última Modificação');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['product_id'], $result['model'], $result['product_name'], $result['weight'], $result['price'], $result['size_name'], $result['quantity'], $result['status'], $result['date_modified']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Produtos Sem Estoque', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//ESTOQUE/VOLTAGEM PRODUTOS
//PROMOÇÕES
if ($data['filter_type'] == 'active_sales') {
$results = $this->model_report_export_reports->getActiveSales();
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Código do Produto', 'Código da Loja', 'Nome do Produto', 'Preço Promoção', 'Data Inicio', 'Data Final');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['product_id'], $result['model'], $result['product_name'], $result['price'], $result['date_start'], $result['date_end']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Promocoes Ativas', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
if ($data['filter_type'] == 'expired_sales') {
$results = $this->model_report_export_reports->getExpiredSales($filter_date_start, $filter_date_end);
if (count($results) > 0) {
$excel = new ExcelWriter($name_file);
$myArr = array('Código do Produto', 'Código da Loja', 'Nome do Produto', 'Preço Promoção', 'Data Inicio', 'Data Final');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['product_id'], $result['model'], $result['product_name'], $result['price'], $result['date_start'], $result['date_end']);
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->model_report_export_reports->setReport('Promocoes Vencidas', str_replace(DIR_APPLICATION, HTTPS_SERVER, $name_file));
$this->response->redirect($this->url->link('report/export_reports', 'success=Arquivo Gerado com Sucesso!&token=' . $this->request->get['token'], 'SSL'));
exit;
} else {
$this->response->redirect($this->url->link('report/export_reports', 'error_warning=Nehum Registro Foi Encontrado! O Relatório não foi gerado!&token=' . $this->request->get['token'], 'SSL'));
exit;
}
}
//PROMOÇÕES
//GERA OS RELATORIOS
//MEIOS DE PAGAMENTO
$this->load->model('extension/extension');
$extensions = $this->model_extension_extension->getInstalled('payment');
$files = glob(DIR_APPLICATION . 'controller/payment/*.php');
if ($files) {
foreach ($files as $file) {
$extension = basename($file, '.php');
if ($extension != 'clearsale' && $extension != 'mercadopago' && $extension != 'pagamentodigital' && $extension != 'pp_standard') {
$data['payment_methods'][] = array('payment_method' => $extension, 'name' => strtoupper(str_replace('_', ' ', $extension)));
}
}
}
//MEIOS DE PAGAMENTO
//MEIOS DE ENTREGA
$extensions = $this->model_extension_extension->getInstalled('shipping');
$files = glob(DIR_APPLICATION . 'controller/shipping/*.php');
if ($files) {
foreach ($files as $file) {
$extension = basename($file, '.php');
if ($extension != 'flat' && $extension != 'free' && $extension != 'item' && $extension != 'frete_personalizado' && $extension != 'weight') {
$name = 'frete';
if ($extension == 'freight') {
$name = 'Motoboy ';
}
if ($extension == 'pickup') {
$name = 'RETIRAR NA LOJA';
}
if ($extension == 'transportadora') {
$name = 'Transportadora';
}
if ($extension == 'sedex') {
$name = 'SEDEX';
}
if ($extension == 'pac') {
$name = 'PAC';
}
if ($extension == 'esedex') {
$name = 'E-SEDEX';
}
$data['shipping_methods'][] = array('shipping_method' => $name, 'name' => strtoupper(str_replace('_', ' ', $name)));
}
}
}
//MEIOS DE ENTREGA
$this->load->model('localisation/order_status');
$data['order_statuses'] = $this->model_localisation_order_status->getOrderStatuses();
//ESTADOS
$this->load->model('localisation/zone');
$data['zones'] = $this->model_localisation_zone->getZonesByCountryId(30);
$this->load->model('report/export_reports');
$results = $this->model_report_export_reports->getReports();
$data['results_reports'] = array();
foreach ($results as $result) {
$data['results_reports'][] = array('date_added' => $result['date_added'], 'type' => $result['type'], 'link' => $result['link'], 'user' => $result['user']);
}
$data['header'] = $this->load->controller('common/header');
$data['column_left'] = $this->load->controller('common/column_left');
$data['footer'] = $this->load->controller('common/footer');
$this->response->setOutput($this->load->view('report/export_reports.tpl', $data));
}
/**
* Retourne le tableau au format excel
*
*/
public function getExcel()
{
//Inclusion de la classe pour gnération des fichiers excel
require_once COPIX_PATH . '../excelwriter/excelwriter.inc.php';
$excel = new ExcelWriter("temp.xls", "", "");
if ($excel == false) {
throw new Exception($excel->error);
}
//Création de la ligne de titre
if (count($this->_title) > 0) {
$tabTitle = array();
foreach ($this->_title as $key => $titre) {
$tabTitle[utf8_decode($titre)] = isset($this->_size[$key]) ? $this->_size[$key] : $this->_defaultSize;
}
$excel->writeLine($tabTitle, "gras");
}
//Création des lignes de données
foreach ($this->_array as $line) {
$excel->writeRow();
if (count($this->_mapObject) > 0) {
foreach ($this->_mapObject as $key => $map) {
$size = isset($this->_size[$key]) ? $this->_size[$key] : $this->_defaultSize;
$excel->writeCol(utf8_decode($line->{$map}), $size);
}
} else {
foreach ($line as $key => $cell) {
$size = isset($this->_size[$key]) ? $this->_size[$key] : $this->_defaultSize;
$excel->writeCol(utf8_decode($cell), $size);
}
}
}
//Fin du document
$excel->close();
return $excel->getData();
}
function write_xls($sql)
{
//echo $sql;
$sql = str_replace('\\', '', $sql);
$excel = new ExcelWriter('excel_report/' . date('m_d_Y_s') . '_feedback_report.xls');
$file_name = date('m_d_Y_s') . '_feedback_report.xls';
if ($excel == false) {
echo $excel->error;
}
$result1 = mysql_query($sql);
$arr1 = mysql_fetch_array($result1);
$myArr = array("Branch", branch_name($arr1['b_id']), "", "Semester", sem_name($arr1['sem_id']));
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$myArr = array("Batch", batch_name($arr1['batch_id']), "", "Feedback No", $arr1['feedback_no']);
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$myArr = array("Faculty Name:", faculty_name($arr1['f_id']), "", "Subject:", subject_name($arr1['sub_id']));
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$myArr = array("Remark");
//"Ans1","Ans2","Ans3","Ans4","Ans5","Ans6","Ans7","Ans8","Ans9"
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$result = mysql_query($sql) or die(mysql_error());
$total_ids = "0";
$r_id = 1;
while ($arr = mysql_fetch_array($result)) {
if ($arr['remark'] != NULL) {
$myArr = array($arr['remark']);
//$arr['ans1'],$arr['ans2'],$arr['ans3'],$arr['ans4'],$arr['ans5'],$arr['ans6'],$arr['ans7'],$arr['ans8'],$arr['ans9']
$excel->writeLine($myArr);
//$r_id++;
}
}
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$excel->close();
return $file_name;
}
/**
* Function generates a report in the file format.
* @param array $sql
*
* @return file
*/
function createReport($sql)
{
if ($_POST['export'] == 'excel') {
include "classes/Lib/excelwriter.inc.php";
$excel = new ExcelWriter("User_Detail.xls");
if ($excel == false) {
$excel->error;
}
$myArr = array("No", "Display Name", "First Name", "Last Name", "Email", "Address", "City", "State", "Zip Code", "Country");
$excel->writeLine($myArr);
$j = 1;
$sql = 'select * from users_table';
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
$sqlAdd = "SELECT a.*,b.cou_code,b.cou_name FROM addressbook_table AS a LEFT JOIN country_table AS b ON b.cou_code=a.country WHERE a.user_id='" . $obj->records[$i]['user_id'] . "'";
$objAdd = new Bin_Query();
$objAdd->executeQuery($sqlAdd);
$display_name = $obj->records[$i]['user_display_name'];
$first_name = $obj->records[$i]['user_fname'];
$last_name = $obj->records[$i]['user_lname'];
$email = $obj->records[$i]['user_email'];
$address = $objAdd->records[0]['address'];
$city = $objAdd->records[0]['city'];
$state = $objAdd->records[0]['state'];
$zip = $objAdd->records[0]['zip'];
$country = $objAdd->records[0]['cou_name'];
$doj = $obj->records[$i]['user_doj'];
$excel->writeRow();
$excel->writeCol($j);
$excel->writeCol($display_name);
$excel->writeCol($first_name);
$excel->writeCol($last_name);
$excel->writeCol($email);
$excel->writeCol($address);
$excel->writeCol($city);
$excel->writeCol($state);
$excel->writeCol($zip);
$excel->writeCol($country);
$excel->writeCol($doj);
$j++;
}
$excel->close();
}
if (strpos($_SERVER['USER'], 'MSIE')) {
// IE cannot download from sessions without a cache
header('Cache-Control: public');
} else {
//header("Cache-Control: no-cache, must-revalidate");
header("Cache-Control: no-cache");
}
$file = "User_Detail.xls";
//chmod ($file, 0755);
header("Pragma: no-cache");
header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
header("Connection: close");
header("Content-Disposition: attachment; filename=" . $file . "\n");
header("Content-Transfer-Encoding: binary\n");
header("Content-length: " . (string) filesize("{$file}"));
$fd = fopen($file, "rb");
fpassthru($fd);
} else {
if ($_POST['export'] == 'xml') {
$sqlselect = "select user_id,user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
header("Content-Type: text/xml");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: xml/force-download");
header("Content-Type: xml/octet-stream");
header("Content-Type: xml/download");
header("Content-Disposition: attachment;filename=user_report.xml");
header("Content-Transfer-Encoding: binary ");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
echo "<userdetails>\n";
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$sqlAdd = "SELECT a.*,b.cou_code,b.cou_name FROM addressbook_table AS a LEFT JOIN country_table AS b ON b.cou_code=a.country WHERE a.user_id='" . $obj->records[$i]['user_id'] . "'";
$objAdd = new Bin_Query();
$objAdd->executeQuery($sqlAdd);
echo "<userid>" . $obj->records[$i]['user_id'] . "</userid>\n";
echo "<displayname>" . $obj->records[$i]['user_display_name'] . "</displayname>\n";
echo "<firstname>" . $obj->records[$i]['user_fname'] . "</firstname>\n";
echo "<lastname>" . $obj->records[$i]['user_lname'] . "</lastname>\n";
echo "<email>" . $obj->records[$i]['user_email'] . "</email>\n";
echo "<address>" . $objAdd->records[0]['address'] . "</address>\n";
echo "<city>" . $objAdd->records[0]['city'] . "</city>\n";
echo "<state>" . $objAdd->records[0]['state'] . "</state>\n";
echo "<zipcode>" . $objAdd->records[0]['zip'] . "</zipcode>\n";
echo "<country>" . $objAdd->records[0]['cou_name'] . "</country>\n";
echo "<userdoj>" . $obj->records[$i]['user_doj'] . "</userdoj>\n";
}
echo "</userdetails>\n";
exit;
}
} else {
if ($_POST['export'] == 'csv') {
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sqlselect = "select user_id,user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $csv_enclosed . No . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . FirstName . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . LastName . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . DisplayName . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Email . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . DateofJoin . $csv_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$sqlAdd = "SELECT a.*,b.cou_code,b.cou_name FROM addressbook_table AS a LEFT JOIN country_table AS b ON b.cou_code=a.country WHERE a.user_id='" . $obj->records[$i]['user_id'] . "'";
$objAdd = new Bin_Query();
$objAdd->executeQuery($sqlAdd);
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_id'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_display_name'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_fname'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_lname'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_email'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $objAdd->records[0]['address'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $objAdd->records[0]['city'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $objAdd->records[0]['state'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $objAdd->records[0]['zip'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $objAdd->records[0]['cou_name'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_doj'] . $csv_enclosed;
}
$out .= $schema_insert;
$out .= $csv_terminated;
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=user_report.csv");
echo $out;
exit;
} else {
if ($_POST['export'] == 'tab') {
$tab_terminated = "\n";
$tab_separator = "->";
$tab_enclosed = '"';
$tab_escaped = "\\";
$sqlselect = "select user_id,user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $tab_enclosed . No . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . DisplayName . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . FirstName . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . LastName . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Email . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Address . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . City . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . State . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Zipcode . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Country . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . DateofJoin . $tab_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$sqlAdd = "SELECT a.*,b.cou_code,b.cou_name FROM addressbook_table AS a LEFT JOIN country_table AS b ON b.cou_code=a.country WHERE \ta.user_id='" . $obj->records[$i]['user_id'] . "'";
$objAdd = new Bin_Query();
$objAdd->executeQuery($sqlAdd);
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_id'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_fname'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_lname'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_display_name'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_email'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $objAdd->records[0]['address'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $objAdd->records[0]['city'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $objAdd->records[0]['state'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $objAdd->records[0]['zip'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $objAdd->records[0]['cou_name'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_doj'] . $tab_enclosed;
}
$out .= $schema_insert;
$out .= $tab_terminated;
}
/*header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: application/tab");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=user_report.tab");*/
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-Type: tab/force-download");
header("Content-Type: tab/octet-stream");
header("Content-Type: tab/download");
header("Content-Disposition: attachment;filename=user_report.tab");
header("Content-Transfer-Encoding: binary ");
echo $out;
exit;
}
}
}
}
}
/**
* Function converts the order details from the database into an Excel/CSV/TSV/XML format.
*
*
* @return file
*/
function OrderDataExport()
{
if ($_POST['export'] == 'excel') {
include "classes/Lib/excelwriter.inc.php";
$excel = new ExcelWriter("order_Detail.xls");
if ($excel == false) {
echo $excel->error;
}
$myArr = array("SlNo", "Orders Id", "Shipping Name", "Street Address", "Suburb", "City", "State", "Billing Company");
$excel->writeLine($myArr);
$j = 1;
$sql = 'select orders_id,shipping_name,shipping_street_address,shipping_suburb,shipping_city,shipping_state,billing_company from orders_table';
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
$orders_id = $obj->records[$i]['orders_id'];
$shipping_name = $obj->records[$i]['shipping_name'];
$shipping_street_address = $obj->records[$i]['shipping_street_address'];
$shipping_suburb = $obj->records[$i]['shipping_suburb'];
$shipping_city = $obj->records[$i]['shipping_city'];
$shipping_state = $obj->records[$i]['shipping_state'];
$billing_company = $obj->records[$i]['billing_company'];
//$doj = $obj->records[$i]['user_doj'];
$excel->writeRow();
$excel->writeCol($j);
$excel->writeCol($orders_id);
$excel->writeCol($shipping_name);
$excel->writeCol($shipping_street_address);
$excel->writeCol($shipping_suburb);
$excel->writeCol($shipping_city);
$excel->writeCol($shipping_state);
$excel->writeCol($billing_company);
//$excel->writeCol($doj);
$j++;
}
$excel->close();
}
if (strpos($_SERVER['USER'], 'MSIE')) {
// IE cannot download from sessions without a cache
header('Cache-Control: public');
} else {
//header("Cache-Control: no-cache, must-revalidate");
header("Cache-Control: no-cache");
}
$file = "order_Detail.xls";
//chmod ($file, 0755);
header("Pragma: no-cache");
header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
header("Connection: close");
header("Content-Disposition: attachment; filename=" . $file . "\n");
header("Content-Transfer-Encoding: binary\n");
header("Content-length: " . (string) filesize("{$file}"));
$fd = fopen($file, "rb");
fpassthru($fd);
} else {
if ($_POST['export'] == 'xml') {
$sqlselect = "select orders_id,shipping_name,shipping_street_address,shipping_suburb,shipping_city,shipping_state,billing_company from orders_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
header("Content-Type: text/xml");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: xml/force-download");
header("Content-Type: xml/octet-stream");
header("Content-Type: xml/download");
header("Content-Disposition: attachment;filename=order_details.xml");
header("Content-Transfer-Encoding: binary ");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
echo "<orderdetails>\n";
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
echo "<orderid>" . $obj->records[$i]['orders_id'] . "</orderid>\n";
echo "<shipping_name>" . $obj->records[$i]['shipping_name'] . "</shipping_name>\n";
echo "<shipping_street_address>" . $obj->records[$i]['shipping_street_address'] . "</shipping_street_address>\n";
echo "<shipping_suburb>" . $obj->records[$i]['shipping_suburb'] . "</shipping_suburb>\n";
echo "<shipping_city>" . $obj->records[$i]['shipping_city'] . "</shipping_city>\n";
echo "<shipping_state>" . $obj->records[$i]['shipping_state'] . "</shipping_state>\n";
}
echo "</orderdetails>\n";
exit;
}
} else {
if ($_POST['export'] == 'csv') {
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sqlselect = "select orders_id,shipping_name,shipping_street_address,shipping_suburb,shipping_city,shipping_state,billing_company from orders_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $csv_enclosed . Orderid . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ShippingName . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ShippingStreetAddress . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ShippingSuburb . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ShippingCity . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ShippingState . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . BillingCompany . $csv_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$schema_insert .= $csv_enclosed . $obj->records[$i]['orders_id'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['shipping_name'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['shipping_street_address'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['shipping_suburb'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['shipping_city'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['shipping_state'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['billing_company'] . $csv_enclosed;
}
$out .= $schema_insert;
$out .= $csv_terminated;
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=order_report.csv");
echo $out;
exit;
} else {
if ($_POST['export'] == 'tab') {
$tab_terminated = "\n";
$tab_separator = "->";
$tab_enclosed = '"';
$tab_escaped = "\\";
$sqlselect = "select orders_id,shipping_name,shipping_street_address,shipping_suburb,shipping_city,shipping_state,billing_company from orders_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $tab_enclosed . Orderid . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ShippingName . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ShippingStreetAddress . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ShippingSuburb . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ShippingCity . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ShippingState . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . BillingCompany . $tab_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$schema_insert .= $tab_enclosed . $obj->records[$i]['orders_id'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['shipping_name'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['shipping_street_address'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['shipping_suburb'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['shipping_city'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['shipping_state'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['billing_company'] . $tab_enclosed;
}
$out .= $schema_insert;
$out .= $tab_terminated;
}
/*header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: application/tab");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=user_report.tab");*/
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-Type: tab/force-download");
header("Content-Type: tab/octet-stream");
header("Content-Type: tab/download");
header("Content-Disposition: attachment;filename=order_report.tsv");
header("Content-Transfer-Encoding: binary ");
echo $out;
exit;
}
}
}
}
}
/**
* Function generates a product review report in the file format.
* @param string $sql
*
* @return file
*/
function productReviewReport($sql)
{
if ($_POST['export'] == 'excel') {
include "classes/Lib/excelwriter.inc.php";
$excel = new ExcelWriter("Product_Review.xls");
if ($excel == false) {
echo $excel->error;
}
$myArr = array("Id", "User Name", "Title", "Review Summary", "Review", "Review Date");
$excel->writeLine($myArr);
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
$product_id = $obj->records[$i]['product_id'];
$display_name = $obj->records[$i]['user_display_name'];
$title = $obj->records[$i]['title'];
$reviewsummary = $obj->records[$i]['review_txt'];
$review = $obj->records[$i]['review_caption'];
$reviewdate = $obj->records[$i]['review_date'];
$excel->writeRow();
$excel->writeCol($product_id);
$excel->writeCol($display_name);
$excel->writeCol($title);
$excel->writeCol($reviewsummary);
$excel->writeCol($review);
$excel->writeCol($reviewdate);
}
$excel->close();
}
if (strpos($_SERVER['USER'], 'MSIE')) {
// IE cannot download from sessions without a cache
header('Cache-Control: public');
} else {
//header("Cache-Control: no-cache, must-revalidate");
header("Cache-Control: no-cache");
}
$file = "Product_Review.xls";
//chmod ($file, 0755);
header("Pragma: no-cache");
header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
header("Connection: close");
header("Content-Disposition: attachment; filename=" . $file . "\n");
header("Content-Transfer-Encoding: binary\n");
header("Content-length: " . (string) filesize("{$file}"));
$fd = fopen($file, "rb");
fpassthru($fd);
} else {
if ($_POST['export'] == 'xml') {
//$sqlselect = "select user_id,user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
header("Content-Type: text/xml");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: xml/force-download");
header("Content-Type: xml/octet-stream");
header("Content-Type: xml/download");
header("Content-Disposition: attachment;filename=product_review_report.xml");
header("Content-Transfer-Encoding: binary ");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
echo "<productreviewdetails>\n";
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
/*echo ("<user id=\"". $obj->records[$i]['user_id'] ."\">\n");
echo ("<firstname=\"". $obj->records[$i]['user_fname'] ."\">\n");
echo ("<lastname=\"". $obj->records[$i]['user_lname'] ."\">\n");
echo ("<displayname=\"". $obj->records[$i]['user_display_name'] ."\">\n");
echo ("<email=\"". $obj->records[$i]['user_email'] ."\">\n");
echo ("<userdoj=\"". $obj->records[$i]['user_doj'] ."\">\n");*/
echo "<productid>" . $obj->records[$i]['product_id'] . "<\\productid>\n";
echo "<username>" . $obj->records[$i]['user_display_name'] . "<\\username>\n";
echo "<title>" . $obj->records[$i]['title'] . "<\title>\n";
echo "<reviewsummary>" . $obj->records[$i]['review_txt'] . "<\reviewsummary>\n";
echo "<review>" . $obj->records[$i]['review_caption'] . "<\review>\n";
echo "<reviewdate>" . $obj->records[$i]['review_date'] . "<\reviewdate>\n";
}
echo "</productreviewdetails>\n";
}
} else {
if ($_POST['export'] == 'csv') {
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=product_review_report.csv");
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
//$sqlselect = "select user_id,user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
$schema_insert = $csv_enclosed . Id . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . UserName . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Title . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ReviewSummary . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Review . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ReviewPostedDate . $csv_enclosed;
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
$schema_insert .= $csv_enclosed . $obj->records[$i]['product_id'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['user_display_name'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['title'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['review_txt'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['review_caption'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['review_date'] . $csv_enclosed;
}
$out .= $schema_insert;
$out .= $csv_terminated;
}
echo $out;
exit;
} else {
if ($_POST['export'] == 'tab') {
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-Type: tab/force-download");
header("Content-Type: tab/octet-stream");
header("Content-Type: tab/download");
header("Content-Disposition: attachment;filename=product_review_report.tab");
header("Content-Transfer-Encoding: binary ");
$tab_terminated = "\n";
$tab_separator = "->";
$tab_enclosed = '"';
$tab_escaped = "\\";
//$sqlselect = "select user_id,user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
$schema_insert = $tab_enclosed . Id . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . UserName . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Title . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ReviewSummary . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Review . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ReviewPostedDate . $tab_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$schema_insert .= $tab_enclosed . $obj->records[$i]['product_id'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['user_display_name'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['title'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['review_txt'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['review_caption'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['review_date'] . $tab_enclosed;
}
$out .= $schema_insert;
$out .= $tab_terminated;
}
echo $out;
exit;
}
}
}
}
}
public function builtXLS()
{
$this->load->model('report/product');
$url = '';
if (isset($this->request->get['filter_date_start'])) {
$url .= '&filter_date_start=' . $this->request->get['filter_date_start'];
}
if (isset($this->request->get['filter_date_end'])) {
$url .= '&filter_date_end=' . $this->request->get['filter_date_end'];
}
if (isset($this->request->get['filter_payment_method'])) {
$url .= '&filter_payment_method=' . $this->request->get['filter_payment_method'];
}
if (isset($this->request->get['filter_shipping_method'])) {
$url .= '&filter_shipping_method=' . $this->request->get['filter_shipping_method'];
}
if (isset($this->request->get['filter_payment_code'])) {
$url .= '&filter_payment_code=' . $this->request->get['filter_payment_code'];
}
if (isset($this->request->get['filter_type_date'])) {
$url .= '&filter_type_date=' . $this->request->get['filter_type_date'];
}
if (isset($this->request->get['filter_order_status_id'])) {
$url .= '&filter_order_status_id=' . $this->request->get['filter_order_status_id'];
}
require_once DIR_SYSTEM . "library/excel/excelwriter.inc.php";
if (is_file(DIR_APPLICATION . 'controller/report/reports/product_purchased.xls')) {
unlink(DIR_APPLICATION . 'controller/report/reports/product_purchased.xls');
}
$name_file = DIR_APPLICATION . 'controller/report/reports/product_purchased.xls';
if (isset($this->request->get['filter_date_start'])) {
$filter_date_start = $this->request->get['filter_date_start'];
} else {
$filter_date_start = '';
}
if (isset($this->request->get['filter_date_end'])) {
$filter_date_end = $this->request->get['filter_date_end'];
} else {
$filter_date_end = '';
}
if (isset($this->request->get['filter_payment_code'])) {
$filter_payment_code = $this->request->get['filter_payment_code'];
} else {
$filter_payment_code = '';
}
if (isset($this->request->get['filter_shipping_method'])) {
$filter_shipping_method = $this->request->get['filter_shipping_method'];
} else {
$filter_shipping_method = '';
}
if (isset($this->request->get['filter_payment_method'])) {
$filter_payment_method = $this->request->get['filter_payment_method'];
} else {
$filter_payment_method = '';
}
if (isset($this->request->get['filter_type_date'])) {
$filter_type_date = $this->request->get['filter_type_date'];
} else {
$filter_type_date = '';
}
if (isset($this->request->get['filter_order_status_id'])) {
$filter_order_status_id = $this->request->get['filter_order_status_id'];
} else {
$filter_order_status_id = 0;
}
$filter_data = array('filter_date_start' => $filter_date_start, 'filter_date_end' => $filter_date_end, 'filter_payment_code' => $filter_payment_code, 'filter_payment_method' => $filter_payment_method, 'filter_shipping_method' => $filter_shipping_method, 'filter_order_status_id' => $filter_order_status_id, 'filter_type_date' => $filter_type_date);
$results = $this->model_report_product->getPurchased($filter_data);
$excel = new ExcelWriter($name_file);
if (count($results) > 0) {
$myArr = array('Nome do produto', 'Modelo', 'Quantidade', 'Preço de Custo', 'Total');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['name'], $result['model'], $result['quantity'], $this->currency->format($result['cost_price'], $this->config->get('config_currency')), $this->currency->format($result['total'], $this->config->get('config_currency')));
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->response->redirect(HTTPS_SERVER . 'controller/report/reports/product_purchased.xls');
} else {
$this->response->redirect($this->url->link('report/product_purchased', 'warning=Nenhum registro foi encontrado&token=' . $this->session->data['token'] . $url, 'SSL'));
}
}
function write_xls($sql)
{
$sql = str_replace('\\', '', $sql);
$excel = new ExcelWriter('excel_report/' . date('m_d_Y_s') . '_feedback_report.xls');
$file_name = date('m_d_Y_s') . '_feedback_report.xls';
if ($excel == false) {
echo $excel->error;
}
$result1 = mysql_query($sql) or die(mysql_error());
$arr1 = mysql_fetch_array($result1);
if (mysql_num_rows($result1) != 0) {
$myArr = array("Branch", branch_name($arr1['b_id']), "", "Semester", sem_name($arr1['sem_id']));
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$myArr = array("Batch", batch_name($arr1['batch_id']), "", "Division", division_name($arr1['division_id']), "", "Feedback No", $arr1['feedback_no']);
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$myArr = array("Faculty Name:", faculty_name($arr1['f_id']), "", "Subject:", subject_name($arr1['sub_id']));
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
/*$myArr=array("Remark(s)");//"Ans1","Ans2","Ans3","Ans4","Ans5","Ans6","Ans7","Ans8","Ans9"
$excel->writeLine($myArr);
$myArr=array("","","","","","");
$excel->writeLine($myArr);*/
$myArr = array("Ans1", "Ans2", "Ans3", "Ans4", "Ans5", "Ans6", "Ans7", "Ans8", "Ans9", "Remark");
$excel->writeLine($myArr);
$result = mysql_query($sql) or die(mysql_error());
$total_ids = "0";
$r_id = 1;
while ($arr = mysql_fetch_array($result)) {
/*if($arr['remark']!=NULL)
{
$myArr=array(strtolower($arr['remark']));
$excel->writeLine($myArr);
$r_id++;
}*/
$myArr = array($arr['ans1'], $arr['ans2'], $arr['ans3'], $arr['ans4'], $arr['ans5'], $arr['ans6'], $arr['ans7'], $arr['ans8'], $arr['ans9'], $arr['remark']);
$excel->writeLine($myArr);
$r_id++;
}
$myArr = array("", "", "", "", "", "");
$excel->writeLine($myArr);
$excel->close();
return $file_name;
} else {
echo '<p align=center>No Record Found!.</p>';
}
}
public function builtXLS()
{
$this->load->language('sale/customer');
$this->load->model('sale/customer');
$url = '';
if (isset($this->request->get['filter_name'])) {
$url .= '&filter_name=' . urlencode(html_entity_decode($this->request->get['filter_name'], ENT_QUOTES, 'UTF-8'));
}
if (isset($this->request->get['filter_email'])) {
$url .= '&filter_email=' . urlencode(html_entity_decode($this->request->get['filter_email'], ENT_QUOTES, 'UTF-8'));
}
if (isset($this->request->get['filter_customer_group_id'])) {
$url .= '&filter_customer_group_id=' . $this->request->get['filter_customer_group_id'];
}
if (isset($this->request->get['filter_status'])) {
$url .= '&filter_status=' . $this->request->get['filter_status'];
}
if (isset($this->request->get['filter_birthday'])) {
$url .= '&filter_birthday=' . $this->request->get['filter_birthday'];
}
if (isset($this->request->get['filter_ip'])) {
$url .= '&filter_ip=' . $this->request->get['filter_ip'];
}
if (isset($this->request->get['filter_date_added'])) {
$url .= '&filter_date_added=' . $this->request->get['filter_date_added'];
}
if (isset($this->request->get['sort'])) {
$url .= '&sort=' . $this->request->get['sort'];
}
if (isset($this->request->get['order'])) {
$url .= '&order=' . $this->request->get['order'];
}
if (isset($this->request->get['page'])) {
$url .= '&page=' . $this->request->get['page'];
}
if (isset($this->request->get['pdv'])) {
$url .= '&pdv=' . $this->request->get['pdv'];
}
require_once DIR_SYSTEM . "library/excel/excelwriter.inc.php";
$name_file = DIR_APPLICATION . 'controller/sale/customer_report/customer_report.xls';
if (isset($this->request->get['filter_name'])) {
$filter_name = $this->request->get['filter_name'];
} else {
$filter_name = null;
}
if (isset($this->request->get['filter_email'])) {
$filter_email = $this->request->get['filter_email'];
} else {
$filter_email = null;
}
if (isset($this->request->get['filter_customer_group_id'])) {
$filter_customer_group_id = $this->request->get['filter_customer_group_id'];
} else {
$filter_customer_group_id = null;
}
if (isset($this->request->get['filter_status'])) {
$filter_status = $this->request->get['filter_status'];
} else {
$filter_status = null;
}
if (isset($this->request->get['filter_birthday'])) {
$filter_birthday = $this->request->get['filter_birthday'];
} else {
$filter_birthday = null;
}
if (isset($this->request->get['filter_ip'])) {
$filter_ip = $this->request->get['filter_ip'];
} else {
$filter_ip = null;
}
if (isset($this->request->get['filter_date_added'])) {
$filter_date_added = $this->request->get['filter_date_added'];
} else {
$filter_date_added = null;
}
if (isset($this->request->get['sort'])) {
$sort = $this->request->get['sort'];
} else {
$sort = 'name';
}
if (isset($this->request->get['order'])) {
$order = $this->request->get['order'];
} else {
$order = 'ASC';
}
$filter_data = array('filter_name' => $filter_name, 'filter_email' => $filter_email, 'filter_customer_group_id' => $filter_customer_group_id, 'filter_status' => $filter_status, 'filter_birthday' => $filter_birthday, 'filter_date_added' => $filter_date_added, 'filter_ip' => $filter_ip, 'sort' => $sort, 'order' => $order);
$results = $this->model_sale_customer->getCustomers($filter_data);
$excel = new ExcelWriter($name_file);
if (count($results) > 0) {
$myArr = array('Nome', 'Email', 'Sexo', 'Data de Aniversário', 'Data de Cadastro');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
if ($result['sex'] == 'F') {
$sex = 'Mulher';
} else {
$sex = 'Homem';
}
$myArr = array($result['firstname'] . ' ' . $result['lastname'], $result['email'], $sex, date('d/m/Y', strtotime($result['birthday'])), date('d/m/Y H:i:s', strtotime($result['date_added'])));
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->response->redirect(HTTPS_SERVER . 'controller/sale/customer_report/customer_report.xls');
} else {
$this->response->redirect($this->url->link('sale/customer', 'warning=Nenhum registro foi encontrado&token=' . $this->session->data['token'] . $url, 'SSL'));
}
}
/**
* Function generates a product details in an excel/xml/csv/tsv format file
*
*
* @return file
*/
function productDataExport()
{
if ($_POST['export'] == 'excel') {
include "classes/Lib/excelwriter.inc.php";
$excel = new ExcelWriter("Product_Detail.xls");
if ($excel == false) {
echo $excel->error;
}
$myArr = array("SlNo", "Title", "Description", "Brand", "Model", "Msrp", "Price", "Shipping Cost", "Intro Date");
$excel->writeLine($myArr);
$j = 1;
$sql = 'select sku,title,description,brand,model,msrp,price,shipping_cost,intro_date from products_table';
$obj = new Bin_Query();
if ($obj->executeQuery($sql)) {
$cnt = count($obj->records);
for ($i = 0; $i < $cnt; $i++) {
$title = $obj->records[$i]['title'];
$description = $obj->records[$i]['description'];
$brand = $obj->records[$i]['brand'];
$model = $obj->records[$i]['model'];
$msrp = $obj->records[$i]['msrp'];
$price = $obj->records[$i]['price'];
$shipping_cost = $obj->records[$i]['shipping_cost'];
$intro_date = $obj->records[$i]['intro_date'];
//$doj = $obj->records[$i]['user_doj'];
$excel->writeRow();
$excel->writeCol($j);
$excel->writeCol($title);
$excel->writeCol($description);
$excel->writeCol($brand);
$excel->writeCol($model);
$excel->writeCol($msrp);
$excel->writeCol($price);
$excel->writeCol($shipping_cost);
$excel->writeCol($intro_date);
//$excel->writeCol($doj);
$j++;
}
$excel->close();
}
if (strpos($_SERVER['USER'], 'MSIE')) {
// IE cannot download from sessions without a cache
header('Cache-Control: public');
} else {
//header("Cache-Control: no-cache, must-revalidate");
header("Cache-Control: no-cache");
}
$file = "Product_Detail.xls";
//chmod ($file, 0755);
header("Pragma: no-cache");
header("Content-Type: php/doc/xml/html/htm/asp/jpg/JPG/sql/txt/jpeg/gif/bmp/png/xls/csv/x-ms-asf\n");
header("Connection: close");
header("Content-Disposition: attachment; filename=" . $file . "\n");
header("Content-Transfer-Encoding: binary\n");
header("Content-length: " . (string) filesize("{$file}"));
$fd = fopen($file, "rb");
fpassthru($fd);
/*function xlsBOF()
{
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF()
{
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteNumber($Row, $Col, $Value)
{
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
function xlsWriteLabel($Row, $Col, $Value )
{
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
//Send header
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=user_report.xls");
header("Content-Transfer-Encoding: binary ");
xlsBOF();
xlsWriteLabel(1,0,"No");
xlsWriteLabel(1,1,"First Name");
xlsWriteLabel(1,2,"Last Name");
xlsWriteLabel(1,3,"Display Name");
xlsWriteLabel(1,4,"Email");
xlsWriteLabel(1,5,"Date of Joining");
$xlsRow = 2;
$j=1;
//Query
$sqlselect = "select user_fname,user_lname,user_display_name,user_email,user_doj from users_table";
$obj = new Bin_Query();
if($obj->executeQuery($sqlselect))
{
$count=count($obj->records);
for($i=0;$i<$count;$i++)
{
$description = $obj->records[$i]['user_fname'];
$last_name = $obj->records[$i]['user_lname'];
$display_name = $obj->records[$i]['user_display_name'];
$email = $obj->records[$i]['user_email'];
$doj = $obj->records[$i]['user_doj'];
xlsWriteLabel($xlsRow,0,"$j");
xlsWriteLabel($xlsRow,1,$description);
xlsWriteLabel($xlsRow,2,$last_name);
xlsWriteLabel($xlsRow,3,$display_name);
xlsWriteLabel($xlsRow,4,$email);
xlsWriteLabel($xlsRow,5,$doj);
$xlsRow++;
$j++;
}
}
xlsEOF();
exit(); */
} else {
if ($_POST['export'] == 'xml') {
$sqlselect = "select sku,title,description,brand,model,msrp,price,shipping_cost,intro_date from products_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
header("Content-Type: text/xml");
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: xml/force-download");
header("Content-Type: xml/octet-stream");
header("Content-Type: xml/download");
header("Content-Disposition: attachment;filename=product_report.xml");
header("Content-Transfer-Encoding:binary");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n";
echo "<productdetails>\n";
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
/*echo ("<user id=\"". $obj->records[$i]['user_id'] ."\">\n");
echo ("<firstname=\"". $obj->records[$i]['user_fname'] ."\">\n");
echo ("<lastname=\"". $obj->records[$i]['user_lname'] ."\">\n");
echo ("<displayname=\"". $obj->records[$i]['user_display_name'] ."\">\n");
echo ("<email=\"". $obj->records[$i]['user_email'] ."\">\n");
echo ("<userdoj=\"". $obj->records[$i]['user_doj'] ."\">\n");*/
echo "<title>" . $obj->records[$i]['title'] . "</title>\n";
echo "<description>" . str_replace("&", '&', $obj->records[$i]['description']) . "</description>\n";
echo "<brand>" . $obj->records[$i]['brand'] . "</brand>\n";
echo "<model>" . $obj->records[$i]['model'] . "</model>\n";
echo "<msrp>" . $obj->records[$i]['msrp'] . "</msrp>\n";
echo "<price>" . $obj->records[$i]['price'] . "</price>\n";
echo "<shipping_cost>" . $obj->records[$i]['shipping_cost'] . "</shipping_cost>\n";
echo "<intro_date>" . $obj->records[$i]['intro_date'] . "</intro_date>\n";
}
echo "</productdetails>\n";
exit;
}
} else {
if ($_POST['export'] == 'csv') {
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sqlselect = "select sku,title,description,brand,model,msrp,price,shipping_cost,intro_date from products_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $csv_enclosed . Title . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Description . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Brand . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Model . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Msrp . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . Price . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . ShippingCost . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . IntroDate . $csv_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$schema_insert .= $csv_enclosed . $obj->records[$i]['title'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['description'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['brand'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['model'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['msrp'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['price'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['ShippingCost'] . $csv_enclosed . $csv_separator;
$schema_insert .= $csv_enclosed . $obj->records[$i]['intro_date'] . $csv_enclosed;
}
$out .= $schema_insert;
$out .= $csv_terminated;
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=product_report.csv");
echo $out;
exit;
} else {
if ($_POST['export'] == 'tab') {
$tab_terminated = "\n";
$tab_separator = "->";
$tab_enclosed = '"';
$tab_escaped = "\\";
$sqlselect = "select sku,title,description,brand,model,msrp,price,shipping_cost,intro_date from products_table";
$obj = new Bin_Query();
if ($obj->executeQuery($sqlselect)) {
$schema_insert = '';
$schema_insert .= $tab_enclosed . Title . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Description . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Brand . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Model . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Msrp . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . Price . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . ShippingCost . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . IntroDate . $tab_enclosed;
$count = count($obj->records);
for ($i = 0; $i < $count; $i++) {
$schema_insert .= $tab_enclosed . $obj->records[$i]['title'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['description'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['brand'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['model'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['msrp'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['price'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['shipping_cost'] . $tab_enclosed . $tab_separator;
$schema_insert .= $tab_enclosed . $obj->records[$i]['intro_date'] . $tab_enclosed;
}
$out .= $schema_insert;
$out .= $tab_terminated;
}
/*header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose ;)
header("Content-type: application/tab");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=user_report.tab");*/
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
header("Content-Type: tab/force-download");
header("Content-Type: tab/octet-stream");
header("Content-Type: tab/download");
header("Content-Disposition: attachment;filename=product_report.tsv");
header("Content-Transfer-Encoding: binary ");
echo $out;
exit;
}
}
}
}
}
public function builtXLS()
{
$this->load->language('marketing/newsletter');
$this->load->model('marketing/newsletter');
$url = '';
if (isset($this->request->get['filter_name'])) {
$url .= '&filter_name=' . urlencode(html_entity_decode($this->request->get['filter_name'], ENT_QUOTES, 'UTF-8'));
}
if (isset($this->request->get['filter_email'])) {
$url .= '&filter_email=' . urlencode(html_entity_decode($this->request->get['filter_email'], ENT_QUOTES, 'UTF-8'));
}
if (isset($this->request->get['filter_date_added_begin'])) {
$url .= '&filter_date_added_begin=' . $this->request->get['filter_date_added_begin'];
}
if (isset($this->request->get['filter_date_added_end'])) {
$url .= '&filter_date_added_end=' . $this->request->get['filter_date_added_end'];
}
if (isset($this->request->get['sort'])) {
$url .= '&sort=' . $this->request->get['sort'];
}
if (isset($this->request->get['order'])) {
$url .= '&order=' . $this->request->get['order'];
}
if (isset($this->request->get['page'])) {
$url .= '&page=' . $this->request->get['page'];
}
require_once DIR_SYSTEM . "library/excel/excelwriter.inc.php";
$name_file = DIR_APPLICATION . 'controller/marketing/newsletter_report/newsletter_report.xls';
if (isset($this->request->get['filter_name'])) {
$filter_name = $this->request->get['filter_name'];
} else {
$filter_name = null;
}
if (isset($this->request->get['filter_email'])) {
$filter_email = $this->request->get['filter_email'];
} else {
$filter_email = null;
}
if (isset($this->request->get['filter_date_added_begin'])) {
$filter_date_added_begin = $this->request->get['filter_date_added_begin'];
} else {
$filter_date_added_begin = null;
}
if (isset($this->request->get['filter_date_added_end'])) {
$filter_date_added_end = $this->request->get['filter_date_added_end'];
} else {
$filter_date_added_end = null;
}
$filter_data = array('filter_name' => $filter_name, 'filter_email' => $filter_email, 'filter_date_added_begin' => $filter_date_added_begin, 'filter_date_added_end' => $filter_date_added_end);
$results = $this->model_marketing_newsletter->getNewsletters($filter_data);
$excel = new ExcelWriter($name_file);
if (count($results) > 0) {
$myArr = array('Nome', 'Email', 'IP', 'Data de Cadastro');
$excel->writeLine($myArr, 'title');
foreach ($results as $result) {
$myArr = array($result['name'], $result['email'], $result['ip'], date('d/m/Y H:i:s', strtotime($result['date_added'])));
$excel->writeLine($myArr, 'rows');
}
$excel->close();
$this->response->redirect(HTTPS_SERVER . 'controller/marketing/newsletter_report/newsletter_report.xls');
} else {
$this->response->redirect($this->url->link('marketing/newsletter', 'warning=Nenhum registro foi encontrado&token=' . $this->session->data['token'] . $url, 'SSL'));
}
}
