public function Reports::customers in Ubercart 8.4

Displays the customer report.

1 string reference to 'Reports::customers'

uc_report.routing.yml in uc_report/uc_report.routing.yml

uc_report/uc_report.routing.yml

File

uc_report/src/Controller/Reports.php, line 53

Class

Reports

Provides reports for Ubercart.

Namespace

Drupal\uc_report\Controller

Code

public function customers() {
  $address_preference = $this
    ->config('uc_store.settings')
    ->get('customer_address');
  $first_name = $address_preference == 'billing' ? 'billing_first_name' : 'delivery_first_name';
  $last_name = $address_preference == 'billing' ? 'billing_last_name' : 'delivery_last_name';
  $page = isset($_GET['page']) ? intval($_GET['page']) : 0;
  $page_size = isset($_GET['nopage']) ? UC_REPORT_MAX_RECORDS : $this
    ->config('uc_report.settings')
    ->get('table_size');
  $order_statuses = uc_report_order_statuses();
  $rows = [];
  $csv_rows = [];
  $header = [
    [
      'data' => $this
        ->t('#'),
    ],
    [
      'data' => $this
        ->t('Customer'),
      'field' => "ou.{$last_name}",
    ],
    [
      'data' => $this
        ->t('Username'),
      'field' => "u.name",
    ],
    [
      'data' => $this
        ->t('Orders'),
      'field' => 'orders',
    ],
    [
      'data' => $this
        ->t('Products'),
      'field' => 'products',
    ],
    [
      'data' => $this
        ->t('Total'),
      'field' => 'total',
      'sort' => 'desc',
    ],
    [
      'data' => $this
        ->t('Average'),
      'field' => 'average',
    ],
  ];
  $csv_rows[] = [
    $this
      ->t('#'),
    $this
      ->t('Customer'),
    $this
      ->t('Username'),
    $this
      ->t('Orders'),
    $this
      ->t('Products'),
    $this
      ->t('Total'),
    $this
      ->t('Average'),
  ];
  $query = $this->database
    ->select('users_field_data', 'u', [
    'fetch' => \PDO::FETCH_ASSOC,
  ])
    ->extend('Drupal\\Core\\Database\\Query\\PagerSelectExtender')
    ->extend('Drupal\\Core\\Database\\Query\\TableSortExtender');
  $query
    ->leftJoin('uc_orders', 'ou', 'u.uid = ou.uid');
  $query
    ->fields('u', [
    'uid',
    'name',
  ])
    ->fields('ou', [
    $first_name,
    $last_name,
  ])
    ->condition('u.uid', 0, '>')
    ->groupBy('u.uid');
  $query
    ->addExpression("(SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} o WHERE o.uid = u.uid AND o.order_status IN (:statuses[]))", 'orders', [
    ':statuses[]' => $order_statuses,
  ]);
  $query
    ->addExpression("(SELECT SUM(qty) FROM {uc_order_products} ps LEFT JOIN {uc_orders} os ON ps.order_id = os.order_id WHERE os.order_status IN (:statuses2[]) AND os.uid = u.uid)", 'products', [
    ':statuses2[]' => $order_statuses,
  ]);
  $query
    ->addExpression("(SELECT SUM(ot.order_total) FROM {uc_orders} ot WHERE ot.uid = u.uid AND ot.order_status IN (:statuses3[]))", 'total', [
    ':statuses3[]' => $order_statuses,
  ]);
  $query
    ->addExpression("ROUND((SELECT SUM(ot.order_total) FROM {uc_orders} ot WHERE ot.uid = u.uid AND ot.order_status IN (:sum_statuses[]))/(SELECT COUNT(DISTINCT(order_id)) FROM {uc_orders} o WHERE o.uid = u.uid AND o.order_status IN (:count_statuses[])), 2)", 'average', [
    ':sum_statuses[]' => $order_statuses,
    ':count_statuses[]' => $order_statuses,
  ]);
  $count_query = $this->database
    ->select('users_field_data', 'u');
  $count_query
    ->leftJoin('uc_orders', 'ou', 'u.uid = ou.uid');
  $count_query
    ->addExpression('COUNT(DISTINCT u.uid)');
  $count_query
    ->condition('u.uid', 0, '>');
  $query
    ->setCountQuery($count_query);
  $query
    ->groupBy('u.uid')
    ->groupBy('u.name')
    ->groupBy("ou.{$first_name}")
    ->groupBy("ou.{$last_name}")
    ->orderByHeader($header)
    ->limit($page_size);
  $customers = $query
    ->execute();
  foreach ($customers as $customer) {
    $name = !empty($customer[$last_name]) || !empty($customer[$first_name]) ? Link::fromTextAndUrl($customer[$last_name] . ', ' . $customer[$first_name], Url::fromUri('base:admin/store/customers/orders/' . $customer['uid']))
      ->toString() : Link::fromTextAndUrl($customer['name'], Url::fromUri('base:admin/store/customers/orders/' . $customer['uid']))
      ->toString();
    $customer_number = $page * $this
      ->config('uc_report.settings')
      ->get('table_size') + (count($rows) + 1);
    $customer_order_name = !empty($customer[$last_name]) || !empty($customer[$first_name]) ? $customer[$last_name] . ', ' . $customer[$first_name] : $customer['name'];
    $customer_name = $customer['name'];
    $orders = !empty($customer['orders']) ? $customer['orders'] : 0;
    $products = !empty($customer['products']) ? $customer['products'] : 0;
    $total_revenue = uc_currency_format($customer['total']);
    $average_revenue = uc_currency_format($customer['average']);
    $rows[] = [
      [
        'data' => $customer_number,
      ],
      [
        'data' => $name,
      ],
      [
        'data' => Link::createFromRoute($customer_name, 'entity.user.canonical', [
          'user' => $customer['uid'],
        ])
          ->toString(),
      ],
      [
        'data' => $orders,
      ],
      [
        'data' => $products,
      ],
      [
        'data' => $total_revenue,
      ],
      [
        'data' => $average_revenue,
      ],
    ];
    $csv_rows[] = [
      $customer_number,
      $customer_order_name,
      $customer_name,
      $orders,
      $products,
      $customer['total'],
      $customer['average'],
    ];
  }
  $csv_data = $this
    ->store_csv('uc_customers', $csv_rows);
  $build['report'] = [
    '#theme' => 'table',
    '#header' => $header,
    '#rows' => $rows,
    '#attributes' => [
      'width' => '100%',
      'class' => [
        'uc-sales-table',
      ],
    ],
    '#empty' => $this
      ->t('No customers found'),
  ];
  $build['pager'] = [
    '#type' => 'pager',
  ];
  $build['links'] = [
    '#prefix' => '<div class="uc-reports-links">',
    '#suffix' => '</div>',
  ];
  $build['links']['export_csv'] = [
    '#markup' => Link::createFromRoute($this
      ->t('Export to CSV file.'), 'uc_report.getcsv', [
      'report_id' => $csv_data['report'],
      'user_id' => $csv_data['user'],
    ])
      ->toString(),
    '#suffix' => '&nbsp;&nbsp;&nbsp;',
  ];
  if (isset($_GET['nopage'])) {
    $build['links']['toggle_pager'] = [
      '#markup' => Link::createFromRoute($this
        ->t('Show paged records'), 'uc_report.customers')
        ->toString(),
    ];
  }
  else {
    $build['links']['toggle_pager'] = [
      '#markup' => Link::createFromRoute($this
        ->t('Show all records'), 'uc_report.customers', [
        'query' => [
          'nopage' => '1',
        ],
      ])
        ->toString(),
    ];
  }
  return $build;
}