/** * 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; } } } } }