function get_audience_sql_query($selected_entry, $query_category = 'email', $is_send_coupon = false) { // This is used to take the query_name selected in the drop-down menu or singular customer email address and // generate the SQL Select query to be used to build the list of email addresses to be sent to // it only returns a query name and query string (SQL SELECT statement) // the query string is then used in a $db->Execute() command for later parsing and emailing. global $db; $query_name = ''; $queries_list = $db->Execute("select query_name, query_string from " . TABLE_QUERY_BUILDER . " " . "where query_category like '%" . $query_category . "%'"); // "where query_category = '" . $query_category . "'"); while (!$queries_list->EOF) { if ($selected_entry == $queries_list->fields['query_name']) { $query_name = $queries_list->fields['query_name']; $query_string = parsed_query_string($queries_list->fields['query_string']); //echo 'GET_AUD_EM_ADDR_QRY:<br />query_name='.$query_name.'<br />query_string='.$query_string; } $queries_list->MoveNext(); } //if no match found against queries listed in database, then $selected_entry must be an email address if ($query_name == '' && $query_category == 'email') { $cust_email_address = zen_db_prepare_input($selected_entry); $query_name = $cust_email_address; // BEGIN newsletter_subscribe mod 2/2 /*$query_string = "select customers_firstname, customers_lastname, customers_email_address from " . TABLE_CUSTOMERS . " where customers_email_address = '" . zen_db_input($cust_email_address) . "'";*/ if (!$is_send_coupon && defined('NEWSONLY_SUBSCRIPTION_ENABLED') && NEWSONLY_SUBSCRIPTION_ENABLED == 'true') { $query_string = "select c.customers_firstname, c.customers_lastname, " . "s.email_address as customers_email_address from " . TABLE_SUBSCRIBERS . " as s left join " . TABLE_CUSTOMERS . " as c on c.customers_id = s.customers_id " . " where email_address = '" . zen_db_input($cust_email_address) . "'" . " and (s.confirmed = 1 or s.customers_id >= 1) "; } else { $query_string = "select customers_firstname, customers_lastname, customers_email_address\n\t\t\t\t\t\t\t\t from " . TABLE_CUSTOMERS . "\n\t\t\t\t\t\t\t\t where customers_email_address = '" . zen_db_input($cust_email_address) . "'"; } // END newsletter_subscribe mod 2/2 } //send back a 1-row array containing the query_name and the SQL query_string return array('query_name' => $query_name, 'query_string' => $query_string); }
function get_audience_sql_query_with_language($selected_entry, $query_category = 'email') { // This is used to take the query_name selected in the drop-down menu or singular customer email address and // generate the SQL Select query to be used to build the list of email addresses to be sent to // it only returns a query name and query string (SQL SELECT statement) // the query string is then used in a $db->Execute() command for later parsing and emailing. global $db; $query_name = ''; $queries_list = $db->Execute("select query_name, query_string from " . TABLE_QUERY_BUILDER . " " . "where query_category like '%" . $query_category . "%'"); // "where query_category = '" . $query_category . "'"); list($selected_entry, $language) = spliti(',', $selected_entry); $languages_query_string = ''; if ($language != '') { list($keyname, $customers_languages_id) = spliti(':', $language); $customer_language = zen_get_language_code($customers_languages_id); $languages_query_string = "customers_languages_id = '" . (int) $customers_languages_id . "'"; if ($customer_language == DEFAULT_LANGUAGE) { $languages_query_string = "(" . $languages_query_string . " or customers_languages_id = '0')"; } } while (!$queries_list->EOF) { if ($selected_entry == $queries_list->fields['query_name']) { $query_string = $queries_list->fields['query_string']; $query_name = $queries_list->fields['query_name']; if (preg_match('/TABLE_CUSTOMERS/', $query_string) && $languages_query_string != '') { $query_string = preg_replace('/where/i', 'where ' . $languages_query_string . ' and', $query_string); $query_name .= ' (' . zen_get_language_name($customers_languages_id) . ')'; } if (preg_match('/TABLE_CUSTOMERS c/', $query_string) && $languages_query_string != '') { $query_string = preg_replace('/customers_languages_id/', 'c.customers_languages_id', $query_string); } $query_string = parsed_query_string($query_string); //echo 'GET_AUD_EM_ADDR_QRY:<br />query_name='.$query_name.'<br />query_string='.$query_string; } $queries_list->MoveNext(); } //if no match found against queries listed in database, then $selected_entry must be an email address if ($query_name == '' && $query_category == 'email') { $cust_email_address = zen_db_prepare_input($selected_entry); $query_name = $cust_email_address; $query_string = "select customers_firstname, customers_lastname, customers_email_address, customers_languages_id\n from " . TABLE_CUSTOMERS . "\n where customers_email_address = '" . zen_db_input($cust_email_address) . "'"; $mail = $db->Execute($query_string); $customers_languages_id = $mail->fields['customers_languages_id']; $query_name .= ' (' . zen_get_language_name($customers_languages_id) . ')'; } //send back a 1-row array containing the query_name and the SQL query_string return array('query_name' => $query_name, 'query_string' => $query_string, 'customers_languages_id' => $customers_languages_id); }
function get_audience_sql_query($selected_entry, $query_category = 'email') { // This is used to take the query_name selected in the drop-down menu or singular customer email address and // generate the SQL Select query to be used to build the list of email addresses to be sent to // it only returns a query name and query string (SQL SELECT statement) // the query string is then used in a $db->Execute() command for later parsing and emailing. global $db; $query_name = ''; $queries_list = $db->Execute("select query_name, query_string from " . TABLE_QUERY_BUILDER . " " . "where query_category like '%" . $query_category . "%'"); // "where query_category = '" . $query_category . "'"); while (!$queries_list->EOF) { if ($selected_entry == $queries_list->fields['query_name']) { $query_name = $queries_list->fields['query_name']; $query_string = parsed_query_string($queries_list->fields['query_string']); //echo 'GET_AUD_EM_ADDR_QRY:<br />query_name='.$query_name.'<br />query_string='.$query_string; } $queries_list->MoveNext(); } //if no match found against queries listed in database, then $selected_entry must be an email address if ($query_name == '' && $query_category == 'email') { $cust_email_address = zen_db_prepare_input($selected_entry); $query_name = $cust_email_address; $query_string = "select customers_firstname, customers_lastname, customers_email_address\n from " . TABLE_CUSTOMERS . "\n where customers_email_address = '" . zen_db_input($cust_email_address) . "'"; } //send back a 1-row array containing the query_name and the SQL query_string return array('query_name' => $query_name, 'query_string' => $query_string); }