CRELoaded osCommerce PHP Script for SQL Query/Import Needed

Public Clarification Board

Sharing of contact details is prohibited by the Terms of Service.

Post Message

• 

  leenegri

  Also, we need to have a way in the admin to choose which customers can access this export script and make it hidden or unaccessable to people who don't have access. A checkbox in the "edit customer details" page in the admin would work if that can be done.

  over 1 year ago

• 

  leenegri

  Here's an example of an export script someone wrote to work with a .csv file. If we can modify this to work how we need it to, that'd be great.
  
  <?php
  
  // name this file get_feed.php or whatever you want, just change it below also.
  /* use the following cron job to execute this as often as you need.
  cd /absolute/path/to/catalog/your_admin/
  php get_feed.php
  */
  
  // set options
  $use_map = 0; // set to 1 to enable map
  $use_markup = 0; //set to 1 to enable markup calculations
  $remote_file = 'products.csv' // name of products file to get from vendor
  $local_file = '/absolute/path/to/catalog/products.csv'; // Change to absolute path to a local file name
  $ftp_server = 'ftp://domain.com'; // Change to fully qualified domain name
  $ftp_user_name = 'your_user_name'; // Change to your user name
  $ftp_user_passs = 'password'; // Change to your password
  $remote_dir = '' ; // Change to something other than entry directory
  
  // open some file to write to
  $handle = fopen($local_file, 'w');
  
  // set up basic connection
  $conn_id = ftp_connect($ftp_server);
  
  // login with username and password
  $login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);
  
  // change directory if need to
  if (isset($remote_dir)) {
  ftp_chdir($conn_id, "somedir")
  }
  
  // try to download $remote_file and save it to $handle
  if (ftp_fget($conn_id, $handle, $remote_file, FTP_ASCII, 0)) {
  echo "successfully written to $local_file\n"; // may have to comment this out after testing or may get emailed to you by the cron process
  } else {
  echo "There was a problem while downloading $remote_file to $local_file\n"; // may have to comment this out after testing or may get emailed to you by the cron process
  }
  
  // close the connection and the file handler
  ftp_close($conn_id);
  fclose($handle);
  
  // end of php-ftp code
  
  
  
  // grab content os file into array
  $lines = file_get_contents($source);
  
  // let's get the database open now
  // have to include the configure file
  require('includes/configure.php');
  
  // include the database functions
  require('includes/functions/database.php');
  
  // make a connection to the database... now
  tep_db_connect() or die('Unable to connect to database server!');
  
  
  // loop through lines in file
  foreach($lines as $line) {
  
  $items = explode(",",$line); // change the , to whatever your field separator is. Use \t for tab delimited.
  $itemno = $items[0]; // change these numbers to match the order of data received in the file
  $qty = $items[1]; // 0 is the first element, 1 is the second element, etc.
  $cost = $items[2];
  // $another_field = $items[3]; // expand if you need to but you will have to add to the sql statement below
  
  
  if ($use_markup == true) { // requires a markup column in the categories table with markeup amount as decimal ( .20 as 20%)
  // get and set markup
  $sql = "select categories.markup from categories,products_to_categories,products where products.products_id = '" . $itemno . "' and products.products_id = products_to_categories.products_id and products_to_categories.categories_id = categories.categories_id";
  $result = mysql_query($sql);
  $markup = mysql_fetch_row($result);
  $price = (int)($cost * (1+$markup[0]))+.99; // go ahead and change the .99 to whatever you want
  $cost = $price;
  }
  
  // are we going to use MAP pricing? Requires a products_map column in the products table, with a value for map, NULL if no MAP
  if ($use_map == true) {
  $sql = "select products_map from products where products_id = '" . $itemno . "'";
  $result = mysql_query($sql);
  $map = mysql_fetch_row($result);
  if (!(is_null($map[0]))) {
  $cost = $map[0];
  }
  }
  
  // let's do it
  $sql = "update products set products_quantity = '" . $qty . "', products_price = '" . $cost . "' where products_id = '" . $itemno . "'";
  $result = mysql_query($sql);
  
  }
  
  tep_db_close();
  
  ?>

  over 1 year ago

Show more messages