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); }