Payment report

payment_report.php

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * Implements hook_permission().
 */
function payment_report_permission() {
  return [
    'download payment report' => [
      'title' => t('Access the payment report page'),
      'description' => t('Allows to access and download reports from the payment report page.'),
    ]
  ];
}

/**
 * Implements hook_menu().
 */
function payment_report_menu() {
  $items = [];

  $items['journal/cmp/editor/payments/download'] = [
    'title' => t('Download report'),
    'type' => MENU_LOCAL_ACTION,
    'page callback' => 'drupal_get_form',
    'page arguments' => ['payment_report_report'],
    'access arguments' => ['download payment report'],
  ];

  $items['journal/cmp/editor/payments/download/%'] = [
    'title' => t('Download report'),
    'type' => MENU_CALLBACK,
    'page callback' => ['payment_report_report_download_report'],
    'page arguments' => [5],
    'access arguments' => ['download payment report'],
  ];

  return $items;
}

/**
 * Form callback for the report download form.
 *
 * @param string $filename
 *   The filename to download.
 */
function payment_report_report_download_report(string $filename) {
  $directory = payment_report_get_data_directory(NULL, NULL, TRUE);
  $filename = urldecode($filename);
  $file_uri = $directory . DIRECTORY_SEPARATOR . $filename;
  $headers = [
    'Content-Type' => 'application/zip',
    'Content-Disposition' => "attachment; filename=$filename",
    'Content-Length' => filesize($file_uri),
  ];

  if (file_exists($file_uri)) {
    file_transfer($file_uri, $headers);
  }
  else {
    drupal_not_found();
  }
  drupal_exit();
}

/**
 * Form callback for the report download form.
 *
 * @param array $form
 *   The form array.
 * @param array $form_state
 *   The form state array.
 *
 * @return array
 *   The form.
 */
function payment_report_report(array $form, array &$form_state) {
  $form_state['filter']['year'] = !empty($form_state['values']['year']) ? $form_state['values']['year'] : (int) date('Y');
  $form_state['filter']['month'] = !empty($form_state['values']['month']) ? $form_state['values']['month'] : '';


  _payment_report_report_attach_available_files($form, $form_state);
  _payment_report_report_attach_filters($form,$form_state);
  return $form;
}

/**
 * Validation callback for the payment_report_report form.
 *
 * @param array $form
 *   The form array.
 * @param array $form_state
 *   The form state array.
 */
function payment_report_report_validate(array $form, array &$form_state) {
  if ($form_state['values']['month'] === '') {
    $form_state['filter']['month'] = NULL;
  }
  else {
    $form_state['filter']['month'] = (int) $form_state['values']['month'];
  }
  $form_state['filter']['year'] = (int) $form_state['values']['year'];
}

/**
 * Returns an array of filters for the report download form.
 *
 * @param array $form
 *   The form array.
 * @param array $form_state
 *   The form state array.
 */
function _payment_report_report_attach_filters(array &$form, array &$form_state) {
  $start_year = 2018;
  $current_year = (int) date('Y');

  $form['filters'] = [
    '#type' => 'fieldset',
    '#title' => t('Generate report'),
    '#collapsible' => FALSE,
    '#collapsed' => FALSE,
  ];
  $form['filters']['year'] = [
    '#type' => 'select',
    '#title' => t('Year'),
    '#options' => array_combine(range($start_year, $current_year), range($start_year, $current_year)),
    '#default_value' => $form_state['filter']['year'],
    '#required' => TRUE,
  ];
  $form['filters']['month'] = [
    '#type' => 'select',
    '#title' => t('Month'),
    '#options' => array_combine(range(1, 12), range(1, 12)),
    '#empty_option' => ' - Whole year - ',
    '#empty_value' => '',
    '#default_value' => $form_state['filter']['month'],
  ];

  $form['filters']['generate'] = [
    '#type' => 'submit',
    '#value' => empty($file_uri) ? t('Generate data') : t('Regenerate data'),
    '#submit' => ['payment_report_report_generate_submit'],
  ];

  if (!empty($form_state['download_data'])) {
    $form['delete_all'] = [
      '#type' => 'submit',
      '#value' => t('Clear all downloads'),
      '#submit' => ['payment_report_report_delete_all_submit'],
    ];
  }

}

