/**
* Displays links to all products that have not been categorized.
*
* @return
* Renderable form array.
*/
public function orphans()
{
$build = array();
if ($this->config('taxonomy.settings')->get('maintain_index_table')) {
$vid = $this->config('uc_catalog.settings')->get('vocabulary');
$product_types = uc_product_types();
$field = FieldStorageConfig::loadByName('node', 'taxonomy_catalog');
//@todo - figure this out
// $field is a config object, not an array, so this doesn't work.
//$types = array_intersect($product_types, $field['bundles']['node']);
$types = $product_types;
//temporary to get this to work at all
$result = db_query('SELECT DISTINCT n.nid, n.title FROM {node_field_data} n LEFT JOIN (SELECT ti.nid, td.vid FROM {taxonomy_index} ti LEFT JOIN {taxonomy_term_data} td ON ti.tid = td.tid WHERE td.vid = :vid) txnome ON n.nid = txnome.nid WHERE n.type IN (:types[]) AND txnome.vid IS NULL', [':vid' => $vid, ':types[]' => $types]);
$rows = array();
while ($node = $result->fetchObject()) {
$rows[] = $this->l($node->title, Url::fromRoute('entity.node.edit_form', ['node' => $node->nid], ['query' => ['destination' => 'admin/store/products/orphans']]));
}
if (count($rows) > 0) {
$build['orphans'] = array('#theme' => 'item_list', '#items' => $rows);
} else {
$build['orphans'] = array('#markup' => $this->t('All products are currently listed in the catalog.'), '#prefix' => '<p>', '#suffix' => '</p>');
}
} else {
$build['orphans'] = array('#markup' => $this->t('The node terms index is not being maintained, so Ubercart can not determine which products are not entered into the catalog.'), '#prefix' => '<p>', '#suffix' => '</p>');
}
return $build;
}
/**
* Sets the order pane to show the product selection form.
*/
public function productSelectSearch($form, FormStateInterface $form_state)
{
$types = uc_product_types();
$options = array();
$query = db_select('node_field_data', 'n')->fields('n', array('nid', 'title'))->condition('n.type', $types, 'IN')->orderBy('n.title')->addTag('node_access');
if (!$form_state->isValueEmpty(['product_controls', 'product_search'])) {
$search = strtolower(str_replace('*', '%', $form_state->getValue(['product_controls', 'product_search'])));
$query->leftJoin('uc_products', 'p', 'n.nid = p.nid');
$query->condition(db_or()->condition('n.title', $search, 'LIKE')->condition('p.model', $search, 'LIKE'));
}
$result = $query->execute();
foreach ($result as $row) {
$options[$row->nid] = $row->title;
}
if (count($options) == 0) {
$options[0] = $this->t('No products found.');
}
$form_state->set('products_action', 'select');
$form_state->set('product_select_options', $options);
$form_state->set('refresh_products', NULL);
$form_state->setRebuild();
}
/**
* Overrides BooleanOperator::query().
*/
public function query()
{
$types = uc_product_types();
$this->query->addField('node', 'type');
$this->query->addWhere($this->options['group'], 'node.type', $types, empty($this->value) ? 'NOT IN' : 'IN');
}
/**
* Displays the custom product report.
*/
public function customProducts()
{
$views_column = \Drupal::moduleHandler()->moduleExists('statistics') && $this->config('statistics.settings')->get('count_content_views');
$page = isset($_GET['page']) ? intval($_GET['page']) : 0;
$page_size = isset($_GET['nopage']) ? UC_REPORT_MAX_RECORDS : variable_get('uc_report_table_size', 30);
$rows = array();
$csv_rows = array();
// Hard code the ignore of the product kit for this report.
$ignored_types = array('product_kit');
// Build an array of valid product types to include on the report.
$product_types = array();
foreach (uc_product_types() as $type) {
// Pass over any ignored types.
if (!in_array($type, $ignored_types)) {
$product_types[] = $type;
}
}
// Use default report parameters if we don't detect values in the URL.
if (arg(5) == '') {
$args = array('start_date' => mktime(0, 0, 0, date('n'), 1, date('Y') - 1), 'end_date' => REQUEST_TIME, 'status' => uc_report_order_statuses());
} else {
$args = array('start_date' => arg(5), 'end_date' => arg(6), 'status' => explode(',', arg(7)));
}
$query = db_select('node', 'n', array('fetch' => \PDO::FETCH_ASSOC))->extend('Drupal\\Core\\Database\\Query\\PagerSelectExtender')->extend('Drupal\\Core\\Database\\Query\\TableSortExtender')->limit($page_size)->fields('n', array('nid', 'title'))->condition('type', $product_types, 'IN')->groupBy('n.nid');
$query->addExpression("(SELECT SUM(qty) FROM {uc_order_products} p LEFT JOIN {uc_orders} o ON p.order_id = o.order_id WHERE o.order_status IN (:statuses[]) AND p.nid = n.nid AND o.created >= :start AND o.created <= :end)", 'sold', array(':statuses[]' => $args['status'], ':start' => $args['start_date'], ':end' => $args['end_date']));
$query->addExpression("(SELECT (SUM(p2.price * p2.qty)) FROM {uc_order_products} p2 LEFT JOIN {uc_orders} o ON p2.order_id = o.order_id WHERE o.order_status IN (:statuses[]) AND p2.nid = n.nid AND o.created >= :start AND o.created <= :end)", 'revenue', array(':statuses[]' => $args['status'], ':start' => $args['start_date'], ':end' => $args['end_date']));
$query->addExpression("(SELECT (SUM(p3.price * p3.qty) - SUM(p3.cost * p3.qty)) FROM {uc_order_products} p3 LEFT JOIN {uc_orders} o ON p3.order_id = o.order_id WHERE o.order_status IN (:statuses[]) AND p3.nid = n.nid AND o.created >= :start AND o.created <= :end)", 'gross', array(':statuses[]' => $args['status'], ':start' => $args['start_date'], ':end' => $args['end_date']));
$header = array(array('data' => t('#')), array('data' => t('Product'), 'field' => 'n.title'), array('data' => t('Sold'), 'field' => 'sold'), array('data' => t('Revenue'), 'field' => 'revenue', 'sort' => 'desc'), array('data' => t('Gross'), 'field' => 'gross'));
$csv_rows[] = array(t('#'), t('Product'), t('Sold'), t('Revenue'), t('Gross'));
if ($views_column) {
$header[] = array('data' => t('Views'), 'field' => 'nc.totalcount');
$csv_rows[0][] = t('Views');
}
$query->orderByHeader($header);
if ($views_column) {
$query->leftJoin('node_counter', 'c', 'n.nid = c.nid');
$query->addField('c', 'totalcount');
}
$products = $query->execute();
foreach ($products as $product) {
$row_cell = $page * variable_get('uc_report_table_size', 30) + count($rows) + 1;
$product_cell = $this->l($product['title'], Url::fromRoute('entity.node.canonical', ['node' => $product['nid']]));
$product_csv = $product['title'];
$sold_cell = empty($product['sold']) ? 0 : $product['sold'];
$sold_csv = $sold_cell;
$revenue_csv = empty($product['revenue']) ? 0 : $product['revenue'];
$revenue_cell = uc_currency_format($revenue_csv);
$gross_csv = empty($product['gross']) ? 0 : $product['gross'];
$gross_cell = uc_currency_format($gross_csv);
if (\Drupal::moduleHandler()->moduleExists('uc_attribute')) {
$breakdown_product = $breakdown_sold = $breakdown_revenue = $breakdown_gross = '';
// Get the SKUs from this product.
$models = uc_report_product_get_skus($product['nid']);
// Add the product breakdown rows
foreach ($models as $model) {
$sold = db_query("SELECT SUM(qty) FROM {uc_order_products} p LEFT JOIN {uc_orders} o ON p.order_id = o.order_id WHERE o.order_status IN (:statuses[]) AND p.model = :model AND p.nid = :nid AND o.created >= :start AND o.created <= :end", array(':statuses[]' => $args['status'], ':start' => $args['start_date'], ':end' => $args['end_date'], ':model' => $model, ':nid' => $product['nid']))->fetchField();
$sold = empty($sold) ? 0 : $sold;
$revenue = db_query("SELECT SUM(p.price * p.qty) FROM {uc_order_products} p LEFT JOIN {uc_orders} o ON p.order_id = o.order_id WHERE o.order_status IN (:statuses[]) AND p.model = :model AND p.nid = :nid AND o.created >= :start AND o.created <= :end", array(':statuses[]' => $args['status'], ':start' => $args['start_date'], ':end' => $args['end_date'], ':model' => $model, ':nid' => $product['nid']))->fetchField();
$revenue = empty($revenue) ? 0 : $revenue;
$gross = db_query("SELECT (SUM(p.price * p.qty) - SUM(p.cost * p.qty)) FROM {uc_order_products} p LEFT JOIN {uc_orders} o ON p.order_id = o.order_id WHERE o.order_status IN (:statuses[]) AND p.model = :model AND p.nid = :nid AND o.created >= :start AND o.created <= :end", array(':statuses[]' => $args['status'], ':start' => $args['start_date'], ':end' => $args['end_date'], ':model' => $model, ':nid' => $product['nid']))->fetchField();
$gross = empty($gross) ? 0 : $gross;
$breakdown_product .= "<br /> {$model}";
$product_csv .= "\n {$model}";
$breakdown_sold .= "<br />" . $sold;
$sold_csv .= "\n " . $sold;
$breakdown_revenue .= "<br />" . uc_currency_format($revenue);
$revenue_csv .= "\n " . $revenue;
$breakdown_gross .= "<br />" . uc_currency_format($gross);
$gross_csv .= "\n " . $gross;
}
$product_cell = $product_cell . $breakdown_product;
$sold_cell = '<strong>' . $sold_cell . '</strong>' . $breakdown_sold;
$revenue_cell = '<strong>' . $revenue_cell . '</strong>' . $breakdown_revenue;
$gross_cell = '<strong>' . $gross_cell . '</strong>' . $breakdown_gross;
}
if ($views_column) {
$views = empty($product['totalcount']) ? 0 : $product['totalcount'];
$rows[] = array(array('data' => $row_cell), array('data' => $product_cell), array('data' => $sold_cell), array('data' => $revenue_cell), array('data' => $gross_cell), array('data' => $views));
$csv_rows[] = array($row_cell, $product_csv, $sold_csv, $revenue_csv, $gross_csv, $views);
} else {
$rows[] = array(array('data' => $row_cell), array('data' => $product_cell), array('data' => $sold_cell), array('data' => $revenue_cell), array('data' => $gross_cell));
$csv_rows[] = array($row_cell, $product_csv, $sold_csv, $revenue_csv, $gross_csv);
}
}
$csv_data = $this->store_csv('uc_products', $csv_rows);
// Build the page output holding the form, table, and CSV export link.
$build['form'] = \Drupal::formBuilder()->getForm('uc_report_products_custom_form', $args);
$build['report'] = array('#theme' => 'table', '#header' => $header, '#rows' => $rows, '#attributes' => array('width' => '100%', 'class' => array('uc-sales-table')), '#empty' => t('No products found'));
$build['pager'] = array('#type' => 'pager');
$build['links'] = array('#prefix' => '<div class="uc-reports-links">', '#suffix' => '</div>');
$build['links']['export_csv'] = array('#markup' => $this->l(t('Export to CSV file.'), Url::fromRoute('uc_report.getcsv', ['report_id' => $csv_data['report'], 'user_id' => $csv_data['user']])), '#suffix' => ' ');
if (isset($_GET['nopage'])) {
$build['links']['toggle_pager'] = array('#markup' => $this->l(t('Show paged records'), Url::fromUri('base:admin/store/reports/products/custom')));
} else {
$build['links']['toggle_pager'] = array('#markup' => $this->l(t('Show all records'), Url::fromUri('base:admin/store/reports/products/custom', array('query' => array('nopage' => '1')))));
}
$build['instructions'] = array('#markup' => '<small>*' . t('Make sure %setting_name is set to %state in the <a href=":url">access log settings page</a> to enable views column.', ['%setting_name' => 'count content views', '%state' => 'enabled', ':url' => Url::fromUri('base:admin/config/system/statistics', ['query' => ['destination' => 'admin/store/reports/products/custom']])->toString()]) . '</small>');
return $build;
}
