function read_xlsfile($filename)
{
/** PHPExcel_IOFactory */
include_once 'Classes/PHPExcel/IOFactory.php';
$inputFileName = $filename;
try {
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch (Exception $e) {
die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) . '": ' . $e->getMessage());
}
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
$row = 0;
foreach ($sheetData as $v => $i) {
if ($row == 0) {
$row = 1;
continue;
}
if (count($i) == 3 && filter_var($i['C'], FILTER_VALIDATE_EMAIL)) {
$query[] = "('" . implode("','", $i) . "')";
}
}
if (count($query) == 1000) {
bulk_insert($query);
$query = '';
}
return $query;
}
<?php
/**
* @var IndexController $this
* @var array|IKommentar[] $kommentare
*/
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=kommentare.xlsx');
header('Cache-Control: max-age=0');
define('PCLZIP_TEMPORARY_DIR', '/tmp/');
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Antragsgruen.de");
$objPHPExcel->getProperties()->setLastModifiedBy("Antragsgruen.de");
$objPHPExcel->getProperties()->setTitle($this->veranstaltung->name);
$objPHPExcel->getProperties()->setSubject("Kommentare");
$objPHPExcel->getProperties()->setDescription($this->veranstaltung->name . " - Kommentare zu Anträgen und Änderungsanträgen");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Datum');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Person');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Antrag');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Absatz / Zeilen');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Kommentar');
if ($this->veranstaltung->getEinstellungen()->kommentare_unterstuetzbar) {
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Bewertung: Positiv');
$objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Bewertung: Negativ');
$objPHPExcel->getActiveSheet()->getStyle("A1:G1")->applyFromArray(array("font" => array("bold" => true)));
} else {
$objPHPExcel->getActiveSheet()->getStyle("A1:E1")->applyFromArray(array("font" => array("bold" => true)));
}
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_AdvancedValueBinder());
public function import()
{
if ($this->request->is('post')) {
if (isset($this->request['data']['Product']['file']) and $this->request['data']['Product']['file']['error'] === 0) {
if (!in_array($this->request['data']['Product']['file']['type'], array('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'application/vnd.ms-excel', 'application/octet-stream'))) {
$this->Session->setFlash('Extensión no permitida', 'flash');
return;
}
App::uses('PHPExcel', 'Lib/PHPExcel');
if (!class_exists('PHPExcel')) {
throw new CakeException('Lib Class PHPExcel not found.');
return;
}
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$excel = PHPExcel_IOFactory::load($this->request['data']['Product']['file']['tmp_name']);
$rows = $excel->getSheet()->toArray(null, true, true, false);
foreach ($rows as $k => $v) {
$rows[$k] = array_filter($rows[$k]);
}
$rows = array_filter($rows);
unset($rows[0]);
// echo '<pre>';
// print_r($rows);
// echo '</pre>';
// exit;
foreach ($rows as $k => $v) {
if (array_key_exists(4, $v)) {
if ($Category = $this->Category->findByName($v[4])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[4];
$this->Category->parent_category_id = 0;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(5, $v)) {
if ($Category = $this->Category->findByName($v[5])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[5];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(6, $v)) {
if ($Category = $this->Category->findByName($v[6])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[6];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(7, $v)) {
if ($Category = $this->Category->findByName($v[7])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[7];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(8, $v)) {
if ($Category = $this->Category->findByName($v[8])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[8];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(9, $v)) {
if ($Category = $this->Category->findByName($v[9])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[9];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(10, $v)) {
if ($Category = $this->Category->findByName($v[10])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[10];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(11, $v)) {
if ($Category = $this->Category->findByName($v[11])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[11];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(12, $v)) {
if ($Category = $this->Category->findByName($v[12])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[12];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (array_key_exists(13, $v)) {
if ($Category = $this->Category->findByName($v[13])) {
$parent_category_id = $Category['Category']['parent_category_id'];
$category_id = $parent_category_id;
} else {
$this->Category->create();
$this->Category->name = $v[13];
$this->Category->parent_category_id = $parent_category_id;
$this->Category->save($this->Category);
$parent_category_id = $this->Category->getInsertID();
$category_id = $parent_category_id;
}
}
if (!$this->Product->findByName($v[0])) {
$this->Product->create();
$this->Product->name = $v[0];
$this->Product->description = $v[1];
$this->Product->price = $v[2];
$this->Product->code = $v[3];
$this->Product->category_id = $category_id;
$this->Product->save($this->Product);
}
}
$this->Session->setFlash('Excel importado con éxito', 'flash');
return;
}
$this->Session->setFlash('No seleccionó ningun archivo', 'flash');
}
}
/**
* Output document to browser
* @param string $filename document filename, null is gernerate by date.
*/
protected function output($filename = null)
{
$filename = $filename ?: "export-" . date('Ymd') . '.xlsx';
// change zip extension to solve unreadable in xlsx
\PHPExcel_Settings::setZipClass(\PHPExcel_Settings::PCLZIP);
// redirect output to client browser
header('Content-type: application/vnd.ms-excel');
header(sprintf('Content-Disposition: attachment; filename="%s"', $filename));
$writer = \PHPExcel_IOFactory::createWriter($this->phpExcel, 'Excel2007');
$writer->save('php://output');
}
public function download($export_type, $offset = null, $rows = null, $min_id = null, $max_id = null)
{
// Error handler
global $registry;
$registry = $this->registry;
set_error_handler('error_handler_for_export_import', E_ALL);
register_shutdown_function('fatal_error_shutdown_handler_for_export_import');
// PHPExcel
$cwd = getcwd();
chdir(DIR_SYSTEM . 'vendor');
require_once 'Classes/PHPExcel.php';
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_ExportImportValueBinder());
chdir($cwd);
// Find out whether all data is to be downloaded
$all = !isset($offset) && !isset($rows) && !isset($min_id) && !isset($max_id);
// Memory Optimization
if ($this->config->get('export_import_settings_use_export_cache')) {
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array('memoryCacheSize' => '16MB');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
}
// Export as ZIP (Beta)
if ($this->config->get('export_import_settings_use_export_pclzip')) {
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
}
try {
// Set appropriate timeout limit
set_time_limit(1800);
// Create a new workbook
$workbook = new PHPExcel();
// Set some default styles
$workbook->getDefaultStyle()->getFont()->setName('Arial');
$workbook->getDefaultStyle()->getFont()->setSize(10);
$workbook->getDefaultStyle()->getAlignment()->setWrapText(true);
$workbook->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$workbook->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$workbook->getDefaultStyle()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
// Pre-define some commonly used styles
$box_format = array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('argb' => 'FFF0F0F0')));
$text_format = array('numberformat' => array('code' => PHPExcel_Style_NumberFormat::FORMAT_TEXT));
$price_format = array('numberformat' => array('code' => '######0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
$date_format = array('numberformat' => array('code' => '0000-00-00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$datetime_format = array('numberformat' => array('code' => '0000-00-00 00:00:00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER));
$weight_format = array('numberformat' => array('code' => '##0.00'), 'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT));
// Create the worksheets
$worksheet_index = 0;
$languages = $this->getLanguages();
$default_language_id = $this->getDefaultLanguageId();
switch ($export_type) {
case 'm':
// Creating the Customers worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Customers');
$this->populateCustomersWorksheet($worksheet, $box_format, $text_format, $date_format, $datetime_format, $offset, $rows, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
case 'c':
// Creating the Categories worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Categories');
$this->populateCategoriesWorksheet($worksheet, $languages, $box_format, $text_format, $datetime_format, $offset, $rows, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the CategoryFilters worksheet
if ($this->existFilter()) {
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('CategoryFilters');
$this->populateCategoryFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
}
break;
case 'p':
// Creating the Products worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Products');
$this->populateProductsWorksheet($worksheet, $languages, $default_language_id, $box_format, $price_format, $weight_format, $text_format, $date_format, $datetime_format, $offset, $rows, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the AdditionalImages worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('AdditionalImages');
$this->populateAdditionalImagesWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the Specials worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Specials');
$this->populateSpecialsWorksheet($worksheet, $default_language_id, $box_format, $price_format, $text_format, $date_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the Discounts worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Discounts');
$this->populateDiscountsWorksheet($worksheet, $default_language_id, $box_format, $price_format, $text_format, $date_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the Rewards worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Rewards');
$this->populateRewardsWorksheet($worksheet, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the ProductOptions worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductOptions');
$this->populateProductOptionsWorksheet($worksheet, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the ProductOptionValues worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductOptionValues');
$this->populateProductOptionValuesWorksheet($worksheet, $box_format, $price_format, $weight_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the ProductAttributes worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductAttributes');
$this->populateProductAttributesWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the ProductFilters worksheet
if ($this->existFilter()) {
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('ProductFilters');
$this->populateProductFiltersWorksheet($worksheet, $languages, $default_language_id, $box_format, $text_format, $min_id, $max_id);
$worksheet->freezePaneByColumnAndRow(1, 2);
}
break;
case 'o':
// Creating the Options worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Options');
$this->populateOptionsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the OptionValues worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('OptionValues');
$this->populateOptionValuesWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
case 'a':
// Creating the AttributeGroups worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('AttributeGroups');
$this->populateAttributeGroupsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the Attributes worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Attributes');
$this->populateAttributesWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
case 'f':
if (!$this->existFilter()) {
throw new Exception($this->language->get('error_filter_not_supported'));
break;
}
// Creating the FilterGroups worksheet
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('FilterGroups');
$this->populateFilterGroupsWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
// Creating the Filters worksheet
$workbook->createSheet();
$workbook->setActiveSheetIndex($worksheet_index++);
$worksheet = $workbook->getActiveSheet();
$worksheet->setTitle('Filters');
$this->populateFiltersWorksheet($worksheet, $languages, $box_format, $text_format);
$worksheet->freezePaneByColumnAndRow(1, 2);
break;
default:
break;
}
$workbook->setActiveSheetIndex(0);
// Redirect output to client browser
$datetime = date('Y-m-d');
switch ($export_type) {
case 'm':
$filename = 'customers-' . $datetime;
if (!$all) {
if (isset($offset)) {
$filename .= "-offset-" . $offset;
} elseif (isset($min_id)) {
$filename .= "-start-" . $min_id;
}
if (isset($rows)) {
$filename .= "-rows-" . $rows;
} elseif (isset($max_id)) {
$filename .= "-end-" . $max_id;
}
}
$filename .= '.xlsx';
break;
case 'c':
$filename = 'categories-' . $datetime;
if (!$all) {
if (isset($offset)) {
$filename .= "-offset-" . $offset;
} elseif (isset($min_id)) {
$filename .= "-start-" . $min_id;
}
if (isset($rows)) {
$filename .= "-rows-" . $rows;
} elseif (isset($max_id)) {
$filename .= "-end-" . $max_id;
}
}
$filename .= '.xlsx';
break;
case 'p':
$filename = 'products-' . $datetime;
if (!$all) {
if (isset($offset)) {
$filename .= "-offset-" . $offset;
} elseif (isset($min_id)) {
$filename .= "-start-" . $min_id;
}
if (isset($rows)) {
$filename .= "-rows-" . $rows;
} elseif (isset($max_id)) {
$filename .= "-end-" . $max_id;
}
}
$filename .= '.xlsx';
break;
case 'o':
$filename = 'options-' . $datetime . '.xlsx';
break;
case 'a':
$filename = 'attributes-' . $datetime . '.xlsx';
break;
case 'f':
if (!$this->existFilter()) {
throw new Exception($this->language->get('error_filter_not_supported'));
break;
}
$filename = 'filters-' . $datetime . '.xlsx';
break;
default:
$filename = $datetime . '.xlsx';
break;
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($workbook, 'Excel2007');
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
// Clear the spreadsheet caches
$this->clearSpreadsheetCache();
exit;
} catch (Exception $e) {
$errstr = $e->getMessage();
$errline = $e->getLine();
$errfile = $e->getFile();
$errno = $e->getCode();
$this->session->data['export_import_error'] = array('errstr' => $errstr, 'errno' => $errno, 'errfile' => $errfile, 'errline' => $errline);
if ($this->config->get('config_error_log')) {
$this->log->write('PHP ' . get_class($e) . ': ' . $errstr . ' in ' . $errfile . ' on line ' . $errline);
}
return;
}
}
public function getxorder()
{
ini_set('memory_limit', '-1');
Configure::write('debug', 0);
require '../Vendor/PhpExcel/PHPExcel.php';
define('PCLZIP_TEMPORARY_DIR', '../tmp/');
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$this->loadModel('Order');
$x = $this->Order->find("all", array("contain" => array("Address", "Combination", "Combination.Vendor", "Customer"), "order" => "Order.id DESC"));
// debug($x);
// exit;
// create new empty worksheet and set default font
$this->PhpExcel->createWorksheet()->setDefaultFont('Calibri', 12);
$this->PhpExcel->getSheet()->setTitle("Orders");
// define table cells
$table = array(array('label' => __('Used Discount'), 'filter' => true), array('label' => __('Vendor Name'), 'filter' => true), array('label' => __('Combination (Dish)'), 'filter' => true), array('label' => __('Essentials'), 'filter' => true), array('label' => __('Price')), array('label' => __('Paid Amount')), array('label' => __('Quantity')), array('label' => __('Customer ID'), 'filter' => true), array('label' => __('Customer Name'), 'filter' => true), array('label' => __('Contact No.'), 'width' => 20, 'wrap' => true), array('label' => __('Payment Method')), array('label' => __('Order @'), 'filter' => true), array('label' => __('ID'), 'filter' => true), array('label' => __('ORDER_ID'), 'filter' => true), array('label' => __('Delivery Address'), 'width' => 40, 'wrap' => true));
// add heading with different font and bold text
$this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));
// add data
foreach ($x as $d) {
$this->PhpExcel->addTableRow(array($d['Order']['discount_amount'] == 0 ? "NO" : "YES", @$d['Combination']['Vendor']['name'], $d['Order']['recipe_names'], $d['Order']['essentials'], $d['Combination']['price'], $d['Order']['discount_amount'] >= $d['Combination']['price'] ? 0.0 : abs($d['Order']['discount_amount'] - $d['Combination']['price']), $d['Order']['qty'], $d['Order']['customer_id'], $d['Address']['f_name'] . " " . $d['Address']['l_name'], $d['Address']['phone_number'], $d['Order']['paid_via'], date("d-m-Y h:i A", $d['Order']['timestamp']), $d['Order']['id'], $d['Order']['sku'], $d['Address']['address']));
}
// close table and output
$file = $this->PhpExcel->addTableFooter();
$this->PhpExcel->addSheet("Customers");
$table = array(array('label' => __('id'), 'filter' => true), array('label' => __('Promo Code')), array('label' => __('Name'), 'filter' => true), array('label' => __('Mobile No.'), 'width' => 50, 'wrap' => true), array('label' => __('Registered On'), 'filter' => true), array('label' => __('Cash Promo')), array('label' => __('Total Orders')));
$this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));
$cstDate = array();
$this->loadModel('Customer');
$cst = $this->Customer->find('all');
foreach ($cst as $d) {
$c = $this->Order->find("count", array("conditions" => array("Order.customer_id" => $d['Customer']['id'])));
$cstDate[@date("d-m-Y", $d['Customer']['registered_on'])][] = $d;
$this->PhpExcel->addTableRow(array($d['Customer']['id'], $d['Customer']['my_promo_code'], $d['Customer']['name'], $d['Customer']['mobile_number'], @date("d-m-Y h:i A", $d['Customer']['registered_on']), "Rs. " . $d['Customer']['cash_by_promo'], $c));
}
$this->PhpExcel->addTableFooter();
$odr3 = array();
$chartData = array();
foreach ($x as $v3) {
$d3 = date("d-m-Y", $v3['Order']['timestamp']);
$odr3[$d3][] = $v3;
}
foreach ($odr3 as $kv3 => $cv3) {
$chartData[$kv3]['total'] = count($cv3);
$repeated = 0;
$newCount = 0;
$couponCount = 0;
foreach ($cv3 as $ev3) {
$tmp = $this->Order->find("count", array("conditions" => array("Order.customer_id" => $ev3['Order']['customer_id'])));
if ($ev3['Order']['discount_amount'] != NULL && $ev3['Order']['discount_amount'] > 0) {
$couponCount++;
}
if (@date("d-m-Y", $ev3['Customer']['registered_on']) == @date("d-m-Y", $ev3['Order']['timestamp'])) {
$newCount++;
}
if ($tmp > 1) {
$repeated++;
}
}
$chartData[$kv3]['repeated'] = $repeated;
$chartData[$kv3]['newreg'] = isset($cstDate[$kv3]) ? count($cstDate[$kv3]) : 0;
$chartData[$kv3]['Coupon'] = $couponCount;
$chartData[$kv3]['new'] = $newCount;
}
//echo json_encode($chartData);
//exit;
$this->PhpExcel->addSheet("Chart 1");
$table = array(array('label' => __('Date'), 'filter' => true), array('label' => __('Total Orders')), array('label' => __('Repeated')), array('label' => __('New Registered Customers')), array('label' => __('New Orders')), array('label' => __('Coupon'), 'filter' => true));
$this->PhpExcel->addTableHeader($table, array('name' => 'Cambria', 'bold' => true));
$this->loadModel('Customer');
$cst = $this->Customer->find('all');
foreach ($chartData as $eK => $eD) {
$this->PhpExcel->addTableRow(array($eK, $eD['total'], $eD['repeated'], $eD['newreg'], $eD['new'], $eD['Coupon']));
}
$this->PhpExcel->addTableFooter();
$this->PhpExcel->output();
exit;
}
