/**
* 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);
}
/**
* 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();
}
<?php
include "ExcelWriter.php";
$excel = new ExcelWriter("myXls.xls");
if ($excel == false) {
echo $excel->error;
}
$myArr = array("Name", "Last Name", "Address", "Age");
$excel->writeLine($myArr);
$myArr = array("Sriram", "Pandit", "23 mayur vihar", 24);
$excel->writeLine($myArr);
$excel->writeRow();
$excel->writeCol("Manoj");
$excel->writeCol("Tiwari");
$excel->writeCol("80 Preet Vihar");
$excel->writeCol(24);
$excel->writeRow();
$excel->writeCol("Harish");
$excel->writeCol("Chauhan");
$excel->writeCol("115 Shyam Park Main");
$excel->writeCol(22);
$myArr = array("Tapan", "Chauhan", "1st Floor Vasundhra", 25);
$excel->writeLine($myArr);
$excel->close();
echo "data is write into myXls.xls Successfully.";
?>
/**
* 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;
}
}
}
}
}
while ($fetch_batch = mysql_fetch_array($base_table)) {
$base_file_name = $fetch_batch['base_file_name'];
$batch_code = $fetch_batch['batch_code'];
$batch_date = str_replace('_', '.', substr($base_file_name, 0, 8));
$batch_ivr_all = get_manual_batch_total($batch_code, $ivr_all, $base_file_name);
$batch_icrm = get_manual_icrm_total($batch_code, $base_file_name);
$batch_accpeted = get_manual_batch_total($batch_code, $ivr_accepted, $base_file_name);
$batch_rejected = get_manual_batch_total($batch_code, $ivr_rejected, $base_file_name);
$batch_hold = get_manual_batch_total($batch_code, $ivr_hold, $base_file_name);
$batch_pending = get_manual_batch_total($batch_code, $ivr_pending, $base_file_name);
$batch_status = "PENDING";
if ($batch_pending == 0) {
$batch_status = "ICRM PENDING";
}
$excel->writeRow();
$excel->writeCol($batch_date);
$excel->writeCol("B" . $batch_code);
$excel->writeCol($batch_ivr_all);
$excel->writeCol($batch_icrm);
$excel->writeCol($batch_accpeted);
$excel->writeCol($batch_rejected);
$excel->writeCol($batch_hold);
$excel->writeCol($batch_pending);
$excel->writeCol($batch_status);
$ivr_all_total += $batch_ivr_all;
$icrm_total += $batch_icrm;
$ivr_accepted_total += $batch_accpeted;
$ivr_rejected_total += $batch_rejected;
$ivr_hold_total += $batch_hold;
$ivr_pending_total += $batch_pending;
}
<?php
require_once '../util/excel/writer/ExcelWriter.php';
echo "<center><h1>Testando excel</h1></center>";
$excel = new ExcelWriter("Excel.xls");
$excel->writeCol("Nome");
$excel->writeCol("Sobrenome");
$excel->writeCol("bobagem");
$excel->writeLine(array("ramonox", "henrique", "gonçalves"));
$excel->writeLine(array("tundra", "matheus", "bosta"));
$excel->close();
public function purchasedexport()
{
if ($this->Session->check('id')) {
$this->autoRender = false;
App::import('Vendor', 'ExcelWriter');
$date = date("Y-m-d");
$this->loadModel('Purchasedsticker');
$request = $this->request->data;
// echo "Work in Progress \n";
// pr($request);die;
$conditions = array('Purchasedsticker.by_admin' => 0);
if ($request['Stickers']['type'] == 1) {
$reportname = "FULL";
if ($request['Stickers']['reporttype'] == 1) {
$conditions = "Purchasedsticker.by_admin=0";
} elseif ($request['Stickers']['reporttype'] == 2) {
$conditions = "Purchasedsticker.by_admin=1";
} elseif ($request['Stickers']['reporttype'] == 3) {
$conditions = array();
}
} elseif ($request['Stickers']['type'] == 2) {
$reportname = "By_DATE";
$from = $request['Stickers']['from'];
$to = $request['Stickers']['to'];
$conditions = "WHERE Purchasedsticker.created >= '" . date($from . ' 00:00:00') . "' AND Purchasedsticker.created <='" . date($to . ' 23:59:59') . "' ";
if ($request['Stickers']['reporttype'] == 1) {
$conditions .= " AND Purchasedsticker.by_admin=0";
} elseif ($request['Stickers']['reporttype'] == 2) {
$conditions .= " AND Purchasedsticker.by_admin=1";
} elseif ($request['Stickers']['reporttype'] == 3) {
}
}
// pr($conditions);die;
if ($request['Stickers']['sort'] == 1) {
if ($request['Stickers']['sorttype'] == 1) {
$order = "Sticker.name ASC";
} else {
$order = "Sticker.name DESC";
}
} elseif ($request['Stickers']['sort'] == 2) {
if ($request['Stickers']['sorttype'] == 1) {
$order = "Categorie.name ASC";
} else {
$order = "Categorie.name DESC";
}
} elseif ($request['Stickers']['sort'] == 5) {
if ($request['Stickers']['sorttype'] == 1) {
$order = "total ASC";
} else {
$order = "total DESC";
}
} else {
$order = "Sticker.name ASC";
}
$cond = "`Sticker`.`id`=`Purchasedsticker.sticker_id`";
$cond2 = "`Categorie`.`id`=`Sticker`.`category_id`";
$result = $this->Purchasedsticker->find('all', array('fields' => array('Sticker.id', 'Sticker.name', 'Sticker.category_id', 'Sticker.type_id', 'Sticker.is_active', 'Sticker.is_deleted', 'Sticker.created', 'Sticker.modified', 'Purchasedsticker.created', 'Categorie.name', 'COUNT(`Purchasedsticker`.`sticker_id`) AS total'), 'joins' => array(array('table' => 'stickers', 'alias' => 'Sticker', 'type' => 'left', 'conditions' => $cond), array('table' => 'categories', 'alias' => 'Categorie', 'type' => 'left', 'conditions' => $cond2)), 'conditions' => $conditions, 'order' => $order, 'group' => 'Sticker.id'));
// $log = $this->Purchasedsticker->getDataSource()->getLog(false, false);
// debug($log);
// foreach($result as $key=>$value){
//
// }
// echo "Work in progress.\n";
//
// pr($result);die;
if (empty($result)) {
$this->Session->setFlash("No Data Found", "flash_custom");
$this->redirect(array('controller' => 'Reports'));
}
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Purchased_Stickers_Report" . "_" . $reportname . "_" . $date . ".xls";
$excel = new ExcelWriter($fileName);
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Name", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Category", array('font-weight' => 'bold'));
if ($request['Stickers']['reporttype'] == 2) {
$excel->writeCol("Times Given", array('font-weight' => 'bold'));
} else {
$excel->writeCol("Times Purchased", array('font-weight' => 'bold'));
}
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['Sticker']['name']);
if ($data['Sticker']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
$excel->writeCol($data['Categorie']['name']);
$excel->writeCol($data['0']['total']);
$excel->writeLine(array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "Purchased_Stickers_Report" . "_" . $reportname . "_" . $date . ".xls");
} else {
$this->redirect(array('controller' => 'Users', 'action' => 'index'));
}
}
public function categoryexport()
{
if ($this->Session->check('id')) {
// pr($this->request->data);
$this->autoRender = false;
App::import('Vendor', 'ExcelWriter');
$date = date("Y-m-d");
$this->loadModel('Categorie');
$request = $this->request->data;
// pr($request);
$conditions = array();
if ($request['Categories']['type'] == 1) {
$reportname = "FULL";
if ($request['Categories']['status'] == 1) {
$conditions = "Categorie.is_active=1";
} elseif ($request['Categories']['status'] == 2) {
$conditions = "Categorie.is_active=0";
} else {
$conditions = array();
}
} elseif ($request['Categories']['type'] == 2) {
$from = $this->request->data['Categories']['from'];
$to = $this->request->data['Categories']['to'];
$reportname = "Created From " . date('jS F, Y', strtotime($from . ' 00:00:00')) . " To " . date("jS F, Y", strtotime($to . ' 23:59:59'));
$conditions = "WHERE Categorie.created >= '" . date($from . ' 00:00:00') . "' AND Categorie.created <='" . date($to . ' 23:59:59') . "' ";
if ($request['Categories']['status'] == 1) {
$conditions .= "AND Categorie.is_active=1";
} elseif ($request['Categories']['status'] == 2) {
$conditions .= "AND Categorie.is_active=0";
} else {
$conditions .= "";
}
} elseif ($request['Categories']['type'] == 3) {
$from = $this->request->data['Categories']['from'];
$to = $this->request->data['Categories']['to'];
$reportname = "Modified From " . date('jS F, Y', strtotime($from . ' 00:00:00')) . " To " . date("jS F, Y", strtotime($to . ' 23:59:59'));
$conditions = "WHERE Categorie.updated >= '" . date($from . ' 00:00:00') . "' AND Categorie.updated <='" . date($to . ' 23:59:59') . "' ";
if ($request['Categories']['status'] == 1) {
$conditions .= "AND Categorie.is_active=1";
} elseif ($request['Categories']['status'] == 2) {
$conditions .= "AND Categorie.is_active=0";
} else {
$conditions .= "";
}
// pr($conditions);die;
}
if ($request['Categories']['sort'] == 1) {
if ($request['Categories']['sorttype'] == 1) {
$order = "Categorie.name ASC";
} else {
$order = "Categorie.name DESC";
}
} elseif ($request['Categories']['sort'] == 4) {
if ($request['Categories']['sorttype'] == 1) {
$order = "Categorie.is_active ASC";
} else {
$order = "Categorie.is_active DESC";
}
} elseif ($request['Categories']['sort'] == 5) {
if ($request['Categories']['sorttype'] == 1) {
$order = "total ASC";
} else {
$order = "total DESC";
}
} elseif ($request['Categories']['sort'] == 6) {
if ($request['Categories']['sorttype'] == 1) {
$order = "Categorie.created ASC";
} else {
$order = "Categorie.created DESC";
}
} elseif ($request['Categories']['sort'] == 7) {
if ($request['Categories']['sorttype'] == 1) {
$order = "Categorie.updated ASC";
} else {
$order = "Categorie.updated DESC";
}
} else {
$order = "Categorie.name ASC";
}
$cond = "`Sticker.category_id`=`Categorie`.`id`";
$result = $this->Categorie->find('all', array('fields' => array('Categorie.id', 'Categorie.name', 'Categorie.is_active', 'Categorie.is_deleted', 'Categorie.created', 'Categorie.updated', 'COUNT(`Sticker`.`id`) AS total'), 'joins' => array(array('table' => 'stickers', 'alias' => 'Sticker', 'type' => 'inner', 'conditions' => $cond)), 'conditions' => $conditions, 'order' => $order, 'group' => 'Categorie.id'));
if (empty($result)) {
$this->Session->setFlash("No Data Found", "flash_custom");
$this->redirect(array('controller' => 'Reports'));
}
// pr($result);die;
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Category_Report" . "_" . $reportname . "_" . $date . ".xls";
$excel = new ExcelWriter($fileName);
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Name", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Deletion Status", array('font-weight' => 'bold'));
$excel->writeCol("Total Stickers", array('font-weight' => 'bold'));
$excel->writeCol("Created", array('font-weight' => 'bold'));
$excel->writeCol("Modified", array('font-weight' => 'bold'));
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['Categorie']['name']);
if ($data['Categorie']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
if ($data['Categorie']['is_deleted'] == 0) {
$excel->writeCol("Not Deleted");
} else {
$excel->writeCol("Deleted");
}
if (!empty($data['0']['total'])) {
$excel->writeCol($data['0']['total']);
} else {
$excel->writeCol('None');
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['Categorie']['created'])));
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['Categorie']['updated'])));
$excel->writeLine(array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "Category_Report" . "_" . $reportname . "_" . $date . ".xls");
} else {
$this->redirect(array('controller' => 'Users', 'action' => 'index'));
}
}
/**
* 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 export()
{
if ($this->Session->check('id')) {
App::import('Vendor', 'ExcelWriter');
$date = date("Y-m-d");
$this->loadModel('Sticker');
$this->loadModel('Categorie');
$cond = "Sticker.id=Favsticker.sticker_id";
$conditions = "GROUP BY Favsticker.sticker_id HAVING count(Favsticker.sticker_id) >= 2";
$data = $this->Sticker->find('all', array('fields' => array('Sticker.id', 'Sticker.name', 'Sticker.image', 'Sticker.type_id', 'Sticker.category_id', 'Sticker.created', 'Sticker.modified'), 'joins' => array(array('table' => 'favstickers', 'alias' => 'Favsticker', 'type' => 'inner', 'conditions' => $cond)), 'conditions' => $conditions));
foreach ($data as $key => $value) {
$f = $this->Categorie->find('first', array('fields' => 'name', 'conditions' => array('id' => $value['Sticker']['category_id'])));
$data[$key]['Sticker']['category_name'] = $f['Categorie']['name'];
}
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "PopularSticker_Report" . "_" . $date . ".xls";
$this->autoRender = false;
$excel = new ExcelWriter($fileName);
$excel->writeCol("S. No.");
$excel->writeCol("Name");
$excel->writeCol("Category");
$excel->writeCol("Type");
$excel->writeCol("Created");
$excel->writeCol("Modified");
foreach ($data as $key => $value) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol(ucfirst($value['Sticker']['name']));
$excel->writeCol(ucfirst($value['Sticker']['category_name']));
if ($value['Sticker']['type_id'] == 1) {
$excel->writeCol("Free");
} else {
$excel->writeCol("Paid");
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($value['Sticker']['created'])));
$excel->writeCol(date("F j, Y, g:i A", strtotime($value['Sticker']['modified'])));
$excel->writeLine(array(), array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "PopularSticker_Report" . "_" . $date . ".xls");
} else {
$this->redirect(array('controller' => 'Users', 'action' => 'index'));
}
}
public function appuserexport()
{
if ($this->Session->check('id')) {
$this->autoRender = false;
App::import('Vendor', 'ExcelWriter');
$date = date("Y-m-d");
$this->loadModel('Appuser');
$request = $this->request->data;
// pr($request);
$conditions = array();
if ($request['Appusers']['type'] == 1) {
$reportname = "FULL";
if ($request['Appusers']['status'] == 1) {
$conditions = "Appuser.is_active=1";
} elseif ($request['Appusers']['status'] == 2) {
$conditions = "Appuser.is_active=0";
} else {
$conditions = "1=1";
}
if ($request['Appusers']['sort'] == 1) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`email` ASC";
} else {
$order = "`Appuser`.`email` DESC";
}
} elseif ($request['Appusers']['sort'] == 2) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`zip` ASC";
} else {
$order = "`Appuser`.`zip` DESC";
}
} elseif ($request['Appusers']['sort'] == 3) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`city` ASC";
} else {
$order = "`Appuser`.`city` DESC";
}
} elseif ($request['Appusers']['sort'] == 4) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`country` ASC";
} else {
$order = "`Appuser`.`country` DESC";
}
} elseif ($request['Appusers']['sort'] == 5) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`created` ASC";
} else {
$order = "`Appuser`.`created` DESC";
}
} else {
$order = "`Appuser`.`email` ASC";
}
$cond = "Device.user_id=Appuser.id";
$cond2 = "Stickeruse.user_id=Appuser.id";
$cond3 = "Purchasedsticker.user_id=Appuser.id";
$result = $this->Appuser->query("SELECT `Appuser`.`id`, `Appuser`.`zip`, `Appuser`.`city`, `Appuser`.`email`, `Appuser`.`country`, `Appuser`.`created`, `Appuser`.`is_active`,( SELECT SUM(`count`) FROM digistic_stickerapp.stickeruses WHERE user_id = `Appuser`.`id` ) AS totalused,( SELECT COUNT(*) FROM digistic_stickerapp.devices WHERE user_id = `Appuser`.`id` ) AS tdevice,( SELECT COUNT(`id`) FROM digistic_stickerapp.purchasedstickers WHERE user_id=`Appuser`.`id` ) AS pstickers FROM digistic_stickerapp.`appusers` AS Appuser WHERE {$conditions} GROUP BY `Appuser`.`id` ORDER BY {$order}");
// $log = $this->Appuser->getDataSource()->getLog(false, false);
// debug($log);
// pr($result);die;
} elseif ($request['Appusers']['type'] == 2) {
$from = $this->request->data['Appusers']['from'];
$to = $this->request->data['Appusers']['to'];
$reportname = "Date Range From " . date('jS F, Y', strtotime($from . ' 00:00:00')) . " To " . date("jS F, Y", strtotime($to . ' 23:59:59'));
$conditions = "WHERE Appuser.created >= '" . date($from . ' 00:00:00') . "' AND Appuser.created <='" . date($to . ' 23:59:59') . "' ";
if ($request['Appusers']['status'] == 1) {
$conditions = " AND Appuser.is_active=1 ";
} elseif ($request['Appusers']['status'] == 2) {
$conditions = " AND Appuser.is_active=0 ";
}
if ($request['Appusers']['sort'] == 1) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`email` ASC";
} else {
$order = "`Appuser`.`email` DESC";
}
} elseif ($request['Appusers']['sort'] == 2) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`zip` ASC";
} else {
$order = "`Appuser`.`zip` DESC";
}
} elseif ($request['Appusers']['sort'] == 3) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`city` ASC";
} else {
$order = "`Appuser`.`city` DESC";
}
} elseif ($request['Appusers']['sort'] == 4) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`country` ASC";
} else {
$order = "`Appuser`.`country` DESC";
}
} elseif ($request['Appusers']['sort'] == 5) {
if ($request['Appusers']['sorttype'] == 1) {
$order = "`Appuser`.`created` ASC";
} else {
$order = "`Appuser`.`created` DESC";
}
} else {
$order = "`Appuser`.`email` ASC";
}
$result = $this->Appuser->query("SELECT `Appuser`.`id`, `Appuser`.`zip`, `Appuser`.`city`, `Appuser`.`email`, `Appuser`.`country`, `Appuser`.`created`, `Appuser`.`is_active`,( SELECT SUM(`count`) FROM digistic_stickerapp.stickeruses WHERE user_id = `Appuser`.`id` ) AS totalused,( SELECT COUNT(*) FROM digistic_stickerapp.devices WHERE user_id = `Appuser`.`id` ) AS tdevice,( SELECT COUNT(`id`) FROM digistic_stickerapp.purchasedstickers WHERE user_id=`Appuser`.`id` ) AS pstickers FROM digistic_stickerapp.`appusers` AS Appuser {$conditions} GROUP BY `Appuser`.`id` ORDER BY {$order}");
}
// pr($result);die;
if ($request['Appusers']['type'] == 1 || $request['Appusers']['type'] == 2) {
if (empty($result)) {
$this->Session->setFlash("No Data Found", "flash_custom");
$this->redirect(array('controller' => 'Reports'));
}
}
if ($request['Appusers']['type'] == 1 || $request['Appusers']['type'] == 2) {
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls";
$excel = new ExcelWriter($fileName);
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Email", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("ZIP", array('font-weight' => 'bold'));
$excel->writeCol("City", array('font-weight' => 'bold'));
$excel->writeCol("Country", array('font-weight' => 'bold'));
$excel->writeCol("Total Stickers Used", array('font-weight' => 'bold'));
$excel->writeCol("Total Devices", array('font-weight' => 'bold'));
$excel->writeCol("Total Stickers Purchased", array('font-weight' => 'bold'));
$excel->writeCol("Date Registered", array('font-weight' => 'bold'));
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['Appuser']['email']);
if ($data['Appuser']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
if (!empty($data['Appuser']['zip'])) {
$excel->writeCol($data['Appuser']['zip']);
} else {
$excel->writeCol("Not Available");
}
$excel->writeCol($data['Appuser']['city']);
$excel->writeCol($data['Appuser']['country']);
if (!empty($data['0']['totalused'])) {
$excel->writeCol($data['0']['totalused']);
} else {
$excel->writeCol('None');
}
if (!empty($data['0']['tdevice'])) {
$excel->writeCol($data['0']['tdevice']);
} else {
$excel->writeCol('None');
}
if (!empty($data['0']['pstickers'])) {
$excel->writeCol($data['0']['pstickers']);
} else {
$excel->writeCol('None');
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['Appuser']['created'])));
$excel->writeLine(array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls");
} else {
// pr($request);
$id = $this->Appuser->find('first', array('fields' => 'id', 'conditions' => array('email' => $request['Appusers']['email'])));
// pr($id);
// $conditions="WHERE id=".$id['Appuser']['id'];
if ($request['Appusers']['include'] == 1) {
$reportname = "for " . $request['Appusers']['email'] . "_Purchased Stickers";
$this->loadModel('Purchasedsticker');
$query = "SELECT * FROM `appusers` INNER JOIN purchasedstickers on purchasedstickers.user_id=appusers.id INNER JOIN stickers ON stickers.id=purchasedstickers.sticker_id WHERE appusers.id=" . $id['Appuser']['id'];
$result = $this->Appuser->query($query);
// pr($result);die;
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls";
$excel = new ExcelWriter($fileName);
$excel->writeCol("USER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Email", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("ZIP", array('font-weight' => 'bold'));
$excel->writeCol("City", array('font-weight' => 'bold'));
$excel->writeCol("Country", array('font-weight' => 'bold'));
$excel->writeCol("Date Registered", array('font-weight' => 'bold'));
$excel->writeLine(array());
$excel->writeCol("1");
$excel->writeCol($result['0']['appusers']['email']);
if ($result['0']['appusers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
if (!empty($result['0']['appusers']['zip'])) {
$excel->writeCol($result['0']['appusers']['zip']);
} else {
$excel->writeCol("Not Available");
}
$excel->writeCol($result['0']['appusers']['city']);
$excel->writeCol($result['0']['appusers']['country']);
$excel->writeCol($result['0']['appusers']['created']);
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("PURCHASED", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("STICKER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Name", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Created", array('font-weight' => 'bold'));
$excel->writeCol("Modified", array('font-weight' => 'bold'));
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['stickers']['name']);
if ($data['stickers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['created'])));
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['modified'])));
$excel->writeLine(array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls");
} elseif ($request['Appusers']['include'] == 2) {
$reportname = "for " . $request['Appusers']['email'] . "_Devices";
$query = "SELECT * FROM `appusers` INNER JOIN devices on devices.user_id=appusers.id WHERE appusers.id=" . $id['Appuser']['id'];
$result = $this->Appuser->query($query);
// pr($result);die;
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls";
$excel = new ExcelWriter($fileName);
$excel->writeCol("USER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Email", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("ZIP", array('font-weight' => 'bold'));
$excel->writeCol("City", array('font-weight' => 'bold'));
$excel->writeCol("Country", array('font-weight' => 'bold'));
$excel->writeCol("Date Registered", array('font-weight' => 'bold'));
$excel->writeLine(array());
$excel->writeCol("1");
$excel->writeCol($result['0']['appusers']['email']);
if ($result['0']['appusers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
if (!empty($result['0']['appusers']['zip'])) {
$excel->writeCol($result['0']['appusers']['zip']);
} else {
$excel->writeCol("Not Available");
}
$excel->writeCol($result['0']['appusers']['city']);
$excel->writeCol($result['0']['appusers']['country']);
$excel->writeCol($result['0']['appusers']['created']);
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("DEVICES", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("IMEI", array('font-weight' => 'bold'));
$excel->writeCol("Device Type", array('font-weight' => 'bold'));
$excel->writeCol("Notification", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Date Registered", array('font-weight' => 'bold'));
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['devices']['imei']);
if ($data['devices']['device_type'] == 1) {
$excel->writeCol("Android");
} else {
$excel->writeCol("IOS");
}
if ($data['devices']['notification'] == 1) {
$excel->writeCol("On");
} else {
$excel->writeCol("Off");
}
if ($data['devices']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['devices']['created'])));
$excel->writeLine(array(), array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls");
} elseif ($request['Appusers']['include'] == 3) {
$reportname = "for " . $request['Appusers']['email'] . "_Used Stickers";
$query = "SELECT * FROM `appusers` INNER JOIN stickeruses on stickeruses.user_id=appusers.id INNER JOIN stickers ON stickers.id=stickeruses.sticker_id WHERE appusers.id=" . $id['Appuser']['id'] . " ORDER BY stickeruses.count DESC";
$result = $this->Appuser->query($query);
// pr($result);die;
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls";
$excel = new ExcelWriter($fileName);
$excel->writeCol("USER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Email", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("ZIP", array('font-weight' => 'bold'));
$excel->writeCol("City", array('font-weight' => 'bold'));
$excel->writeCol("Country", array('font-weight' => 'bold'));
$excel->writeCol("Date Registered", array('font-weight' => 'bold'));
$excel->writeLine(array());
$excel->writeCol("1");
$excel->writeCol($result['0']['appusers']['email']);
if ($result['0']['appusers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
if (!empty($result['0']['appusers']['zip'])) {
$excel->writeCol($result['0']['appusers']['zip']);
} else {
$excel->writeCol("Not Available");
}
$excel->writeCol($result['0']['appusers']['city']);
$excel->writeCol($result['0']['appusers']['country']);
$excel->writeCol($result['0']['appusers']['created']);
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("USED", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("STICKER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Name", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Created", array('font-weight' => 'bold'));
$excel->writeCol("Modified", array('font-weight' => 'bold'));
$excel->writeCol("Times Used", array('font-weight' => 'bold'));
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['stickers']['name']);
if ($data['stickers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['created'])));
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['modified'])));
$excel->writeCol($data['stickeruses']['count']);
$excel->writeLine(array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls");
} else {
$reportname = "for " . $request['Appusers']['email'] . "_All Data";
$q1 = "SELECT * FROM `appusers` INNER JOIN devices on devices.user_id=appusers.id WHERE appusers.id=" . $id['Appuser']['id'];
$result = $this->Appuser->query($q1);
// pr($result);die;
$fileName = WWW_ROOT . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls";
$excel = new ExcelWriter($fileName);
$excel->writeCol("USER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Email", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("ZIP", array('font-weight' => 'bold'));
$excel->writeCol("City", array('font-weight' => 'bold'));
$excel->writeCol("Country", array('font-weight' => 'bold'));
$excel->writeCol("Date Registered", array('font-weight' => 'bold'));
$excel->writeLine(array());
$excel->writeCol("1");
$excel->writeCol($result['0']['appusers']['email']);
if ($result['0']['appusers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
if (!empty($result['0']['appusers']['zip'])) {
$excel->writeCol($result['0']['appusers']['zip']);
} else {
$excel->writeCol("Not Available");
}
$excel->writeCol($result['0']['appusers']['city']);
$excel->writeCol($result['0']['appusers']['country']);
$excel->writeCol($result['0']['appusers']['created']);
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("DEVICES", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("IMEI", array('font-weight' => 'bold'));
$excel->writeCol("Device Type", array('font-weight' => 'bold'));
$excel->writeCol("Notification", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Date Registered", array('font-weight' => 'bold'));
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['devices']['imei']);
if ($data['devices']['device_type'] == 1) {
$excel->writeCol("Android");
} else {
$excel->writeCol("IOS");
}
if ($data['devices']['notification'] == 1) {
$excel->writeCol("On");
} else {
$excel->writeCol("Off");
}
if ($data['devices']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['devices']['created'])));
$excel->writeLine(array(), array());
}
$q2 = "SELECT * FROM `appusers` INNER JOIN purchasedstickers on purchasedstickers.user_id=appusers.id INNER JOIN stickers ON stickers.id=purchasedstickers.sticker_id WHERE appusers.id=" . $id['Appuser']['id'];
$result = $this->Appuser->query($q2);
$excel->writeLine(array());
$excel->writeCol("PURCHASED", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("STICKER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Name", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Created", array('font-weight' => 'bold'));
$excel->writeCol("Modified", array('font-weight' => 'bold'));
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['stickers']['name']);
if ($data['stickers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['created'])));
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['modified'])));
$excel->writeLine(array());
}
$excel->writeLine(array());
$excel->writeCol("USED", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("STICKER", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeCol("DATA", array('font-weight' => 'bold', 'color' => 'green'));
$excel->writeLine(array());
$excel->writeLine(array());
$excel->writeCol("S. No.", array('font-weight' => 'bold'));
$excel->writeCol("Name", array('font-weight' => 'bold'));
$excel->writeCol("Activation Status", array('font-weight' => 'bold'));
$excel->writeCol("Created", array('font-weight' => 'bold'));
$excel->writeCol("Modified", array('font-weight' => 'bold'));
$excel->writeCol("Times Used", array('font-weight' => 'bold'));
$q3 = "SELECT * FROM `appusers` INNER JOIN stickeruses on stickeruses.user_id=appusers.id INNER JOIN stickers ON stickers.id=stickeruses.sticker_id WHERE appusers.id=" . $id['Appuser']['id'] . " ORDER BY stickeruses.count DESC";
$result = $this->Appuser->query($q3);
foreach ($result as $key => $data) {
if ($key == 0) {
$excel->writeLine(array(), array());
}
$excel->writeCol($key + 1, array());
$excel->writeCol($data['stickers']['name']);
if ($data['stickers']['is_active'] == 1) {
$excel->writeCol("Active");
} else {
$excel->writeCol("Inactive");
}
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['created'])));
$excel->writeCol(date("F j, Y, g:i A", strtotime($data['stickers']['modified'])));
$excel->writeCol($data['stickeruses']['count']);
$excel->writeLine(array());
}
$this->redirect(DS . "files" . DS . "Excel" . DS . "Appuser_Report" . "_" . $reportname . "_" . $date . ".xls");
}
// pr($result);die;
}
} else {
$this->redirect(array('controller' => 'Users', 'action' => 'index'));
}
}
<?php
include "excelwriter.inc.php";
$excel = new ExcelWriter("abc.xls");
if ($excel == false) {
echo $excel->error;
exit;
}
//write header
$header = array("<b>Col-1</b>", "<b>Col-2</b>", "Col-3");
//print header
$excel->writeLine($header);
//fetch all data from db according to requirement
while (true) {
$excel->writeRow();
$excel->writeCol("{$variable}-for-col-1");
$excel->writeCol("{$variable}-for-col-3");
$excel->writeCol("{$variable}-for-col-3");
}
$excel->close();
$file = "abc.xls";
if (file_exists($file)) {
header('Content-Description: File Transfer');
header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename=' . basename($file));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . filesize($file));
ob_clean();
/**
* 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;
}
}
}
}
}
echo '<br><br><strong>Você não tem permissão para acessar essa página</strong>';
exit;
}
//
$empresaDAO = new EmpresaDAO();
$empresas = $empresaDAO->listaAdendo();
//Você pode colocar aqui o nome do arquivo que você deseja salvar.
$arquivoDiretorio = "./exporta/adendo_" . md5(time()) . ".xls";
$excel = new ExcelWriter($arquivoDiretorio);
if ($excel == false) {
echo $excel->error;
}
$excel->writeLine(array('Cidades', 'Estado', 'Fone/Loja', 'Endereço', 'CEP', 'E-MAIL', 'Razão Social', 'CNPJ', 'Dados Bancários', 'Data adendo'));
foreach ($empresas as $e) {
$cidades = '';
$excel->writeCol($e->regioes[0]->cidade);
$excel->writeCol($e->regioes[0]->estado);
$excel->writeCol($e->tel . ' ' . $e->ramal);
$excel->writeCol($e->endereco . ',' . $e->numero . ' ' . $e->complemento);
$excel->writeCol($e->cep);
$excel->writeCol($e->email);
$excel->writeCol($e->empresa);
$excel->writeCol($e->cpf);
$excel->writeCol('Ag.:' . $e->agencia . '|Conta:' . $e->conta . '|Banco:' . $e->banco . '|' . $e->favorecido);
$excel->writeCol(invert($e->adendo_data, '/', 'php'));
foreach ($e->regioes as $i => $r) {
if ($i > 0) {
$excel->writeRow();
$excel->writeCol($r->cidade, 2);
$excel->writeCol($r->estado);
}
//print_r($arrEventInfo);exit;
//Write each datapoint to the sheet starting one row beneath
//Loop through each row
$row++;
if ($arrUsersList) {
//$column = 0;
foreach ($arrUsersList as $subArrayKey => $subArray) {
if ($subArray['pkClientID'] != '') {
/*$columnWidth = 20;
$worksheet->setColumn (0, $numColumns, $columnWidth);
$worksheet->write($row, $column, $subArray, $regularFormat);
$column++;*/
$column = 0;
$excel->writeRow();
foreach ($subArray as $varKey => $value) {
$excel->writeCol($value);
}
$row++;
}
}
}
$fdl = @fopen('client_list.xls', 'r');
header("Cache-Control: ");
# leave blank to avoid IE errors
header("Pragma: ");
# leave blank to avoid IE errors
header("Content-type: application/xls");
header("Content-Type: application/force-download");
header("Content-Disposition: attachment; filename=\"client_list.xls\"");
header("Content-length:" . (string) filesize('client_list.xls'));
//sleep(1);
/**
* 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;
}
}
}
}
}
