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