/**
 * Generates the form for the report.
 *
 * @param array $form
 *   The form array.
 * @param array $form_state
 *   The form state array.
 */
function _payment_report_report_attach_available_files(&$form, &$form_state) {
  $directory = rtrim(payment_report_get_data_directory());
  $form_state['download_data'] = [];

  foreach (file_scan_directory($directory, '/.*\.zip/') as $file) {
    $form_state['download_data'][] = [
      $file->filename,
      journal_format_date(filectime($file->uri), TRUE),
      l(t('Download'), 'journal/cmp/editor/payments/download/' . urlencode($file->filename)),
    ];
  }


  $form['report_container'] = [
    '#type' => 'container',
  ];

  $form['report_container']['table_of_files'] = [
    '#theme' => 'table',
    '#tree' => TRUE,
    '#caption' => t('Available downloads'),
    '#header' => [
      t('Filename'),
      t('Created'),
      t('Actions'),
    ],
    '#rows' => $form_state['download_data'],
  ];
}


/**
 * Clears the payment report directory from all files.
 *
 * @param array $form
 *   The form array.
 * @param array $form_state
 *   The form state array.
 */
function payment_report_report_delete_all_submit($form, &$form_state) {
  $basepath = realpath(payment_report_get_data_directory());
  if ($basepath === '/') {
    throw new Exception('Private directory cannot be root "/".');
  }

  file_unmanaged_delete_recursive($basepath);
  file_prepare_directory($basepath, FILE_CREATE_DIRECTORY|FILE_MODIFY_PERMISSIONS);
}

/**
 * Generates the data in a batch process.
 *
 * The procedure will perform the following:
 * - 1 step to create a temporary folder and its sub-folders.
 * - Multiple steps that load the orders, generate the invoices PDFs and store
 * the data of the order into an array in the batch context.
 * - 1 step to zip the folder and move it to the private directory.
 *
 * @param array $form
 *   The form array.
 * @param array $form_state
 *   The form state array.
 */
function payment_report_report_generate_submit($form, &$form_state) {
  $ids = _payment_report_load_order_ids_by_time_range($form_state['filter']['year'], $form_state['filter']['month']);
  if (empty($ids)) {
    drupal_set_message('No orders have been found for the given date range.');
    return;
  }

  $form_state['rebuild'] = TRUE;
  $operations = [['payment_report_initialize_new_generate_batch', [
    $form_state['filter'],
  ]]];
  foreach ($ids as $id) {
    $operations[] = ['payment_report_collect_order_data', [$id]];
  }
  $operations[] = ['payment_report_generate_xlsx', []];
  $operations[] = ['payment_report_cleanup_generated_files', []];

  $batch = [
    'title' => t('Generating data for the given range...'),
    'operations' => $operations,
  ];
  batch_set($batch);
}

/**
 * Initialize the report generation.
 *
 * - Creates a temporary folder and stores the path to the context.
 *
 * @param array $filters
 *   The year and month settings.
 * @param array $context
 *   The batch context.
 */
