Score:0

Report of coupon and users

ro flag

I have users who are partners and users who are clients. When clients submit an order, they use a custom coupon code associated with each partner. (Each partner has a Mycodes field set within their profile.)

Previously, at the end of the month, we would download the sales into an Excel sheet and calculate the commissions of each partner using the partner's coupon codes.

Now I want to do this on the website using a view. I created a view for the coupons but I cannot compare the coupon code against the partner's Mycodes field. I have written the sql query in phpMyAdmin (one line with two JOIN), but I can not put that into views and filter that based on current userID:

SELECT
  uc_order_line_items.order_id AS OrderID,
  uc_orders.order_status AS Currently,
  uc_orders.created AS ThisDate,
  uc_orders.delivery_first_name AS Name,
  uc_orders.delivery_last_name AS Surname,
  uc_orders.delivery_phone AS Phone,
  uc_orders.primary_email AS Email,
  field_data_field_mycodes.entity_id AS UserID,
  uc_orders.order_total AS Total
FROM
  uc_order_line_items
JOIN
  field_data_field_mycodes
ON
  uc_order_line_items.title = field_data_field_mycodes.field_mycodes_value
JOIN
  uc_orders
ON
  uc_orders.order_id = uc_order_line_items.order_id
WHERE
  field_data_field_mycodes.entity_id=
Chris Happy avatar
cg flag
Just to clarify, what is the field type of "Mycodes"? Is it a plain text or an entity reference?
user3532287 avatar
ro flag
it can be anything that can link these two! I am open to any idea
Chris Happy avatar
cg flag
Okay, there's a few different ways to solve this problem. Looking at your SQL query, I'm confused at what you're looking for.
Chris Happy avatar
cg flag
Are you wanting a View that calculates the commission each partner gets? Or just to display the orders for each partner? Or all of the orders, grouped by each partner?
user3532287 avatar
ro flag
Just the list of the orders of each partner would be enough. This is what I can not do in Views: where current user's mycodes = uc_order_line_items.title
Score:0
ro flag

Thanks to Chris I tried that with 3 different base tables, but none worked. This might not be the answer but it is for the people who might read this article as an option. After spending more than 2 days, I decided to go with the craziest approach yet "doable": I created a login table in the database, wrote one login page and one report page in PHP (less than 650 lines of codes with all those sort and filter), and I was done in about 5 hours. Link to that login went into Drupal, and that was it. Drupal is a very great tool but I guess Views needs to add a free-hand SQL query, and, also a free-hand javascript insertion per page. these could really save programmers a lot saving hours of work...

I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.