Example #1
0
 function initQuery($params)
 {
     $tables = Customer_Account::getTables();
     $caatg = $tables['ca_attrs_to_groups']['columns'];
     $capia = $tables['ca_person_info_attrs']['columns'];
     $capid = $tables['ca_person_info_data']['columns'];
     $cac = $tables['ca_customers']['columns'];
     $cacg = $tables['ca_customer_groups']['columns'];
     $capig = $tables['ca_person_info_groups']['columns'];
     $this->addSelectField($cac['customer_id'], 'customer_id');
     $this->addSelectField($cac['customer_account'], 'customer_account');
     $this->addSelectField($cac['customer_status'], 'customer_status');
     $this->addSelectField($cac['group_id'], 'group_id');
     if ($params['type'] == 'custom' && !preg_match("/^[0-9]+\$/", $params['search_string']) && @is_array($params['attr_group_ids'])) {
         $this->addWhereOpenSection();
         $this->WhereValue($cac['customer_account'], DB_LIKE, '%' . $params['search_string'] . '%');
         $this->WhereOR();
         foreach ($params['attr_group_ids'] as $k => $v) {
             $conditions = array('cpid' . $k . '.customer_id', DB_EQ, $cac['customer_id'], DB_AND, 'cpid' . $k . '.ag_id', DB_EQ, '\'' . $v . '\'');
             $this->addLeftJoinOnConditions('ca_person_info_data', $conditions, 'cpid' . $k);
             if ($k > 0) {
                 $this->WhereOR();
             }
             $this->WhereValue('cpid' . $k . '.data_value', DB_LIKE, '%' . $params['search_string'] . '%');
         }
         // search customer group
         $conditions = array($cac['group_id'], DB_EQ, 'cacg.group_id');
         $this->addLeftJoinOnConditions('ca_customer_groups', $conditions, 'cacg');
         $this->WhereOR();
         $this->WhereValue('cacg.group_name', DB_LIKE, '%' . $params['search_string'] . '%');
         $this->addWhereCloseSection();
     } elseif ($params['type'] == 'custom') {
         if (preg_match("/^[0-9]+\$/", $params['search_string'])) {
             $this->addWhereOpenSection();
             $this->Where($cac['customer_id'], DB_EQ, intval($params['search_string']));
             $this->WhereOR();
             $this->WhereValue($cac['customer_account'], DB_LIKE, '%' . $params['search_string'] . '%');
             $this->WhereOR();
             $this->WhereValue($cac['customer_account'], DB_LIKE, '%' . $params['search_string'] . '%' . PSEUDO_CUSTOMER_SUFFIX);
             $this->addWhereCloseSection();
         } else {
             //Impossible: handled in __searchCustomers;
             _fatal(array("CODE" => "CORE_061"), 'SELECT_SEARCH_CUSTOMERS', '$this->_customers_search_filter[\'type\'] == \'custom\'<br>BUT<br>!preg_match("/^[0-9]+$/", $this->_customers_search_filter[\'search_string\'])');
         }
     } elseif ($params['type'] == 'letter' && $params['search_string'] != '') {
         if ($params['letter_filter_by'] == 'customer_name') {
             $this->addSelectTable('ca_person_info_attrs');
             $this->addLeftJoin('ca_attrs_to_groups', $caatg['attr_id'], DB_EQ, $capia['attr_id']);
             $this->addLeftJoin('ca_person_info_data', $capid['customer_id'], DB_EQ, $cac['customer_id']);
             $this->addLeftJoin('ca_person_info_groups', $capig['group_id'], DB_EQ, $caatg['group_id']);
             $this->addWhereOpenSection();
             $this->WhereValue($capig['group_id'], DB_EQ, 1);
             $this->WhereAND();
             $this->WhereValue($capia['attr_name'], DB_EQ, 'LastName');
             $this->WhereAND();
             $this->Where($capid['ag_id'], DB_EQ, $caatg['ag_id']);
             $this->WhereAND();
             $this->WhereValue($capid['data_value'], DB_LIKE, $params['search_string'] . '%');
             $this->addWhereCloseSection();
         } else {
             $like_exp_a = $params['search_string'] . '%';
             $like_exp_b = $params['search_string'] . '%' . PSEUDO_CUSTOMER_SUFFIX;
             $this->addWhereOpenSection();
             $this->WhereValue($cac['customer_account'], DB_LIKE, $like_exp_a);
             $this->WhereOR();
             $this->WhereValue($cac['customer_account'], DB_LIKE, $like_exp_b);
             $this->addWhereCloseSection();
         }
     }
     // getting full name
     $pid1 = $this->convertAliasColumns($capid, 'pid1');
     $pid2 = $this->convertAliasColumns($capid, 'pid2');
     $pid3 = $this->convertAliasColumns($capid, 'pid3');
     $pid4 = $this->convertAliasColumns($capid, 'pid4');
     $pid5 = $this->convertAliasColumns($capid, 'pid5');
     $pid6 = $this->convertAliasColumns($capid, 'pid6');
     $this->addSelectField('IF(TRIM(CONCAT(' . $pid1['data_value'] . ', \' \', ' . $pid2['data_value'] . ')) <> \'\', TRIM(CONCAT(' . $pid1['data_value'] . ', \' \', ' . $pid2['data_value'] . ')), IF(TRIM(CONCAT(' . $pid3['data_value'] . ', \' \', ' . $pid4['data_value'] . ')) <> \'\', TRIM(CONCAT(' . $pid3['data_value'] . ', \' \', ' . $pid4['data_value'] . ')), IF(TRIM(CONCAT(' . $pid5['data_value'] . ', \' \', ' . $pid6['data_value'] . ')) <> \'\', TRIM(CONCAT(' . $pid5['data_value'] . ', \' \', ' . $pid6['data_value'] . ')), \'N\\A\')))', 'name');
     $this->addLeftJoinOnConditions('ca_person_info_data', array($pid1['ag_id'], DB_EQ, 5, DB_AND, $pid1['customer_id'], DB_EQ, $cac['customer_id']), 'pid1');
     $this->addLeftJoinOnConditions('ca_person_info_data', array($pid2['ag_id'], DB_EQ, 6, DB_AND, $pid2['customer_id'], DB_EQ, $cac['customer_id']), 'pid2');
     $this->addLeftJoinOnConditions('ca_person_info_data', array($pid3['ag_id'], DB_EQ, 14, DB_AND, $pid3['customer_id'], DB_EQ, $cac['customer_id']), 'pid3');
     $this->addLeftJoinOnConditions('ca_person_info_data', array($pid4['ag_id'], DB_EQ, 15, DB_AND, $pid4['customer_id'], DB_EQ, $cac['customer_id']), 'pid4');
     $this->addLeftJoinOnConditions('ca_person_info_data', array($pid5['ag_id'], DB_EQ, 24, DB_AND, $pid5['customer_id'], DB_EQ, $cac['customer_id']), 'pid5');
     $this->addLeftJoinOnConditions('ca_person_info_data', array($pid6['ag_id'], DB_EQ, 25, DB_AND, $pid6['customer_id'], DB_EQ, $cac['customer_id']), 'pid6');
     // getting order information
     $co_tables = Checkout::getTables();
     $orders_table = $co_tables['orders']['columns'];
     $order_prices_table = $co_tables['order_prices']['columns'];
     // orders_count
     $this->addSelectField('IF(' . $orders_table['id'] . ' IS NOT NULL, COUNT(' . $orders_table['id'] . '), 0)', 'orders_count');
     // left join criteria
     $this->addLeftJoin('orders', $cac['customer_id'], DB_EQ, $orders_table['person_id']);
     // getting currency converter rates
     $cconv_tables = Currency_Converter::getTables();
     $tmp_rates_table = $cconv_tables['cconv_temp_cur_rates']['columns'];
     if ($params['currency_code'] != $params['main_currency_code']) {
         $conv = $this->convertAliasColumns($tmp_rates_table, 'conv');
         // total_amount
         $this->addSelectField('SUM(IF(' . $order_prices_table['order_id'] . ' IS NOT NULL, ' . $tmp_rates_table['rate'] . ' * ' . $order_prices_table['order_total'] . ' / IF(' . $conv['rate'] . ' <> 0, ' . $conv['rate'] . ', 1.00)' . ', 0))', 'total_amount');
         // fully_paid_amount
         $this->addSelectField('SUM(IF(' . $order_prices_table['order_id'] . ' IS NOT NULL, IF(' . $orders_table['payment_status_id'] . '=' . ORDER_PAYMENT_STATUS_FULLY_PAID . ', ' . $tmp_rates_table['rate'] . ' * ' . $order_prices_table['order_total'] . ' / IF(' . $conv['rate'] . ' <> 0, ' . $conv['rate'] . ', 1.00)' . ', 0), 0))', 'fully_paid_amount');
     } else {
         // total_amount
         $this->addSelectField('SUM(IF(' . $order_prices_table['order_id'] . ' IS NOT NULL, ' . $tmp_rates_table['rate'] . '*' . $order_prices_table['order_total'] . ', 0))', 'total_amount');
         // fully_paid_amount
         $this->addSelectField('SUM(IF(' . $order_prices_table['order_id'] . ' IS NOT NULL, IF(' . $orders_table['payment_status_id'] . '=' . ORDER_PAYMENT_STATUS_FULLY_PAID . ', ' . $tmp_rates_table['rate'] . '*' . $order_prices_table['order_total'] . ', 0), 0))', 'fully_paid_amount');
     }
     // left join criteria
     $this->addLeftJoinOnConditions('order_prices', array($order_prices_table['order_id'], DB_EQ, $orders_table['id'], DB_AND, $order_prices_table['currency_type'], DB_EQ, '\'CURRENCY_TYPE_MAIN_STORE_CURRENCY\''));
     $this->addLeftJoin('cconv_temp_cur_rates', $tmp_rates_table['code'], DB_EQ, $order_prices_table['currency_code']);
     if ($params['currency_code'] != $params['main_currency_code']) {
         $this->addLeftJoin('cconv_temp_cur_rates', $tmp_rates_table['code'], DB_EQ, '\'' . $params['currency_code'] . '\'', 'conv');
     }
     $this->addLeftJoinOnConditions('ca_customer_groups', array($cacg['group_id'], DB_EQ, $cac['group_id']));
     // grouping
     $this->SelectGroup($cac['customer_id']);
     // sorting
     if (in_array($params['sort_by'], array('group_id', 'orders_count', 'total_amount', 'fully_paid_amount', 'name', 'customer_account'))) {
         $sb = $params['sort_by'];
         if ($sb == 'group_id') {
             $sb = $cacg['group_name'];
         }
         $this->SelectOrder($sb, @$params['sort_dir'] != 'asc' ? 'DESC' : 'ASC');
     }
     // paginating
     if (isset($params['paginator']) && is_array($params['paginator'])) {
         list($offset, $count) = $params['paginator'];
         $this->SelectLimit($offset, $count);
     }
 }
 function uninstall()
 {
     $query = new DB_Table_Delete(Customer_Account::getTables());
 }
