function getSubscribedTopics($email, $signed_id = null, $active_only = false)
{
global $application;
$tables = $this->getTables();
$ttable = 'subscription_topic';
$tcolumns =& $tables[$ttable]['columns'];
$etable = 'subscription_email';
$ecolumns =& $tables[$etable]['columns'];
$atable = 'email_address';
$acolumns =& $tables[$atable]['columns'];
$query = new DB_Select();
$query->addSelectTable($ttable);
$query->addSelectField($tcolumns['topic_id']);
$query->SelectGroup($tcolumns['topic_id']);
$query->addInnerJoin($etable, $ecolumns['topic_id'], DB_EQ, $tcolumns['topic_id']);
if (is_numeric($email)) {
$query->WhereValue($ecolumns['email_id'], DB_EQ, $email);
} else {
$query->addInnerJoin($atable, $acolumns['email_id'], DB_EQ, $ecolumns['email_id']);
$query->WhereValue($acolumns['email'], DB_EQ, $email);
}
if (isset($signed_in)) {
$query->WhereAND();
$access = array(SUBSCRIPTION_TOPIC_FULL_ACCESS, $signed_id ? SUBSCRIPTION_TOPIC_REGISTERED_ONLY : SUBSCRIPTION_TOPIC_GUEST_ONLY);
$query->Where($tcolumns['topic_access'], DB_IN, DBQuery::arrayToIn($access));
}
if ($active_only) {
$query->WhereAND();
$query->WhereValue($tcolumns['topic_status'], DB_EQ, SUBSCRIPTION_TOPIC_ACTIVE);
}
$res = $application->db->getDB_Result($query);
$ids = array();
foreach ($res as $r) {
$ids[$r['topic_id']] = $r['topic_id'];
}
return $ids;
}
function getPersonInfoCustomAttributeList($variant_id)
{
global $application;
$tables = $this->getTables();
$s = $tables['person_info_variants_to_attributes']['columns'];
$a = $tables['person_attributes']['columns'];
$query = new DB_Select();
$query->addSelectField($a['tag'], 'tag');
$query->addSelectField($s['name'], 'name');
$query->WhereValue($s['variant_id'], DB_EQ, $variant_id);
$query->WhereAND();
$query->WhereField($a['id'], DB_EQ, $s['attribute_id']);
$query->WhereAND();
$query->WhereValue($a['is_custom'], DB_EQ, 1);
$query->SelectOrder($s['sort']);
$result = $application->db->getDB_Result($query);
return $result;
}
/**
*
*
* @author Alexandr Girin
* @param
* @return
*/
function DeleteOrders($ordersId)
{
modApiFunc('EventsManager', 'throwEvent', 'OrdersWillBeDeleted', $ordersId);
global $application;
$tables = $this->getTables();
$on = $tables['order_notes']['columns'];
$opd = $tables['order_person_data']['columns'];
$opr = $tables['order_prices']['columns'];
$otx = $tables['order_taxes']['columns'];
$otdo = $tables['order_tax_display_options']['columns'];
$op = $tables['order_product']['columns'];
$opca = $tables['order_product_custom_attributes']['columns'];
$opta = $tables['order_product_to_attributes']['columns'];
$opot = $tables['order_product_options']['columns'];
$o = $tables['orders']['columns'];
$DB_IN_string = "('" . implode("', '", $ordersId) . "')";
$query = new DB_Select();
$query->addSelectField($op['id'], 'id');
$query->WhereField($op['order_id'], DB_IN, $DB_IN_string);
$order_products_id = $application->db->getDB_Result($query);
foreach ($order_products_id as $key => $order_product_id) {
$order_products_id[$key] = $order_product_id['id'];
}
$query = new DB_Delete('order_notes');
$query->WhereField($on['order_id'], DB_IN, $DB_IN_string);
$application->db->getDB_Result($query);
$query = new DB_Delete('order_person_data');
$query->WhereField($opd['order_id'], DB_IN, $DB_IN_string);
$application->db->getDB_Result($query);
$query = new DB_Delete('order_prices');
$query->WhereField($opr['order_id'], DB_IN, $DB_IN_string);
$application->db->getDB_Result($query);
$query = new DB_Delete('order_taxes');
$query->WhereField($otx['order_id'], DB_IN, $DB_IN_string);
$application->db->getDB_Result($query);
$query = new DB_Delete('order_tax_display_options');
$query->WhereField($otdo['order_id'], DB_IN, $DB_IN_string);
$application->db->getDB_Result($query);
$query = new DB_Delete('order_product');
$query->WhereField($op['order_id'], DB_IN, $DB_IN_string);
$application->db->getDB_Result($query);
$query = new DB_Delete('order_product_custom_attributes');
$query->WhereField($opca['product_id'], DB_IN, "('" . implode("', '", $order_products_id) . "')");
$application->db->getDB_Result($query);
$query = new DB_Delete('order_product_to_attributes');
$query->WhereField($opta['product_id'], DB_IN, "('" . implode("', '", $order_products_id) . "')");
$application->db->getDB_Result($query);
$query = new DB_Select();
$query->addSelectField($opot['option_value'], 'option_value');
$query->WhereValue($opot['is_file'], DB_EQ, 'Y');
$query->WhereAND();
$query->Where($opot['order_product_id'], DB_IN, "('" . implode("', '", $order_products_id) . "')");
$__res = $application->db->getDB_Result($query);
if (count($__res) > 0) {
foreach ($__res as $oinfo) {
if ($oinfo['option_value'] != '') {
modApiFunc('Shell', 'removeDirectory', dirname($oinfo['option_value']));
}
}
}
$query = new DB_Delete('order_product_options');
$query->WhereField($opot['order_product_id'], DB_IN, "('" . implode("', '", $order_products_id) . "')");
$application->db->getDB_Result($query);
modApiFunc("PromoCodes", "DeleteOrders", $ordersId);
modApiFunc("TaxExempts", "DeleteOrders", $ordersId);
modApiFunc('GiftCertificateApi', 'DeleteOrders', $ordersId);
$query = new DB_Delete('orders');
$query->WhereField($o['id'], DB_IN, $DB_IN_string);
$application->db->getDB_Result($query);
}
function delImagesFromProduct($product_id, $images_ids, $del_from_server)
{
global $application;
$tables = $this->getTables();
$imgs_table = $tables['pi_images']['columns'];
$query = new DB_Select();
$query->addSelectField($imgs_table['image_path']);
$query->addSelectField($imgs_table['thumb_path']);
$query->WhereValue($imgs_table['product_id'], DB_EQ, $product_id);
$query->WhereAND();
$query->Where($imgs_table['image_id'], DB_IN, '(\'' . implode('\',\'', $images_ids) . '\')');
$res = $application->db->getDB_Result($query);
if (strcmp($del_from_server, 'yes') == 0) {
$this->unlinkFiles($res);
}
$query = new DB_Delete('pi_images');
$query->deleteMultiLangField($imgs_table['alt_text'], $imgs_table['image_id'], 'Product_Images');
$query->WhereValue($imgs_table['product_id'], DB_EQ, $product_id);
$query->WhereAND();
$query->Where($imgs_table['image_id'], DB_IN, '(\'' . implode('\',\'', $images_ids) . '\')');
$application->db->getDB_Result($query);
return;
}
function getOrderCoupons($order_id = NULL, $coupon_id = NULL)
{
global $application;
$tables = $this->getTables();
$tr = $tables['order_promo_codes']['columns'];
$result_array = array();
$query = new DB_Select();
$query->addSelectField($tr["order_id"], "order_id");
$query->addSelectField($tr["coupon_id"], "coupon_id");
$query->addSelectField($tr["coupon_promo_code"], "coupon_promo_code");
$query->WhereValue('', '', '1');
if ($order_id !== NULL) {
$query->WhereAnd();
$query->WhereValue($tr["order_id"], DB_EQ, $order_id);
}
if ($coupon_id !== NULL) {
$query->WhereAND();
$query->WhereValue($tr["coupon_id"], DB_EQ, $coupon_id);
}
$result_rows = $application->db->getDB_Result($query);
return $result_rows;
}
function getPersonInfoGroupAttrs($group_id, $attr_vis = PERSON_INFO_GROUP_ATTR_ALL)
{
global $application;
$tables = $this->getTables();
$pia_table = $tables['ca_person_info_attrs']['columns'];
$atg_table = $tables['ca_attrs_to_groups']['columns'];
$query = new DB_Select();
$query->addSelectTable('ca_person_info_attrs');
$query->addSelectTable('ca_attrs_to_groups');
$query->addSelectField($pia_table['attr_id'], 'attr_id');
$query->addSelectField($pia_table['attr_name'], 'attr_name');
$query->addSelectField($pia_table['lang_code'], 'lang_code');
$query->setMultiLangAlias('_ml_name', 'ca_attrs_to_groups', $atg_table['visible_name'], $atg_table['ag_id'], 'Customer_Account');
$query->addSelectField($query->getMultiLangAlias('_ml_name'), 'visible_name');
$query->addSelectField($atg_table['is_visible'], 'is_visible');
$query->addSelectField($atg_table['is_required'], 'is_required');
$query->Where($atg_table['group_id'], DB_EQ, $group_id);
$query->WhereAND();
$query->Where($pia_table['attr_id'], DB_EQ, $atg_table['attr_id']);
if ($attr_vis == PERSON_INFO_GROUP_ATTR_VISIBLE) {
$query->WhereAND();
$query->WhereValue($atg_table['is_visible'], DB_EQ, 'Y');
} elseif ($attr_vis == PERSON_INFO_GROUP_ATTR_HIDDEN) {
$query->WhereAND();
$query->WhereValue($atg_table['is_visible'], DB_EQ, 'N');
}
$query->SelectOrder($atg_table['sort_order']);
$res = $application->db->getDB_Result($query);
$group_name = $this->getPersonInfoGroupNameByID($group_id);
if ($group_name == 'Customer') {
$attrs = $res;
return $attrs;
}
foreach ($res as $k => $attr_info) {
if ($attr_vis == PERSON_INFO_GROUP_ATTR_ALL) {
$attrs[] = $attr_info;
}
if ($attr_vis == PERSON_INFO_GROUP_ATTR_VISIBLE) {
if ($this->__isCOAttrVisible($this->detectCOPITypeID($group_name), $this->detectCOAttrID($attr_info['attr_name']))) {
$attrs[] = $attr_info;
}
}
if ($attr_vis == PERSON_INFO_GROUP_ATTR_HIDDEN) {
if ($this->__isCOAttrHidden($this->detectCOPITypeID($group_name), $this->detectCOAttrID($attr_info['attr_name']))) {
$attrs[] = $attr_info;
}
}
}
return $attrs;
}
function addCustomInfoTag($tagname, $actionIDs = 'order')
{
global $application;
if ($actionIDs == 'order') {
$actionIDs = array(1, 2, 3, 4);
}
if (!is_array($actionIDs)) {
$actionIDs = array($actionIDs);
}
$tagname = '{' . $tagname . '}';
$tables = $this->getTables();
$ni = $tables['notification_infotags']['columns'];
$i2a = $tables['infotags_to_action']['columns'];
// checking if the infotag already exists
$query = new DB_Select();
$query->addSelectField($ni['id'], 'id');
$query->WhereValue($ni['name'], DB_EQ, $tagname);
$result = $application->db->getDB_Result($query);
if (!empty($result)) {
$id = $result[0]['id'];
} else {
$query = new DB_Insert('notification_infotags');
$query->addInsertValue($tagname, $ni['name']);
$application->db->getDB_Result($query);
$id = $application->db->DB_Insert_Id();
}
foreach ($actionIDs as $aid) {
$query = new DB_Select();
$query->addSelectField($i2a['id'], 'id');
$query->WhereValue($i2a['ni_id'], DB_EQ, $id);
$query->WhereAND();
$query->WhereValue($i2a['na_id'], DB_EQ, $aid);
$result = $application->db->getDB_Result($query);
if (empty($result)) {
$query = new DB_Insert('infotags_to_action');
$query->addInsertValue($id, $i2a['ni_id']);
$query->addInsertValue($aid, $i2a['na_id']);
$application->db->getDB_Result($query);
}
}
}
function getOrderGCs($order_id = NULL, $gc_code = NULL)
{
global $application;
$tables = $this->getTables();
$tr = $tables['order_gc']['columns'];
$result_array = array();
$query = new DB_Select();
$query->addSelectField($tr["order_id"], "order_id");
$query->addSelectField($tr["gc_id"], "gc_id");
$query->addSelectField($tr["gc_code"], "gc_code");
if ($order_id !== NULL) {
#$query->WhereAnd();
$query->WhereValue($tr["order_id"], DB_EQ, $order_id);
}
if ($gc_code !== NULL) {
if ($order_id !== NULL) {
$query->WhereAND();
}
$query->WhereValue($tr["gc_code"], DB_EQ, $gc_code);
}
$result_rows = $application->db->getDB_Result($query);
return $result_rows;
}
/**
* . PORTION_MAX_EXPORT_TIME .
*/
function sendMessagesPortion()
{
global $application;
loadCoreFile('ascHtmlMimeMail.php');
$mailer = new ascHtmlMimeMail();
$from = $this->_currentMessage['letter_from_name'] . ' <' . $this->_currentMessage['letter_from_email'] . '>';
$mailer->setFrom($from);
$mailer->setSubject($this->_currentMessage['letter_subject']);
$html = "<html><head><title>{$this->_currentMessage['letter_subject']}</title></head><body>{$this->_currentMessage['letter_html']}</body></html>";
$mailer->setHtml($html);
$start_time = $this->microtime_float();
$sent_count = 0;
//
// $max_to_send
//
$table = 'newsletter_temp';
$tables = $this->getTables();
// PORTION_MAX_MESSAGES_NUM , (_sentCountTotal + 1)
// , PORTION_MAX_MESSAGES_NUM
$query = new DB_Select();
$query->addSelectTable($table);
$query->addSelectField($tables[$table]['columns']['recipient_value']);
$query->addWhereOpenSection();
$query->WhereValue($tables[$table]['columns']['recipient_num'], DB_GTE, $this->_sentCountTotal + 1);
$query->WhereAND();
$query->WhereValue($tables[$table]['columns']['recipient_num'], DB_LTE, $this->_sentCountTotal + PORTION_MAX_MESSAGES_NUM);
$query->addWhereCloseSection();
$res = $application->db->getDB_Result($query);
$addr_num = count($res);
while ($this->microtime_float() - $start_time < PORTION_MAX_EXPORT_TIME && $sent_count < $addr_num) {
//
//
//
$mailer->send(array($res[$sent_count]['recipient_value']));
$sent_count++;
// :
/*debug*/
//usleep(200000);
}
$this->_sentCountTotal += $sent_count;
$sending_status = 'PROCESSING';
if ($this->_sentCountTotal >= $this->_totalRecipients) {
$this->_sentCountTotal = $this->_totalRecipients;
//
//
//
$table = 'newsletter_temp';
$tables = $this->getTables();
$query = new DB_Delete($table);
$application->db->PrepareSQL($query);
$application->db->DB_Exec();
$this->updateMessage($this->_currentMessage['letter_id'], array('letter_sent_date' => date('Y-m-d G:i:s')));
$sending_status = 'COMPLETED';
}
return array('errors' => '', 'warnings' => '', 'sent_total' => $this->_sentCountTotal, 'sending_status' => $sending_status);
}
function __getMaxInventorySortOrder($parent_entity, $entity_id)
{
global $application;
$tables = $this->getTables();
$ex_table = $tables['po_inventory']['columns'];
$query = new DB_Select();
$query->addSelectField($query->fMax($ex_table['sort_order']), 'max_sort_order');
$query->WhereValue($ex_table['parent_entity'], DB_EQ, $parent_entity);
$query->WhereAND();
$query->WhereValue($ex_table['entity_id'], DB_EQ, $entity_id);
$res = $application->db->getDB_Result($query);
return $res[0]['max_sort_order'];
}
function delAllFilesFromProducts($products_ids)
{
global $application;
$tables = $this->getTables();
$files_table = $tables['pf_files']['columns'];
$query = new DB_Select();
$query->addSelectField($files_table['file_path']);
$query->Where($files_table['product_id'], DB_IN, "('" . implode("','", $products_ids) . "')");
$query->WhereAND();
$query->WhereValue($files_table['is_uploaded'], DB_EQ, 'Y');
$res = $application->db->getDB_Result($query);
$this->unlinkFiles($res);
$query = new DB_Delete('pf_files');
$query->Where($files_table['product_id'], DB_IN, "('" . implode("','", $products_ids) . "')");
$application->db->PrepareSQL($query);
$application->db->DB_Exec();
return;
}
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);
}
/**
* @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);
}
