function getTopicsEmailsCount($topics_ids, $unique = true)
{
global $application;
if (empty($topics_ids)) {
return array();
}
$tables = $this->getTables();
$etable = 'subscription_email';
$ecolumns =& $tables[$etable]['columns'];
$query = new DB_Select($etable);
if ($unique) {
$query->addSelectField(DB_Select::fCountDistinct($ecolumns['email_id']), 'email_count');
} else {
$query->addSelectField(DB_Select::fCount($ecolumns['email_id']), 'email_count');
}
$query->Where($ecolumns['topic_id'], DB_IN, DBQuery::arrayToIn($topics_ids));
$res = $application->db->getDB_Result($query);
return $res[0]['email_count'];
}
/**
*
*
* @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 delAllImagesFromProducts($products_ids)
{
global $application;
$tables = $this->getTables();
$images_table = $tables['pi_images']['columns'];
$query = new DB_Select();
$query->addSelectField($images_table['image_path']);
$query->addSelectField($images_table['thumb_path']);
$query->Where($images_table['product_id'], DB_IN, "('" . implode("','", $products_ids) . "')");
$res = $application->db->getDB_Result($query);
if (count($res) > 0) {
//$this->unlinkFiles($res);
$query = new DB_Delete('pi_images');
$query->deleteMultiLangField($images_table['alt_text'], $images_table['image_id'], 'Product_Images');
$query->Where($images_table['product_id'], DB_IN, "('" . implode("','", $products_ids) . "')");
$application->db->getDB_Result($query);
}
return;
}
/**
* Reencrypts temporary data on the server. The step of replacing RSA keys.
* It selects encrypted data by chunks from the database. It reencrypts it and
* saves back to the temporary table. If all data are reencrypted, returns
* b_finished =true in the returned array, false otherwise.
*
* @param string $rsa_private_key_cryptrsa_format old RSA private key, which
* was used to encrypt data, stored in the DB
* @param string $new_rsa_public_key_asc_format new RSA public key, which is
* used to encrypt data, decrypted by the old RSA private key.
*/
function ReplaceRSAKeyPairStep2ReencryptTmpData($rsa_private_key_cryptrsa_format, $new_rsa_public_key_asc_format)
{
global $application;
$new_rsa_public_key_cryptrsa_format = modApiFunc("Crypto", "convert_rsa_public_key_from_asc_into_cryptrsa_format", $new_rsa_public_key_asc_format);
/**
* Read out from the temporary table 500 records at a time (empirical
* value).
*
* Reencrypt by chunks, that have the same Blowfish key, it is about
* 10 database records. The decryption of one blowfish key (RSA), if no
* mathematical libraries exist, can take 10 sec.
* Check the timeout after each chunk - 2 sec.
* If no records are left and the timeout is over, exit.
*
* Write what has been reencrypted to the database.
*/
$tmp_table_name = "order_person_data" . $this->getTmpTableSuffix();
// TableInfo only, but not data. Refer to the table using AVACTIS.
$opd_tmp_info = clone_db_table_info("Checkout", "order_person_data", $tmp_table_name);
$opd_tmp = $opd_tmp_info['columns'];
# get Person Info data. Total record number.
$query = new DB_Select();
$query->addSelectField($query->fCount('*'), 'count');
$query->Where($opd_tmp['b_encrypted'], DB_EQ, "1");
$result = $application->db->getDB_Result($query);
$n_total = $result[0]['count'];
# get Person Info data.
$query = new DB_Select();
$query->addSelectField($opd_tmp['id'], 'id');
$query->addSelectField($opd_tmp['value'], 'value');
$query->addSelectField($opd_tmp['encrypted_secret_key'], 'encrypted_secret_key');
$query->addSelectField($opd_tmp['rsa_public_key_asc_format'], 'rsa_public_key_asc_format');
$query->Where($opd_tmp['b_encrypted'], DB_EQ, "1");
$query->WhereAnd();
$query->Where($opd_tmp['id'], DB_GTE, $this->ReplaceRSAKeyPairStep2ReencryptTmpDataOrderPersonDataId);
$query->SelectOrder($opd_tmp['id']);
$query->SelectLimit(0, 500);
$_person_data = $application->db->getDB_Result($query);
if (sizeof($_person_data) == 0) {
//No unreencrypted data is left. The reencryption is completed.
return array("error_msg" => "", "b_finished" => true, "progress_position" => 1.0);
} else {
$i = 0;
// a number of record from order_person_data
$start_time = time();
while (time() - $start_time < 2) {
//Process one block with the same blowfish key.
$rsa_encrypted_blowfish_key = $_person_data[$i]['encrypted_secret_key'];
/*
If the loaded Private key doesn't match the Public key storing in the database -
output an error message. Don't rewrite anything in the database.
*/
$old_rsa_public_key_asc_format = $_person_data[$i]['rsa_public_key_asc_format'];
$old_rsa_public_key_cryptrsa_format = modApiFunc("Crypto", "convert_rsa_public_key_from_asc_into_cryptrsa_format", $old_rsa_public_key_asc_format);
if (modApiFunc("Crypto", "rsa_do_public_key_match_private_key", $old_rsa_public_key_cryptrsa_format, $rsa_private_key_cryptrsa_format) === true) {
//BEGIN decrypt blowfish key.
$rsa_obj = new Crypt_RSA();
$blowfish_key = $rsa_obj->decrypt($rsa_encrypted_blowfish_key, $rsa_private_key_cryptrsa_format);
$new_blowfish_key = modApiFunc("Crypto", "blowfish_gen_blowfish_key");
$new_encrypted_blowfish_key = $rsa_obj->encrypt($new_blowfish_key, $new_rsa_public_key_cryptrsa_format);
//END decrypt blowfish key.
//Bulk INSERT will increase the rate greatly!
for (; $i < sizeof($_person_data) && $_person_data[$i]['encrypted_secret_key'] == $rsa_encrypted_blowfish_key; $i++) {
$decrypted_value = modApiFunc("Crypto", "blowfish_decrypt", base64_decode($_person_data[$i]['value']), $blowfish_key);
//Store decrypted data:
$query = new DB_Update($tmp_table_name);
$query->addUpdateValue($opd_tmp['value'], base64_encode(modApiFunc("Crypto", "blowfish_encrypt", $decrypted_value, $new_blowfish_key)));
$query->addUpdateValue($opd_tmp['encrypted_secret_key'], $new_encrypted_blowfish_key);
$query->addUpdateValue($opd_tmp['rsa_public_key_asc_format'], $new_rsa_public_key_asc_format);
$query->WhereValue($opd_tmp['id'], DB_EQ, $_person_data[$i]['id']);
$application->db->getDB_Result($query);
$this->ReplaceRSAKeyPairStep2ReencryptTmpDataOrderPersonDataId = $_person_data[$i]['id'] + 1;
$this->saveState();
//Don't lose reencrypted data and save correct number
//of the last processed record. Otherwise the timeout can occur during the
//SQL query and data in the session will be incorrect.
}
if ($i >= sizeof($_person_data)) {
break;
}
} else {
//Report an error: keys don't match.
$MessageResources =& $application->getInstance('MessageResources');
$msg = $MessageResources->getMessage('CRYPTO_RSA_PUBLIC_PRIVATE_KEYS_MISMATCH_DECRYPT_ERROR');
return array("error_msg" => $msg, "b_finished" => false, "progress_position" => 0.0);
}
}
# get Person Info data. Total record count.
$query = new DB_Select();
$query->addSelectField($query->fCount('*'), 'count');
$query->Where($opd_tmp['b_encrypted'], DB_EQ, "1");
$query->WhereAnd();
$query->Where($opd_tmp['id'], DB_LT, $this->ReplaceRSAKeyPairStep2ReencryptTmpDataOrderPersonDataId);
$result = $application->db->getDB_Result($query);
$n_done = $result[0]['count'];
return array("error_msg" => "", "b_finished" => false, "progress_position" => 1.0 * $n_done / $n_total);
}
}
function deleteCustomers($customers_ids)
{
if (!is_array($customers_ids) or empty($customers_ids)) {
return false;
}
global $application;
$ca_tables = $this->getTables();
$co_tables = modApiStaticFunc('Checkout', 'getTables');
$query = new DB_Select();
$query->addSelectTable('orders');
$query->addSelectField($co_tables['orders']['columns']['id'], 'order_id');
$query->Where($co_tables['orders']['columns']['person_id'], DB_IN, "(" . implode(", ", $customers_ids) . ")");
$res = $application->db->getDB_Result($query);
$orders_ids = array();
for ($i = 0; $i < count($res); $i++) {
$orders_ids[] = $res[$i]['order_id'];
}
if (!empty($orders_ids)) {
modApiFunc('Checkout', 'DeleteOrders', $orders_ids);
}
$accounts = array();
$query = new DB_Select();
$query->addSelectField($ca_tables['ca_customers']['columns']['customer_account'], 'customer_account');
$query->addSelectTable('ca_customers');
$query->Where($ca_tables['ca_customers']['columns']['customer_id'], DB_IN, "(" . implode(", ", $customers_ids) . ")");
$res = $application->db->getDB_Result($query);
for ($i = 0; $i < count($res); $i++) {
$accounts[] = $res[$i]['customer_account'];
}
if (!empty($accounts)) {
$query = new DB_Delete('ca_activation_keys');
$query->Where($ca_tables['ca_activation_keys']['columns']['customer_account'], DB_IN, "('" . implode("','", $accounts) . "')");
$application->db->PrepareSQL($query);
$application->db->DB_Exec();
}
$query = new DB_Delete('ca_person_info_data');
$query->Where($ca_tables['ca_person_info_data']['columns']['customer_id'], DB_IN, "(" . implode(", ", $customers_ids) . ")");
$application->db->PrepareSQL($query);
$application->db->DB_Exec();
$query = new DB_Delete('ca_customers');
$query->Where($ca_tables['ca_customers']['columns']['customer_id'], DB_IN, "(" . implode(", ", $customers_ids) . ")");
$application->db->PrepareSQL($query);
$application->db->DB_Exec();
return true;
}
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;
}
/**
* 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;
}
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 __loadOrdersSummary()
{
if (!$this->search_completed or empty($this->orders_ids)) {
return;
}
// :
// ,
// main_store_currency .
// main_store_currency,
// .
// main_store_currency.
global $application;
$co_tables = modApiStaticFunc('Checkout', 'getTables');
$orders_table = $co_tables['orders']['columns'];
$order_prices_table = $co_tables['order_prices']['columns'];
$query = new DB_Select();
$query->addSelectTable('orders');
$query->addSelectField($order_prices_table['order_total'], 'order_total');
$query->addSelectField($orders_table['payment_status_id'], 'payment_status_id');
$query->addSelectField($order_prices_table['currency_code'], 'currency_code');
$query->WhereValue($order_prices_table['currency_type'], DB_EQ, CURRENCY_TYPE_MAIN_STORE_CURRENCY);
$query->WhereAnd();
$query->Where($orders_table['id'], DB_IN, "('" . implode("','", $this->orders_ids) . "')");
$query->addLeftJoin('order_prices', $orders_table['id'], DB_EQ, $order_prices_table['order_id']);
$rows = $application->db->getDB_Result($query);
$amount = 0.0;
$fully_paid_amount = 0.0;
$main_store_currency = modApiFunc("Localization", "getCurrencyCodeById", modApiFunc("Localization", "getMainStoreCurrency"));
foreach ($rows as $order_info) {
$order_main_currency = $order_info['currency_code'];
$order_total = $order_info['order_total'];
if ($order_main_currency != $main_store_currency) {
$order_total = modApiFunc('Currency_Converter', 'convert', $order_total, $order_main_currency, $main_store_currency);
}
$amount += $order_total;
if ($order_info['payment_status_id'] == ORDER_PAYMENT_STATUS_FULLY_PAID) {
$fully_paid_amount += $order_total;
}
}
$query = new DB_Select();
$query->addSelectTable('orders');
$query->addSelectField($query->fMax($orders_table['date']), 'max_date');
$query->addSelectField($query->fMin($orders_table['date']), 'min_date');
$query->Where($orders_table['id'], DB_IN, "('" . implode("','", $this->orders_ids) . "')");
$res = $application->db->getDB_Result($query);
$this->orders_summary = array('amount' => $amount, 'max_date' => $res[0]['max_date'], 'min_date' => $res[0]['min_date'], 'fully_paid_amount' => $fully_paid_amount);
}
/**
* @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);
}
