Post by itrends » Wed Oct 17, 2012 4:27 am

Hi all,

anyone know what SQL code or module I might be able to run in order to spit out a CSV with the following data?

Notes: ONLY ACTIVE PRODUCTS, if the product has options then each option should be on its own row if possible

Manufacturer Name
Product Price
Quantity
Image URL
Attribute fields
Category(s)


So you can imagine why I am highly stuck on how I might go about this. :)

Any help is greatly appreciated as always :)

Active Member

Posts

Joined
Tue Jul 14, 2009 7:54 pm

Post by Avvici » Wed Oct 17, 2012 6:17 am

Take this for example in your control you would have:
(using a form you would just contact the function csvCustomExport from the HTML

Code: Select all

public function csvCustomExport() {
        if ($this->request->server['REQUEST_METHOD'] == 'POST' && $this->validate()) {
            
            $ReflectionResponse = new ReflectionClass($this->response);
            if ($ReflectionResponse->getMethod('addheader')->getNumberOfParameters() == 2) {
                $this->response->addheader('Pragma', 'public');
                $this->response->addheader('Expires', '0');
                $this->response->addheader('Content-Description', 'File Transfer');
                $this->response->addheader("Content-type', 'text/octect-stream");
                $this->response->addheader("Content-Disposition', 'attachment;filename=stock_export.csv");
                $this->response->addheader('Content-Transfer-Encoding', 'binary');
                $this->response->addheader('Cache-Control', 'must-revalidate, post-check=0,pre-check=0');
            } else {
                $this->response->addheader('Pragma: public');
                $this->response->addheader('Expires: 0');
                $this->response->addheader('Content-Description: File Transfer');
                $this->response->addheader("Content-type:text/octect-stream");
                $this->response->addheader("Content-Disposition:attachment;filename=stock_export.csv");
                $this->response->addheader('Content-Transfer-Encoding: binary');
                $this->response->addheader('Cache-Control: must-revalidate, post-check=0,pre-check=0');
            }

            $this->load->model('report/export');

            $this->response->setOutput($this->model_report_export->csvExport("product",$this->request->post['below'],$this->request->post['above']));
        } else {
            return $this->forward('error/permission');
        }
    }
And in your model you would have:

Code: Select all

public function csvExport($table,$below,$above) {
        //create string of fields from settings
     
        
        $field_array = '';
        if($this->config->get('name')){
    $field_array .= $this->config->get('name').",";
        }
        if($this->config->get('location')){
    $field_array .= $this->config->get('location').",";
        }
        if($this->config->get('upc')){
    $field_array .= $this->config->get('upc').",";
        }
        
    
        //remove last comma
        $fields = substr($field_array, 0, -1);    
           
        $output     = '';
      
        if($above == ""){
        $query         = "SELECT $fields  FROM `" . DB_PREFIX . $table ."`  WHERE quantity <= '".(int)$below."'";
        
        }else{
        $query         = "SELECT $fields  FROM `" . DB_PREFIX . $table . "` WHERE quantity >= '".(int)$above."'"; 
        
        }
        //main query
        $result     = $this->db->query($query);
        $columns     = array_keys($result->row);
        
        $csv_terminated = "\n";
        $csv_separator = ",";
        $csv_enclosed = '"';
        $csv_escaped = "\\"; //linux
        $csv_escaped = '"';
       
       
        // Header Row
         if($this->config->get('qexport_name')){
         $output .=  stripslashes(implode('","' ,$columns))  . '","' .$name_field. "\"\n";
         }else{
         
         $output .=  stripslashes(implode('","' ,$columns))  .  "\"\n";
    }

         
        foreach ($result->rows as $row) {
             if($this->config->get('qexport_name')){
             $query_pname = "SELECT $name_field FROM `" . $name_table ."`  WHERE product_id = '".$row['product_id']."'";
             $result2     = $this->db->query($query_pname);
            $product_name = $result2->row['name'];
            
             }

            $schema_insert = '';
             if($this->config->get('qexport_name')){
       
        $fields_cnt = count($row) + 1;
        }else{
            $fields_cnt = count($row);
        }
            
            
            foreach ($row as $k => $v) {
                if ($row[$k] == '0' || $row[$k] != '') {
                    if ($csv_enclosed == '') {
                        $schema_insert .= $row[$k];
                    } else {
                        $row[$k] = str_replace(array("\r","\n","\t"), "", $row[$k]);
                        $row[$k] = html_entity_decode($row[$k], ENT_COMPAT, "utf-8");
                        $schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$k]) . $csv_enclosed;
                    }
                } else {
                    $schema_insert .= '';
                }

                if ($k < $fields_cnt - 1) {
                    $schema_insert .= $csv_separator;
                }
            }
             if($this->config->get('qexport_name')){
            $output .= $schema_insert.$product_name;
             }else{
                 $output .= $schema_insert; 
             }
            $output .= $csv_terminated;

        }
 
      return $output; 

    }


This is a generic example of how to output a CSV in Open Cart. You will need to suit the MODEL and CONTROL to your needs.

User avatar
Expert Member

Posts

Joined
Tue Apr 05, 2011 12:09 pm
Location - Asheville, NC

Post by itrends » Wed Oct 17, 2012 6:19 am

Thank you. That is a wonderful start.

I'm not entirely sure how to evolve the model and link everything up as needed but will see if I can work it out.

Thank you again :)

Active Member

Posts

Joined
Tue Jul 14, 2009 7:54 pm

Post by Avvici » Wed Oct 17, 2012 6:24 am

Np, just fill in the parts that you need to formulate the column names and the data that will go in the columns :) I am PM'ing you an Extension so you can see it work in action. If you install it, use it, and look at the code it will help you understand what is written above.

User avatar
Expert Member

Posts

Joined
Tue Apr 05, 2011 12:09 pm
Location - Asheville, NC
Who is online

Users browsing this forum: Bing [Bot] and 61 guests