CoffeeCake

Oh that's awful. gits!

When sending a warning, the popup dialog covers the entire content you clicked from, including who the warning is going to. There's nothing in the box that tells you who is receiving the message, nor that shows you the content itself so you can reference both in the message to moderators or to the member. This is some first class nonsense. I have the memory of a goldfish left out of water for a day and a half, and can't remember how many letters or numbers or whatever a person had in their display name to address them properly, nor can I grab quotes of text from the content easily to reference. Please fix this UX oversight. K. thx. bai.

You should test this and make sure you're okay with the output.

No worries--just wanted to make you aware of the issue.

This account only has access to parts of Commerce, and nothing under members, yet the Members area appears, with a menu item for Member Notes. When clicking on it, they see the following:

Hi Adriano, I noticed that if no access is granted to an administrator group in the ACP for this application, the administrator still sees the member note icon upon login to ACP, and when clicking, gets an error that no access is available. Expected behavior would be that the icon should not appear if no access is granted similar to any other ACP permission.

That seems a bit out of scope for this add on, yet here's something that claims to do this: Though, it seems that add on also blocks disposable emails using a different mechanism. @Makoto's version uses Kickbox, while the one above uses valdator.pizza.

No, it should be real time unless you have replication turned on (multiple MySQL servers, this is not typical). Without looking at your database directly, I'd be taking guesses. The query doesn't look at invoices, but rather looks at transactions. You may be getting NULL values if you have transactions in currencies other than AED and USD, or had transactions with a status other than okay and rfnd. You'd see negative values if you've refunded more than a person has paid, or if the person paid prior to the two year cut off and the refund was after the two year cut off. If you'd like to remove negative and null values, add the following to the end of the query: WHERE ROUND(`aggregate_transactions`.`usd_and_aed_transactions_only_sum`,2) >= 0 Change this line to be whatever date range you'd like: `t_date` >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 2 YEAR))

What do you mean? The members returned by that query are those with a customer record in Commerce, and that have spent money. The total spend is being calculated by Commerce based on the currency of the transaction. As you have people paying in different forms of currency, you'll need to figure out how you want to handle that as an additional exercise. If you want to convert non-AED amounts into AED, you'd need to sort out the current exchange rate between the currencies (or the exchange rate at the time of the transaction). This is not data available within IPS. Right now, Google tells me that AED is tied to the US dollar at a fixed rate and has been for some time, so if you're confident that works for you (I know nothing about foreign currencies), and it's true that 1 US dollar = 3.6725 AED, then this is something that you could convert in the query by multiplying any USD values by 3.6725 and then adding them to any AED transactions the member has made. Have you accepted any other types of currency? The entire address is stored as a JSON string within the database in a single column. If there's a field missing, it means that the piece missing wasn't stored. Do you have examples? I'm not sure what you're showing in your screenshot (perhaps that the first row doesn't have a state of "Dubai"? What is the full value of nexus_customer_address.address for the member in question (where primary_billing = 1)? This would be a very different query, as you'd have to look at the transaction/invoice level information to filter by date. This solution looks at the aggregate value stored by Commerce for all time. Assuming we'd want to remove refunds, that the AED calculation above is true, and that you've only ever had transactions in USD or AED, this might get you what you want for the past two years: SELECT cm.`email` AS `email` ,cpc.`field_15` AS `phone` ,nc.`cm_first_name` AS `fn` ,nc.`cm_last_name` AS `ln` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.postalCode')) AS `zip` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.city')) AS `ct` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.region')) AS `st` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.country')) AS `country` ,cpc.`field_5` AS `gen` /* * This factors in only the stored amount value for transactions of type "okay" and "rfnd" in USD * or AED currencies. Any other transactions in any other currencies are ignored, as are partial refund values, etc. */ ,ROUND(`aggregate_transactions`.`usd_and_aed_transactions_only_sum`,2) AS `value` FROM `core_members` cm INNER JOIN `nexus_customers` nc ON nc.`member_id` = cm.`member_id` INNER JOIN ( SELECT t_member ,SUM( CASE /* Convert AED to USD at fixed rate of 1 USD to 3.6725 AED */ WHEN `t_currency` = 'USD' THEN `signed_amount` * 3.6725 WHEN `t_currency` = 'AED' THEN `signed_amount` END ) AS `usd_and_aed_transactions_only_sum` FROM ( SELECT `t_member` ,CASE /* negative amount for refunds */ WHEN `t_status` = 'rfnd' THEN -1 * `t_amount` /* okay transaction */ WHEN `t_status` = 'okay' THEN `t_amount` ELSE NULL END AS `signed_amount` ,`t_currency` FROM `nexus_transactions` WHERE /* Last two years from execution only */ `t_date` >= UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 2 YEAR)) /* Transcations in currencies other than USD and AED are out of scope */ AND `t_currency` IN ('USD','AED') ) AS `transactions` GROUP BY `t_member` ) AS `aggregate_transactions` ON `aggregate_transactions`.`t_member` = cm.`member_id` LEFT JOIN `nexus_customer_addresses` nca ON nca.`member` = cm.`member_id` /* Use primary billing address */ AND nca.`primary_billing` = 1 LEFT JOIN `core_pfields_content` cpc ON cpc.`member_id` = cm.`member_id`