function payment_report_initialize_new_generate_batch(array $filters, &$context) {
  $basepath = payment_report_get_data_directory();
  if (!is_dir($basepath)) {
    file_prepare_directory($basepath, FILE_CREATE_DIRECTORY|FILE_MODIFY_PERMISSIONS);
  }
  $basepath = realpath($basepath) . DIRECTORY_SEPARATOR;
  $filename = payment_report_generate_report_filename($filters['year'], $filters['month']);
  $private_dir = payment_report_get_data_directory($filters['year'], $filters['month']);
  // Delete the current file, if exists.
  file_unmanaged_delete($basepath . $filename);

  if ($private_dir === '/') {
    throw new Exception('Private directory cannot be root "/".');
  }

  if (is_dir($private_dir)) {
    // A request has been given to re-generate data. Delete the directory.
    // Also, delete the directory for the cases of a broken export.
    file_unmanaged_delete_recursive($private_dir);
  }
  file_prepare_directory($private_dir, FILE_CREATE_DIRECTORY|FILE_MODIFY_PERMISSIONS);
  $private_dir = realpath($private_dir) . DIRECTORY_SEPARATOR;
  $context['results']['basepath'] = $basepath;
  $context['results']['directory'] = $private_dir;
  $context['results']['filename'] =  $filename;
  $pdf_dir = $context['results']['directory'] . 'pdfs';
  // Prepare the PDFs directory as well.
  file_prepare_directory($pdf_dir, FILE_CREATE_DIRECTORY|FILE_MODIFY_PERMISSIONS);
}

/**
 * Gathers data for the given invoice entry.
 *
 * @param int $order_id
 *   The order id.
 * @param array $context
 *   The batch context.
 */
function payment_report_collect_order_data($order_id, &$context) {
  $order = commerce_order_load($order_id);
  $order_wrapper = entity_metadata_wrapper('commerce_order', $order);

  $line_items = $order_wrapper->commerce_line_items;
  $total = commerce_line_items_total($line_items);
  $total_amount = commerce_currency_format($total['amount'], $total['currency_code']);

  $context['results']['data'][$order_id] = [
    'A' => $order_wrapper->get('order_id')->value(),
    'B' => $order_wrapper->get('field_commerce_invoice_id')->value(),
    'C' => journal_format_date($order_wrapper->get('field_commerce_invoice_date')->value()),
    'D' => $order_wrapper->get('field_commerce_invoice_type')->value(),
    'E' => $total_amount,
    'F' => $order_wrapper->get('field_commerce_proof_id')->value(),
    'G' => journal_format_date($order_wrapper->get('field_commerce_proof_date')->value()),
    'H' => $order_wrapper->get('field_commerce_cancel_id')->value(),
    'I' => journal_format_date($order_wrapper->get('field_commerce_cancel_date')->value()),
  ];

  $fields = [
    'invoice' => 'field_commerce_invoice_id',
    'proof' => 'field_commerce_proof_id',
    'cancelled' => 'field_commerce_cancel_id',
  ];

  $archive_uri = $context['results']['basepath'] . $context['results']['filename'];
  $zip = new ZipArchive();
  if (is_file($archive_uri)) {
    $zip->open($archive_uri);
  }
  else {
    $zip->open($archive_uri, ZipArchive::CREATE);
  }


  foreach ($fields as $view_mode => $field_name) {
    if (empty($order->$field_name[LANGUAGE_NONE][0]['value'])) {
      continue;
    }

    $id = $order->{$field_name}[LANGUAGE_NONE][0]['value'];
    $invoice_type = $order->field_commerce_invoice_type[LANGUAGE_NONE][0]['value'];
    $html = gnest_journal_pms_pdf_html($order, $view_mode);
    $filename = $invoice_type . '-' . preg_replace('/[^a-z0-9]/', '_',
        drupal_strtolower($view_mode . '_' . $id)) . '.pdf';
    $dompdf = gnest_journal_pms_pdf_generate_dompdf($html);
    $output = $dompdf->output();

    $zip->addFromString('pdfs' . DIRECTORY_SEPARATOR . $filename, $output);
    $zip->setCompressionIndex($zip->count() - 1, ZipArchive::CM_DEFLATE);
  }
  $zip->close();
}

/**
 * Writes the generated data into a CSV file.
 *
 * @param array $context
 *   The batch context.
 */