Example #3
0
 function initQuery($params)
 {
     $stables = Subscriptions::getTables();
     $ctables = Customer_Account::getTables();
     $stable = 'subscription_email';
     $atable = 'email_address';
     $ctable = 'ca_customers';
     $this->addSelectField($stables[$atable]['columns']['email']);
     $this->addSelectField($stables[$atable]['columns']['lng']);
     $this->addSelectField($stables[$stable]['columns']['topic_id']);
     $this->WhereField($stables[$stable]['columns']['email_id'], DB_EQ, $stables[$atable]['columns']['email_id']);
     $this->WhereAND();
     $this->WhereField($stables[$atable]['columns']['customer_id'], DB_EQ, $ctables[$ctable]['columns']['customer_id']);
     $this->WhereAND();
     $this->WhereValue($ctables[$ctable]['columns']['customer_account'], DB_EQ, $params['customer']);
 }
 function __install_ProcessSettings($tag)
 {
     global $application;
     $tables = Customer_Account::getTables();
     $settings_table = $tables['ca_settings']['columns'];
     foreach ($tag->children as $s_child) {
         if ($s_child->name == 'SETTING') {
             $key = $s_child->attributes['KEY'];
             $value = $s_child->attributes['VALUE'];
             if (preg_match("/^const\\((.+)\\)/i", $value, $matches)) {
                 $value = constant($matches[1]);
             }
             $query = new DB_Insert('ca_settings');
             $query->addInsertValue($key, $settings_table['setting_key']);
             $query->addInsertValue($value, $settings_table['setting_value']);
             $application->db->PrepareSQL($query);
             $application->db->DB_Exec();
         }
     }
 }
 function __changeAccount($account, $new_account)
 {
     global $application;
     $tables = Customer_Account::getTables();
     $customers_table = $tables['ca_customers']['columns'];
     $query = new DB_Update('ca_customers');
     $query->addUpdateValue($customers_table['customer_account'], $new_account);
     $query->WhereValue($customers_table['customer_account'], DB_EQ, $account);
     $application->db->PrepareSQL($query);
     return $application->db->DB_Exec();
 }