This is probably an unrelated issue then. You might want to open a support request.

This is Cloudflare. Your web site is taking too long to respond to requests, and Cloudflare shows that error after 100 seconds by default. https://support.cloudflare.com/hc/en-us/articles/115003011431-Troubleshooting-Cloudflare-5XX-errors You can turn off the setting to allow for expanded forum views (and get rid of this problem @TSP is highlighting here) by going to ACP > Community > Forums > Settings and make the following setting changes: Default topic list view: Condensed view Members can choose? (Choose whether you wish to allow your members to toggle between the views. When set to 'no', members will always use the default topic list view.😞 No

Welcome, time traveler. Here's a solution:

That'll do it! This should get you what you're looking for. I've included spend_currency as IPS aggregates total customer spend by currency type. If you only accept one type of currency, you can remove this column from the query. Otherwise, you'll probably want to do some sort of currency conversion, so you're comparing apples to apples. Here you go! If you find value and want to donate to a worthy cause, feel free to reach out via PM. SELECT cm.`email` AS `email` ,cpc.`field_15` AS `phone` ,nc.`cm_first_name` AS `fn` ,nc.`cm_last_name` AS `ln` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.postalCode')) AS `zip` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.city')) AS `ct` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.region')) AS `st` ,JSON_UNQUOTE(JSON_EXTRACT(nca.`address`, '$.country')) AS `country` ,cpc.`field_5` AS `gen` ,ncs.`spend_amount` AS `value` /* * Spend is aggregated per currency type. If you only * accept payments via one type of currency, you can * leave off the following column */ ,ncs.`spend_currency` FROM `core_members` cm INNER JOIN `nexus_customers` nc ON nc.`member_id` = cm.`member_id` INNER JOIN `nexus_customer_spend` ncs ON ncs.`spend_member_id` = cm.`member_id` LEFT JOIN `nexus_customer_addresses` nca ON nca.`member` = cm.`member_id` /* Use primary billing address */ AND nca.`primary_billing` = 1 LEFT JOIN `core_pfields_content` cpc ON cpc.`member_id` = cm.`member_id`

We're looking for the column name for each (something like field_1 or field_4, etc.).

We'll need to figure out which field they are in the table core_pfields_content. There should be one there that represents mobile phone and one that represents gender.

This was a fun little exercise. However, some questions: Customers have shipping and billing addresses. Which values are you interested in? Where is gender? Where is "mobile phone"? (Customers have a single phone entry)

What classifies a member as a paid member only? You want results for members that have spent anything ever?

I'm having total deja vu!

No worries. UX improvement would be nice. No need to implement any of these things if you don't find them useful, though I think they have universal benefits. If it's a single bookshelf, it should be a toggle (i.e. "Turn off/on my personal bookshelf"). Right now there's a "Add bookshelf" button, giving you the ability to choose a name, which suggests you'll be able to add multiple. Confusing for the end user.

For a use-case, consider Goodreads, a web site that works similarly to how this might. In their implementation, users can create multiple bookshelves that they give different names. This is a popular site for avid readers. One member's example: It essentially enables additional curation of the collection. You might be interested in featuring a particular collection (bookshelf) by one member. They additionally have lists, which are another way to collect books. A book might be in multiple lists, and lists can be voted on, reviewed, etc. Things like the Best Books for New Moms, or Must-Read Historical Fiction, etc. www.goodreads.com/list This would seem like a good concept to take to your other similar apps (music, movies, etc.).

We continue testing this. How does one create multiple bookshelves? Is it just one bookshelf per member? If so, would you consider adding multiple bookshelves per member? Also, depending on how this is set up, a book could, in theory, have multiple categories (non-fiction, mystery, politics, etc.). We were hoping to work around this by using bookshelves instead, yet it's not apparent how to add additional bookshelves.

I'd say that there is likely a 50/50 split between spam registrants that are simply trying to create a profile with links in an attempt at SEO or link count nonsense as compared to those that actually attempt to post. There is a need to be able to moderate links in profile fields. We turn off profile viewing to guests as one way to mitigate against this, but it's less than ideal.

The ability to edit in the approval queue would be wonderful.

It's possible that IPS could add such an ETL feature, that would allow you to map columns. This does not exist however. Good feature suggestion.

To answer your question, yes it's possible. It would require customization though.