function getLettersEmailsCount($letters_ids)
{
global $application;
if (empty($letters_ids)) {
return array();
}
$tables = $this->getTables();
$ntables = modApiFunc('Newsletter', 'getTables');
$ltable = 'newsletter_topics';
$lcolumns =& $ntables[$ltable]['columns'];
$ttable = 'subscription_topic';
$tcolumns =& $tables[$ttable]['columns'];
$etable = 'subscription_email';
$ecolumns =& $tables[$etable]['columns'];
$query = new DB_Select($ltable);
$query->addSelectField($lcolumns['letter_id']);
$query->addSelectField($ecolumns['email_id']);
$query->addInnerJoin($ttable, $lcolumns['topic_id'], DB_EQ, $tcolumns['topic_id']);
$query->addSelectField(DB_Select::fCountDistinct($ecolumns['email_id']), 'topic_emails');
$query->addLeftJoin($etable, $ecolumns['topic_id'], DB_EQ, $tcolumns['topic_id']);
$query->Where($lcolumns['letter_id'], DB_IN, DBQuery::arrayToIn($letters_ids));
$query->SelectGroup($lcolumns['letter_id']);
$res = $application->db->getDB_Result($query);
$counts = array();
foreach (array_keys($res) as $i) {
$r =& $res[$i];
$counts[$r['letter_id']] = $r['topic_emails'];
}
return $counts;
}
function createUnsubscribeKeys($letter_id, $delivery_num, $topics_ids)
{
global $application;
$str_topics_ids = implode(',', $topics_ids);
// copy emails to unsubscribe table
$ntables = $this->getTables();
$stables = modApiFunc('Subscriptions', 'getTables');
$utable = 'newsletter_unsubscribe';
$ucolumns = $ntables[$utable]['columns'];
$ltable = 'newsletter_topics';
$lcolumns = $ntables[$ltable]['columns'];
$etable = 'subscription_email';
$ecolumns = $stables[$etable]['columns'];
$atable = 'email_address';
$acolumns = $stables[$atable]['columns'];
$iquery = new DB_Insert_Select($utable);
$iquery->setModifiers(DB_IGNORE);
$iquery->setInsertFields(array('key_unsubscribe', 'delivery_num', 'letter_id', 'email_id', 'topics_ids'));
$squery = new DB_Select($etable);
$squery->addSelectField('MD5(CONCAT("' . $delivery_num . '", "' . $letter_id . '", "' . $str_topics_ids . '", NOW(), ' . $ecolumns['email_id'] . ', ' . $acolumns['email'] . '))');
$squery->addSelectField($delivery_num . '-0', 'delivery_num');
$squery->addSelectField($letter_id . '+0', 'letter_id');
$squery->addSelectField($ecolumns['email_id']);
$squery->addSelectField(DBQuery::quoteValue($str_topics_ids));
$squery->addInnerJoin($atable, $ecolumns['email_id'], DB_EQ, $acolumns['email_id']);
$squery->Where($ecolumns['topic_id'], DB_IN, DBQuery::arrayToIn($topics_ids));
$squery->SelectGroup($ecolumns['email_id']);
$squery->SelectOrder($ecolumns['email_id'], 'ASC');
$iquery->setSelectQuery($squery);
$application->db->getDB_Result($iquery);
$this->_totalRecipients = $this->countTempEmails($delivery_num);
$this->_sentCountTotal = 0;
$result = array('Errors' => array($application->db->_getSQL($iquery)), 'Warnings' => array(), 'TotalCount' => $this->_totalRecipients, 'Num' => $delivery_num);
return $result;
}
function __searchOrders()
{
global $application;
$tables = modApiStaticFunc('Checkout', 'getTables');
$orders_table = $tables['orders']['columns'];
$query = new DB_Select();
$query->addSelectTable('orders');
$query->addSelectField($orders_table['id'], 'order_id');
$query->WhereValue($orders_table['person_id'], DB_EQ, $this->base_info['ID']);
if ($this->orders_filter['type'] == 'id') {
$query->WhereAND();
$query->WhereValue($orders_table['id'], DB_EQ, $this->orders_filter['order_id']);
}
$query->SelectGroup($orders_table['id']);
$query->SelectOrder($orders_table['id'], 'DESC');
$oids_wo_filter = array();
$res = $application->db->getDB_Result($query);
for ($i = 0; $i < count($res); $i++) {
$oids_wo_filter[] = $res[$i]['order_id'];
}
if ($this->orders_filter['type'] != 'custom' and ($this->orders_filter['order_status'] == ORDER_STATUS_ALL or empty($oids_wo_filter))) {
$this->__setOrdersIDs($oids_wo_filter);
return;
}
$query = new DB_Select();
$query->addSelectTable('orders');
$query->addSelectField($orders_table['id'], 'order_id');
if ($this->orders_filter['type'] == 'quick') {
$query->WhereValue($orders_table['status_id'], DB_EQ, $this->orders_filter['order_status']);
}
if ($this->orders_filter['type'] == 'custom') {
$from_date = implode("-", array($this->orders_filter['year_from'], $this->orders_filter['month_from'], $this->orders_filter['day_from'])) . ' 00:00:00';
$to_date = implode("-", array($this->orders_filter['year_to'], $this->orders_filter['month_to'], $this->orders_filter['day_to'])) . ' 23:59:59';
$query->WhereValue($orders_table['date'], DB_GTE, $from_date);
$query->WhereAND();
$query->WhereValue($orders_table['date'], DB_LTE, $to_date);
if ($this->orders_filter['order_status'] != ORDER_STATUS_ALL) {
$query->WhereAND();
$query->WhereValue($orders_table['status_id'], DB_EQ, $this->orders_filter['order_status']);
}
if ($this->orders_filter['order_payment_status'] != ORDER_PAYMENT_STATUS_ALL) {
$query->WhereAND();
$query->WhereValue($orders_table['payment_status_id'], DB_EQ, $this->orders_filter['order_payment_status']);
}
}
$query->WhereAND();
$query->Where($orders_table['id'], DB_IN, "('" . implode("','", $oids_wo_filter) . "')");
$oids_with_filter = array();
$res = $application->db->getDB_Result($query);
for ($i = 0; $i < count($res); $i++) {
$oids_with_filter[] = $res[$i]['order_id'];
}
$this->__setOrdersIDs($oids_with_filter);
}
/**
* Checks if options are used during InventoryTracking.
*
* @param array $oids - index array of option IDs
* @return bool; true if at least one option is used, false no option is used
*/
function __isUsedForIT($oids)
{
global $application;
$tables = $this->getTables();
$options_table = $tables['po_options']['columns'];
$query = new DB_Select();
$query->addSelectField($options_table['use_for_it'], 'use_for_it');
$query->addSelectField($query->fCount('*'), 'uit_cnt');
$query->Where($options_table['option_id'], DB_IN, "('" . implode("','", $oids) . "')");
$query->SelectGroup($options_table['use_for_it']);
$res = $application->db->getDB_Result($query);
for ($i = 0; $i < count($res); $i++) {
if ($res[$i]["use_for_it"] == "Y" and $res[$i]["uit_cnt"] > 0) {
return true;
}
}
return false;
}
/**
* @param int $category_id - ID
* @param array $period = ('begin' => timestamp, 'end' => timestamp) -
*
* @param int $limit - (
* , STAT_NO_LIMIT)
* @param int $what_category = STAT_CATEGORY_THIS_ONLY ||
* STAT_CATEGORY_RECURSIVE -
*
* @param int $what_products = STAT_PRODUCTS_ALL ||
* STAT_PRODUCTS_EXISTS_ONLY - ,
*
*/
function getProductsSellingStat($category_id, $period, $limit = STAT_NO_LIMIT, $what_category = STAT_CATEGORY_THIS_ONLY, $what_products = STAT_PRODUCTS_EXISTS_ONLY)
{
global $application;
$tables = $this->getTables();
$ps_table = $tables['stat_products_sold']['columns'];
$categories_ids = array();
if ($what_category == STAT_CATEGORY_RECURSIVE) {
$categories = modApiFunc('Catalog', 'getSubcategoriesFullListWithParent', $category_id, false, false);
foreach ($categories as $cat_info) {
$categories_ids[] = $cat_info['id'];
}
} else {
$categories_ids[] = $category_id;
}
$query = new DB_Select();
$query->addSelectField($ps_table['product_id'], 'product_id');
$query->addSelectField($query->fSum($ps_table['quantity']), 'sum_quantity');
$query->addSelectTable('stat_products_sold');
$query->WhereValue($ps_table['categories_ids'], DB_REGEXP, '[[.vertical-line.]]' . implode('|', $categories_ids) . '[[.vertical-line.]]');
$query->WhereAND();
$query->Where($ps_table['time'], DB_GTE, $period['begin']);
$query->WhereAND();
$query->Where($ps_table['time'], DB_LTE, $period['end']);
if ($what_products == STAT_PRODUCTS_EXISTS_ONLY) {
$catalog_tables = modApiStaticFunc('Catalog', 'getTables');
$query->addSelectTable('products');
$query->WhereAND();
$query->WhereField($ps_table['product_id'], DB_EQ, $catalog_tables['products']['columns']['id']);
}
$query->SelectGroup('product_id');
$query->SelectOrder('sum_quantity', 'DESC');
if ($limit != STAT_NO_LIMIT) {
$query->SelectLimit(0, $limit);
}
return $application->db->getDB_Result($query);
}
