Guest Posted May 4, 2003 Share Posted May 4, 2003 I'm working on a possible contribution(I'm not sure if this would be useful to anyone else), and I need help. I want to have a report that creates a sold product list (exactly like the current report "products purchased", but with a twist. the first link would be exactly as the current report, listing all the sold products (in my case they are referred to as events), after opening the page stats_products_purchase, it displays clickable links for each of the products purchased, which is perfect. Now what i need is to change the "action". I would like a new page, based on customer.php but to display the customer's that bought (registered) the product clicked. I've been reading fast and furiously and have done the basic mods, so that the new report shows up in the list box, and it takes me to a new file that I've renamed, events_listings.php (formerly stats_products_purchased). Now I need to futher modify that page so that I can get an ordered customer list per product sold. I hope I haven't lost anyone. I hope there is a charitable person out there that is willing to help me. I just can't quite grasp, how to write the code. I'll keep reading, but please someone help me with this. :( :? Yours in gratitude Ruth in AZ Quote Link to comment Share on other sites More sharing options...
Guest Posted May 4, 2003 Share Posted May 4, 2003 Folks, I'm still working on this, but i had a thought (still not sure how to write it but I may be getting warm), how would I go about initiating a "search" function based on the event ID that I'm clicking similiar to that done on "Customers Order Totals", that would pull up "all who purchase products when clicked. That would display the list of customers utilizing the customer.php format, which is what I need so that the email function and customer detail are available. I think I see the light, I just hope its not the train. I guess I really need someone super sharp on writing code for OSC, I'm just not it at this time. Again, please help. Ruth in AZ Quote Link to comment Share on other sites More sharing options...
orchard Posted May 5, 2003 Share Posted May 5, 2003 If I understand what you are trying to do correctly, you need to play around with the where section of this query to limit it to the product_id you want. $customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id and o.orders_id = op.orders_id group by c.customers_firstname, c.customers_lastname order by ordersum DESC"; Maybe you need to add op.product_id = xxx to the where section. Quote In olden times the men were made of iron and the ships were made of wood; now it's the other way around. :wink: Link to comment Share on other sites More sharing options...
Guest Posted May 5, 2003 Share Posted May 5, 2003 To be sure I understand, as I've often said, I'm a "real" newbie to OSC, in adding to the "where" statement, will the "xxx" pull the list from the event just clicked or should I be putting a specific value in place of the "xxx". If a specific value - what should that be? I'm sorry if I seem dense... :oops: By the way, thanks for helping me. I was getting lonely typing to myself. :) Ruth in AZ Quote Link to comment Share on other sites More sharing options...
Guest Posted May 5, 2003 Share Posted May 5, 2003 :oops: One more question, are we taking this query and replacing it with the raw query on line 447 of customer.php and then rename to a new file and setup the proper referrers? Quote Link to comment Share on other sites More sharing options...
Guest Posted May 5, 2003 Share Posted May 5, 2003 Sorry, :oops: Its been a long day, I was looking at the wrong file, I mean stats_customer.php and the line then would be 59.... Quote Link to comment Share on other sites More sharing options...
orchard Posted May 5, 2003 Share Posted May 5, 2003 Yes, I think line 59 is where I got that line. I was thinking the xxx would be a variable you had to tell which product you are making the report about. Maybe it would come from the arguments in the URL. Quote In olden times the men were made of iron and the ships were made of wood; now it's the other way around. :wink: Link to comment Share on other sites More sharing options...
Guest Posted May 5, 2003 Share Posted May 5, 2003 That was a big part of my confusion. in the stats_products_purchased.php that I'm using more or less as template, in line 67 is the following: $products['products_id'] . '&origin=' . FILENAME_STATS_PRODUCTS_PURCHASED If I read that correctly, that is the line of code providing "which" product. You can see possiblly why I'm a bit lost here. So if I follow you, make the following changes: Modify stats_products_purchase (then rename, which I've already done to events_listings.php) this is from line 67 <tr class="dataTableRow" onmouseover="this.className='dataTableRowOver';this.style.cursor='hand'" onmouseout="this.className='dataTableRow'" onclick="document.location.href='<?php echo tep_href_link(FILENAME_CATEGORIES, 'action=new_product_preview&read=only, 'NONSSL'); ?>'"> The "red" portion would be changed to (FILENAME_NEW_FILE_NAME,'ACTION=new_file2_name=..... The modify "stats_customers.php to the following (also rename) line 59 as follows: $customers_query_raw = "select c.customers_firstname, c.customers_lastname, sum(op.products_quantity * op.final_price) as ordersum from " . TABLE_CUSTOMERS . " c, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where c.customers_id = o.customers_id, o.orders_id = op.orders_id and op.product_id = xxx, group by c.customers_firstname, c.customers_lastname order by ordersum DESC"; Then modify the applicable english files and define the new file names to application_top.php Please advise errors. I'm still not sure about the"XXX" and what value should be there. Thanks again for trying to help. Ruth in AZ Quote Link to comment Share on other sites More sharing options...
orchard Posted May 5, 2003 Share Posted May 5, 2003 If I understand correctly, events_listings.php knows the product_id because it's $products['product_id'] and is being passed in the URL as the pID value. I think this means that xxx can be replaced by $HTTP_GET_VARS['pID']. I'm not sure whether "and op.product_id = xxx" is the correct syntax or ", op.product_id = xxx" is. Quote In olden times the men were made of iron and the ships were made of wood; now it's the other way around. :wink: Link to comment Share on other sites More sharing options...
Guest Posted May 5, 2003 Share Posted May 5, 2003 Looking at the query there is a call for the total purchased, how can I without breaking the syntax only display the customer list. I'll play with it, but my confidence factor is low that I will do it correctly so I thought I would ask in advance. I had to leave my desk for about a half hour so I'm back doing the final edit on the files before I test it. I'll let you know how it goes. Ruth Quote Link to comment Share on other sites More sharing options...
orchard Posted May 5, 2003 Share Posted May 5, 2003 I think you comment out line 55 <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_TOTAL_PURCHASED; ?> </td> and line 76 <td class="dataTableContent" align="right"><?php echo $currencies->format($customers['ordersum']); ?> </td> There may be some colspan=3 that you need to edit too since now the table will only have 2 columns. Quote In olden times the men were made of iron and the ships were made of wood; now it's the other way around. :wink: Link to comment Share on other sites More sharing options...
Guest Posted May 5, 2003 Share Posted May 5, 2003 I got a parse error on this line (this is the events_listing.php - modification of stats_products_purchased.php - line 67) I've indicated where I think the error is, as I became unsure of the first portion of that line. Once again a bit lost. It calls for "Categories, and then the static page in the original, I wasn't sure if categories needed to be modified or ????? <tr class="dataTableRow" onmouseover="this.className='dataTableRowOver';this.style.cursor='hand'" onmouseout="this.className='dataTableRow'" onclick="document.location.href='<?php echo tep_href_link(FILENAME_CATEGORIES, action=stats_events&pID=, 'NONSSL'); ?>'"> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.