function _affStats($st, $end)
{
$rs = IXdb::read("SELECT COUNT(s.affiliate_id) as aff_salescount, \r\n\t\t\t\t\t\tSUM(s.affiliate_value) AS aff_sales, \r\n\t\t\t\t\t\tSUM(s.affiliate_payment) AS aff_comm\r\n\t\t\t\t\t FROM " . TABLE_AFFILIATE_SALES . " s \r\n\t\t\t\t\t LEFT JOIN " . TABLE_ORDERS . " o ON (s.affiliate_orders_id = o.orders_id) \r\n\t\t\t\t\t WHERE o.orders_status > 1 \r\n\t\t\t\t\t AND s.affiliate_date >= '" . $st . "' \r\n\t\t\t\t\t AND s.affiliate_date <= '" . $end . "'\r\n\t\t\t\t\t ");
$rs['aff_newaffs'] = IXdb::read("SELECT COUNT(DISTINCT o.customers_id) as ct \r\n\t\t\t\t\t\t\t\t\t FROM " . TABLE_AFFILIATE_SALES . " s \r\n\t\t\t\t\t\t\t\t\t LEFT JOIN orders o ON (s.affiliate_orders_id=o.orders_id) \r\n\t\t\t\t\t\t\t\t\t WHERE affiliate_date >= '{$st}' \r\n\t\t\t\t\t\t\t\t\t AND affiliate_date <= '{$end}'", NULL, 'ct');
$rs['aff_clicks'] = IXdb::read("SELECT COUNT(*) AS ct \r\n\t\t\t\t\t\t\t\t FROM " . TABLE_AFFILIATE_CLICKTHROUGHS . " \r\n\t\t\t\t\t\t\t\t WHERE affiliate_clientdate >= '{$st}' \r\n\t\t\t\t\t\t\t\t AND affiliate_clientdate <= '{$end}'", NULL, 'ct');
if ($rs['aff_clicks']) {
$rs['aff_convs'] = $rs['aff_salescount'] / $rs['aff_clicks'];
}
if ($rs['aff_salescount']) {
$rs['aff_salesavg'] = $rs['aff_sales'] / $rs['aff_salescount'];
}
return $rs;
}
}
$new_cells[$j++] = $cell;
}
$cells = $new_cells;
$cells = array_map("trim", $cells);
$cpath = preg_split('!\\s*>>+\\s*!', $cells[$column_names["categories_name"]]);
$categories_id = 0;
foreach ($cpath as $cat) {
if ($cat != '') {
$cat = substr($cat, 0, 32);
$cid = IXdb::read("SELECT c.categories_id FROM categories c,categories_description cd WHERE c.categories_id=cd.categories_id AND cd.categories_name='" . addslashes($cat) . "' AND c.parent_id='{$categories_id}' AND cd.language_id='{$languages_id}'", NULL, 'categories_id');
if ($cid) {
$categories_id = $cid;
} else {
$categories_id = IXdb::store('INSERT', 'categories', array('parent_id' => $categories_id));
IXdb::store('INSERT', 'categories_description', array('categories_id' => $categories_id, 'categories_name' => $cat, 'language_id' => $languages_id));
}
}
}
/*
$query = 'SELECT categories_id, categories_name FROM categories_description WHERE categories_name="'.$cells[$column_names["categories_name"]].'"';
$result = tep_db_query ($query);
if (tep_db_num_rows($result))
{
$row = tep_db_fetch_array($result);
$categories_id = $row["categories_id"];
}
else
{
$query ='INSERT INTO categories SET categories_id=""';
$result = tep_db_query ($query);
?>
<table border="0" width="100%" cellspacing="0" cellpadding="0" >
<tr>
<td valign="top" class="pageHeading" colspan="2">
Export E-Mail Data to CSV<br><br>
</td></tr>
<tr><td>
<form action="<?php
echo $phpself;
?>
" onSubmit="return this.export_customers.checked || this.export_subscribers.checked;">
<input type="checkbox" value="1" name="export_customers" checked onClick="$('cust_groups').style.display=this.checked?'':'none'"> Export Store Customers<br>
<div id="cust_groups">
<?php
foreach (IXdb::read("SELECT * from customers_groups", 'customers_group_id', 'customers_group_name') as $gid => $grp) {
?>
<input type="checkbox" value="<?php
echo $gid;
?>
" name="export_customers_group[]" checked> <?php
echo $grp;
?>
<br>
<?php
}
?>
</div>
<input type="checkbox" value="1" name="export_subscribers" checked> Export Newsletter Subscribers<br>
<input type="submit" value="Click here to export" name="submit"></form>
</td>
?>
</td>
<td nowrap align="right"> MSRP: <?php
echo tep_draw_input_field('products_msrp', number_format($suppliers['products_msrp'], 2, '.', ''), 'style="font-size:8pt; width:55px"');
?>
</td></tr></table>
</td>
</tr>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="5" style="margin-top:5px">
<tr>
<td align="right"> Supplier: </td>
<td>
<?php
$suppliers_array = array_merge(array(array('id' => 0, 'text' => 'Make a selection')), IXdb::read("SELECT * FROM suppliers ORDER BY suppliers_group_name ASC", array(NULL), array('id' => 'suppliers_id', 'text' => 'suppliers_group_name')));
echo tep_draw_pull_down_menu('products_x_supplier', $suppliers_array, $supplierExists, 'id="supplierSelect"');
//echo tep_draw_hidden_field('suppliers_id', $supplierExists);
?>
</td>
</tr>
<tr>
<td nowrap>Case-Pack Sku:</td>
<td>
<table cellpadding="0" cellspacing="0" border="0">
<tr>
<td><?php
echo tep_draw_input_field('casepack_sku', $suppliers['casepack_sku'], 'style="font-size:8pt; width:85px"');
?>
</td>
<td nowrap> Case Qty: <?php
$outstanding = tep_db_num_rows($outstanding_query) > 0 ? tep_db_result($outstanding_query, 0) : 0;
tep_db_free_result($outstanding_query);
$orders_today_query = tep_db_query("SELECT COUNT(0) AS ct,\r\n\t\t\t\t\t\t\t\t\t\t\t SUM(ot.value) AS amount \r\n\t\t\t\t\t\t\t\t\t\tFROM " . TABLE_ORDERS . " o \r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN orders_total ot ON (o.orders_id=ot.orders_id AND ot.class='ot_total') \r\n\t\t\t\t\t\t\t\t\t\tWHERE o.date_purchased >= CURDATE()\r\n\t\t\t\t\t\t\t\t\t\tAND o.orders_status > 0");
$orders_today = tep_db_num_rows($orders_today_query) > 0 ? tep_db_fetch_array($orders_today_query) : 0;
tep_db_free_result($orders_today_query);
$orders_thisweek_query = tep_db_query("SELECT COUNT(0) AS ct,\r\n\t\t\t\t\t\t\t\t\t\t\t SUM(ot.value) AS amount \r\n\t\t\t\t\t\t\t\t\t\t FROM " . TABLE_ORDERS . " o \r\n\t\t\t\t\t\t\t\t\t\t LEFT JOIN orders_total ot ON (o.orders_id=ot.orders_id AND ot.class='ot_total') \r\n\t\t\t\t\t\t\t\t\t\t WHERE o.date_purchased >= '" . $theweek . "' \r\n\t\t\t\t\t\t\t\t\t\t AND o.orders_status > 0");
$orders_thisweek = tep_db_fetch_array($orders_thisweek_query);
tep_db_free_result($orders_thisweek_query);
$returns_count = IXdb::read("SELECT COUNT(0) AS ct FROM orders_products op,returned_products r WHERE op.exchange_returns_id=r.returns_id AND r.last_modified >= CURDATE()", NULL, 'ct');
$status_canceled_query = tep_db_query("SELECT COUNT(DISTINCT orders_id)\r\n\t\t\t\t\t\t\t\t\t\t \tFROM orders_status_history\r\n\t\t\t\t\t\t\t\t\t\t \tWHERE date_added >= CURDATE()\r\n\t\t\t\t\t\t\t\t\t\t \tAND orders_status_id = '0'\r\n\t\t\t\t\t\t\t\t\t\t ");
$status_canceled = tep_db_num_rows($status_canceled_query) > 0 ? tep_db_result($status_canceled_query, 0) : 0;
tep_db_free_result($status_canceled_query);
$status_shipped_query = tep_db_query("SELECT orders_status_id,\r\n\t\t\t\t\t\t\t\t\t\t\t\t COUNT(DISTINCT orders_id) AS ct\r\n\t\t\t\t\t\t\t\t\t\t FROM orders_status_history\r\n\t\t\t\t\t\t\t\t\t\t WHERE date_added >= CURDATE()\r\n\t\t\t\t\t\t\t\t\t\t AND orders_status_id = '3'\r\n\t\t\t\t\t\t\t\t\t\t ");
$status_shipped = tep_db_fetch_array($status_shipped_query);
tep_db_free_result($status_shipped_query);
list($orders_loss) = IXdb::read("select SUM(op.products_quantity * p.products_price_myself) AS loss FROM orders o LEFT JOIN orders_products op ON (o.orders_id=op.orders_id) LEFT JOIN products p ON (op.products_id=p.products_id) WHERE o.date_purchased>='{$theweek}' AND o.orders_status>0", NULL, 'loss');
$customers_new_query = tep_db_query("SELECT COUNT(0)\r\n\t\t\t\t\t\t\t\t\t\t\t\t FROM (\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT NULL\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM orders\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE date_purchased >= ADDDATE(CURDATE(), INTERVAL 1 - DAYOFWEEK(CURDATE())DAY)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tAND customers_id !=0\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tGROUP BY customers_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tHAVING COUNT(*) = 1\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t) AS cnt");
$customers_new = tep_db_num_rows($customers_new_query) > 0 ? tep_db_result($customers_new_query, 0) : 0;
tep_db_free_result($customers_new_query);
$customers_returning_query = tep_db_query("SELECT COUNT(0)\r\n\t\t\t\t\t\t\t\t\t\t\t\t \t FROM (\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT NULL\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM orders\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE date_purchased >= ADDDATE(CURDATE(), INTERVAL 1 - DAYOFWEEK(CURDATE())DAY)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND customers_id !=0\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tGROUP BY customers_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tHAVING COUNT(*) > 1\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t) AS cnt");
$customers_returning = tep_db_num_rows($customers_returning_query) > 0 ? tep_db_result($customers_returning_query, 0) : 0;
tep_db_free_result($customers_returning_query);
$review_count_query = tep_db_query("SELECT COUNT(0) AS ct \r\n\t\t\t\t\t\t\t\t\t\t\t\tFROM reviews \r\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE date_added >= ADDDATE(CURDATE(), INTERVAL 1 - DAYOFWEEK(CURDATE())DAY)\r\n\t\t\t\t\t\t\t\t\t\t\t ");
$review_count = tep_db_num_rows($review_count_query) > 0 ? tep_db_result($review_count_query, 0) : 0;
tep_db_free_result($review_count_query);
if ($sec == 'Dashboards') {
?>
<table width="175" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td valign="top">
<table width="100%" border="0" cellpadding="0" cellspacing="0">
$sql_data_array['entry_zone_id'] = '0';
$sql_data_array['entry_state'] = $entry_state;
}
}
tep_db_perform(TABLE_ADDRESS_BOOK, $sql_data_array, 'update', "customers_id = '" . (int) $customers_id . "' and address_book_id = '" . (int) $default_address_id . "'");
tep_redirect(tep_href_link(FILENAME_CUSTOMERS, tep_get_all_get_params(array('cID', 'action')) . 'cID=' . $customers_id));
} else {
if ($error == true) {
$cInfo = new objectInfo($HTTP_POST_VARS);
$processed = true;
}
}
break;
case 'deleteconfirm':
$customers_id = tep_db_input($_GET['cID']);
if (in_array(strtolower(IXdb::read("SELECT customers_email_address AS email FROM customers WHERE customers_id='{$customers_id}'", NULL, 'email')), $magic_emails)) {
$messageStack->add_session('You are not permitted to delete this account', 'error');
tep_redirect(tep_href_link(FILENAME_CUSTOMERS, tep_get_all_get_params(array('cID', 'action'))));
}
if (isset($_POST['delete_reviews']) && $_POST['delete_reviews'] == 'on') {
$reviews_query = tep_db_query("select reviews_id from " . TABLE_REVIEWS . " WHERE customers_id = '" . (int) $customers_id . "'");
while ($reviews = tep_db_fetch_array($reviews_query)) {
tep_db_query("DELETE FROM " . TABLE_REVIEWS_DESCRIPTION . " WHERE reviews_id = '" . (int) $reviews['reviews_id'] . "'");
}
tep_db_query("DELETE FROM " . TABLE_REVIEWS . " WHERE customers_id = '" . (int) $customers_id . "'");
} else {
tep_db_query("update " . TABLE_REVIEWS . " set customers_id = null where customers_id = '" . (int) $customers_id . "'");
}
// # Once all customers with a specific customers_group_id have been deleted from
// # the table customers, the next time a customer is deleted, all entries in the table products_groups
// # that have the (now apparently obsolete) customers_group_id will be deleted!
<?php
if ($_GET['data']) {
$qry = IXdb::query("SELECT m.*,pd.*,p.* \n\t\t\t FROM products p \n\t\t\t LEFT JOIN products_description pd ON (p.master_products_id = pd.products_id AND language_id='{$languages_id}') \n\t\t\t LEFT JOIN manufacturers m ON (m.manufacturers_id = p.manufacturers_id) \n\t\t\t ORDER BY p.master_products_id,p.products_id = p.master_products_id");
$pf = array();
while ($row = IXdb::fetch($qry)) {
if ($pf[$row['master_products_id']] && $row['products_id'] == $row['master_products_id']) {
continue;
}
$pf[$row['master_products_id']]++;
$cat = array();
$cid = IXdb::read("SELECT categories_id FROM products_to_categories WHERE products_id='{$row['master_products_id']}'", NULL, 'categories_id');
while ($cid) {
$cinfo = IXdb::read("SELECT * FROM categories c LEFT JOIN categories_description cd ON (c.categories_id=cd.categories_id AND cd.language_id='{$languages_id}') WHERE c.categories_id='{$cid}'");
$cat[] = $cinfo['categories_name'];
$cid = $cinfo['parent_id'];
}
$csv = array(join(' >> ', array_reverse($cat)), $row['products_name'], $row['products_model'], $row['products_sku'], $row['products_price'], $row['products_quantity'], $row['products_info'], $row['products_description'], $row['products_head_keywords_tag'], $row['manufacturers_name']);
$attrs = IXdb::read("SELECT * FROM products_attributes pa LEFT JOIN products_options_values pov ON (pa.options_values_id=pov.products_options_values_id AND pov.language_id='{$languages_id}') WHERE pa.products_id='{$row['products_id']}'", 'options_id', 'products_options_values_name');
foreach ($cols as $attr => $attrn) {
$csv[] = $attrs[$attr];
}
$csv[] = join(',', IXdb::read("SELECT p.products_model FROM products_xsell x LEFT JOIN products p ON (x.xsell_id=p.products_id) WHERE x.products_id='{$row['products_id']}' AND p.products_model!=''", array(NULL), 'products_model'));
foreach ($csv as $idx => $c) {
if (preg_match('/[^\\w \\.\\-]/', $c)) {
$csv[$idx] = '"' . str_replace('"', '""', $c) . '"';
}
}
echo join(',', $csv) . "\n";
}
}
require DIR_WS_INCLUDES . 'application_bottom.php';
$result = tep_db_query($query);
if (tep_db_num_rows($result)) {
$row = tep_db_fetch_array($result);
$master_products_id = $row["products_id"];
$new_master = 0;
$lst = array();
foreach (array('products_info', 'products_description', 'products_url', 'products_head_keywords_tag') as $k) {
if ($product[$k] != '') {
$lst[] = $k . '="' . mysql_real_escape_string($product[$k]) . '"';
}
}
if ($lst) {
tep_db_query("UPDATE products_description SET " . join(',', $lst) . " WHERE products_id='{$master_products_id}'");
}
if ($product["categories_id"]) {
IXdb::query('INSERT IGNORE INTO products_to_categories SET products_id=' . $master_products_id . ',categories_id=' . $product["categories_id"]);
}
} else {
// insert master
$new_master = 1;
$query = 'INSERT INTO products SET
products_sku ="' . mysql_real_escape_string($product["products_sku"]) . '",
products_model ="' . mysql_real_escape_string($product["products_model"]) . '",
products_price =' . floatval($product["products_price"]) . ',
products_weight =' . floatval($product["products_weight"]) . ',
products_quantity =' . intval($cells[$column_names["products_quantity"]]) . ',
products_image =' . floatval($product["products_image"]) . ',
manufacturers_id =' . intval($manufacturers[$column_names["manufacturers_name"]] . ',
products_status=' . $product["products_status"]);
$result = tep_db_query($query);
$master_products_id = tep_db_insert_id();