Jump to content

[Solved] Is product_name anywhere besides the ps_order_detail table?

cokenour
 Share

Recommended Posts

cokenour Newbie

cokenour

Posted
Posted

I'm trying to create another custom report. This one will list all sales for a product specified by the user via selection from a dropdown menu.

The first thing I need is to query the db for all products and be able to display the product name.
I have found the name in ps_order_detail.product_name, but that doesn't suit my needs because this table only reflects products that have been ordered. I need to list ALL products.

Where can I find the product name? I've been digging through the entire db via phpMyAdmin and can't find anything except id_product.

Thanks,
Cokenour

Link to comment
Share on other sites
Snol Newbie

Snol

Posted
Posted

Hi cokenour,
is this soething that you will be able to aloow your customer to do with a script of some because I would be interested in being able to do this also if it is able to share.

Thanks
John

Link to comment
Share on other sites
cokenour Newbie

cokenour

Posted
  • Author
Posted

My application for it is this:
I'm using PrestaShop to sell seats for a continuing education center. Each class seat is sold just as any other product would be.
At the minimum they need a report that shows who's in each class (i.e., a report of who has purchased a given product).


My solution (or what I'm attempting...it's not done yet):
1. I created a "Reports" tab and added it to the admin area.
2. I then created a new user (employee in PrestaShop) and restricted their permissions to only view the newly created "Reports" tab.
3. I'm in the process of figuring out exactly what my db query needs to be.
4. Once the query is done I'll add the necessary code to the "Reports" tab so that users with permissions for that tab can run the report.

I don't see any reason why you couldn't use this for any client. I think it could be built into a full-fledged custom reports area.

I'll be happy to share what I have once it's working.

Link to comment
Share on other sites
Snol Newbie

Snol

Posted
Posted

Sounds like a good idea to me with other possibilities such as seeing who has purchased which product within an organisation etc, would be great to have you share this with the community. I will watch this space.

thanks
John

Link to comment
Share on other sites
cokenour Newbie

cokenour

Posted
  • Author
Posted

Ok, I've got the initial report working.

First, I created a new "Reports" tab. I explained that in this post. Before doing anything below, you need to finish that step first.

The first report just needed to show the name, phone(s), email, and purchase date for each customer who bought a given product.

1. I created a new file and called it AdminReportCode.php and placed it in the admin/tabs directory.
2. Then I put an include statement into my AdminReports.php file. This is the file that controls the display of my "Reports" tab. Because I had copied an existing tabs file to create AdminReports.php, the core code was still there. Look for the code below and insert the include statement. You can move it around. I put it there because it rendered the page the way I preferred.

/* ATTACH CCS TO DEFINE PRINTABLE AREA FOR THE REPORT - THIS REMOVES THE EXTRA PAGE ELEMENTS AND PRINTS JUST THE REPORT DATA */
<head>
<link rel="stylesheet" href="../themes/yourtheme/css/print.css" type="text/css" media="print" /> 
</head>
   public function display()
   {
       echo ''.$this->l('Reports').'';
       echo '
'.$this->l('You can run CCS reports from this page.').'';
       echo '
';
       echo $query;
/* INCLUDE STATEMENT */
       include(PS_ADMIN_DIR.'/tabs/AdminReportCode.php');
       echo '';        

   }



3. Then open AdminReportCode.php. Here's what I did:

>
<?php
/** CODE TO QUERY ALL PRODUCTS **/
   echo "
Product Report: Select One";
   include ‘config.php’;
   include ‘opendb.php’;
       $classlistquery="SELECT id_product, id_lang, name
               FROM ps_product_lang
               WHERE id_lang = '1'
               ORDER BY id_product";
       $result=mysql_query($classlistquery);
   include ‘closedb.php’;
/** END OF DROPDOWN QUERY **/


/** CODE TO CREATE A FORM WITH DROPDOWN BASED ON QUERY RESULTS **/
   echo "<form action='".$_SERVER['php_self']."' method='post'>";
   echo "\n";
   while ($data = mysql_fetch_array($result, MYSQL_ASSOC))
   {
   echo " {$data['name']}\n";
   }
   echo "\n";

   echo "<input type='submit' name='submitReport' value='Submit'>\n";
   echo "</form>\n";
/** END DROPDOWN **/


/** CODE TO RENDER THE REPORT ONCE THE USER CLICKS SUBMIT **/
   if (isset($_POST['submitReport']))
   {
   $entry = $_POST['class'] ;
   include ‘config.php’;
   include ‘opendb.php’;
   $classparticipantsquery="SELECT ps_orders.invoice_date, ps_customer.lastname, ps_customer.firstname, ps_customer.email, ps_order_detail.product_name, ps_product_lang.name, ps_address.phone, ps_address.phone_mobile
       FROM ps_orders
       JOIN ps_customer ON ps_orders.id_customer = ps_customer.id_customer
       JOIN ps_order_detail ON ps_order_detail.id_order = ps_orders.id_order
       JOIN ps_product_lang ON ps_order_detail.product_name = ps_product_lang.name
       JOIN ps_address ON ps_address.id_customer = ps_customer.id_customer
       WHERE ps_order_detail.product_name='$entry' AND ps_product_lang.id_lang = '1'
       ORDER BY ps_customer.lastname";
   $result = mysql_query($classparticipantsquery); 
   include ‘closedb.php’;

   $num=mysql_numrows($result);

/* Notice the class='print' - this class style is body {visibility:hidden;} .print {visibility:visible;}  */
   echo "</pre>
<table>";
   echo "


Product Sales Report For '$entry' Ordered By Last Name";
   echo "
Print Report";

   $i=0;
       while ($i < $num)
       {
       $last=mysql_result($result,$i,"lastname");
       $first=mysql_result($result,$i,"firstname");
       $email=mysql_result($result,$i,"email");
       $class=mysql_result($result,$i,"product_name");
       $date=mysql_result($result,$i,"invoice_date");
       $phone=mysql_result($result,$i,"phone");
       $mobile=mysql_result($result,$i,"phone_mobile");

   echo "$last, $first
Class: $class
E-mail: $email
Phone: $phone
Mobile Phone: $mobile
Signed Up: $date


";
       $i++;
       }
   echo "</table>";        <br>   exit;<br>   }<br>/** END ROSTER REPORT **/<br>?&gt



Again, this is a simple report. I've only been working with SQL for about a year and PHP for about a month. I did some ColdFusion stuff before that. So I'm sure there are better ways to do it, but this is what I came up wi

Hope it helps!

Link to comment
Share on other sites
 Share
Go to topic listing
×
×
  • Create New...