function payment_report_generate_xlsx(&$context) {
  $spreadsheet = new Spreadsheet();
  $spreadsheet->getActiveSheet()->getStyle('A1:I1')->getFont()->setBold(true);

  $header = ['Order ID', 'Invoice ID', 'Invoice date', 'Invoice type', 'Total amount (Inc. Tax)', 'Proof ID', 'Proof date', 'Cancellation ID', 'Cancellation date'];
  $spreadsheet->getActiveSheet()->fromArray($header, NULL, 'A1');

  // We wrote on the headers, so start writing on line 2.
  $line = 2;
  foreach($context['results']['data'] as $order_data) {
    foreach ($order_data as $cell_letter => $cell_data) {
      $spreadsheet->getActiveSheet()->setCellValue( $cell_letter . $line, $cell_data);
    }
    $line++;
  }

  $spreadsheet->getActiveSheet()->setAutoFilter("A1:I{$line}");
  foreach (range('A', 'I') as $column) {
    $spreadsheet->getActiveSheet()->getColumnDimension($column)->setAutoSize(true);
  }
  $xlsx_writter = new Xlsx($spreadsheet);
  $xlsx_writter->save($context['results']['directory'] . 'payment_report.xlsx');

  $archive_uri = $context['results']['basepath'] . $context['results']['filename'];
  $zip = new ZipArchive();
  if ($zip->open($archive_uri) !== TRUE) {
    throw new Exception('Could not open zip archive for writing.');
  }

  $zip->addFile($context['results']['directory'] . 'payment_report.xlsx', 'payment_report.xlsx');
  $zip->setCompressionIndex($zip->count() - 1, ZipArchive::CM_DEFLATE);
  $zip->close();

  file_unmanaged_delete($context['results']['directory'] . 'payment_report.xlsx');
}

/**
 * Cleans up the generated files.
 *
 * @param array $context
 *   The context array.
 */
function payment_report_cleanup_generated_files(&$context) {
  // Delete the files in a separate batch step to avoid timeouts.
  file_unmanaged_delete_recursive($context['results']['directory']);
}

/**
 * Returns the payment report path in the private file storage.
 *
 * @param int $year
 *   (optional) The year directory.
 * @param int|NULL $month
 *   (optional) The month of the year directory.
 * @param bool $with_scheme
 *   (optional) If true, the uri will be returned with the drupal scheme instead
 *   of the relative path.
 *
 * @return string
 */
function payment_report_get_data_directory(int $year = NULL, int $month = NULL, $with_scheme = FALSE) {
  $base_path = $with_scheme ?
    'private://payment_report/' :
    file_stream_wrapper_get_instance_by_uri('private://')->getDirectoryPath() . '/payment_report/';

  if ($year && $month) {
    return "{$base_path}{$year}_{$month}/";
  }
  elseif ($year) {
    return "{$base_path}{$year}/";
  }
  return $base_path;
}

/**
 * Returns the filename of the report.
 *
 * @param int $year
 *   The year.
 * @param int|NULL $month
 *   (optional) The month to filter by.
 *
 * @return string
 *   The filename without the extension.
 */
function payment_report_generate_report_filename(int $year, int $month = NULL) {
  return $month ? "payment_report_{$year}_{$month}.zip" : "payment_report_{$year}.zip";
}

/**
 * Retrieves the order IDs that were placed within a time range.
 *
 * To retrieve the order IDs, we take
 *
 * @param int $year
 *   The year.
 * @param int|NULL $month
 *   (optional) The month to filter by.
 *
 * @return array
 *   An array of commerce IDs.
 */
function _payment_report_load_order_ids_by_time_range(int $year, int $month = NULL) {
  if ($month) {
    $number_of_days = cal_days_in_month(CAL_GREGORIAN, $month, $year);
    $start = mktime(0, 0, 0, $month, 1, $year);
    $end = mktime(0, 0, 0, $month, $number_of_days, $year);
  }
  else {
    $start = mktime(0, 0, 0, 1, 1, $year);
    $end = mktime(0, 0, 0, 12, 31, $year);
  }

  return db_select('field_data_field_commerce_invoice_date', 'id')
    ->condition('field_commerce_invoice_date_value', $start, '>=')
    ->fields('id', ['entity_id'])
    ->condition('field_commerce_invoice_date_value', $end, '<=')
    ->execute()
    ->fetchCol();
}