<?php
function GetCountryName($name)
{
  global $countriesIndex;
  $countryName;
  /*
  $parts = explode(' ',$name);
  $parts[2] = preg_replace("/\d+/","",$parts[2]);
  $parts[3] = preg_replace("/\d+/","",$parts[3]);
  if(array_key_exists(substr($name,0,3), $countriesIndex))
    $countryName = $countriesIndex[substr($name,0,3)];
  else if(array_key_exists($parts[2], $countriesIndex))
    $countryName = $countriesIndex[$parts[2]];
  else if(array_key_exists($parts[3], $countriesIndex))
    $countryName = $countriesIndex[$parts[3]];
  else if(stristr($name,'award'))
    $countryName = "Awards Ceremonies";
  */
  //$countryName = "United States of America";
  
  # for the torino 2006 pictures (had full country name in file name)
  // preg_match("/^(T|t)orino2006 (\w+)\s/",$name,$matches);
  // $countryName = $matches[1];
  
  # for the winterberg 2008 photos
  # sample photo names:
  # WC Winterberg WB Nov 08-USA-Pac12
  # WC Winterberg 4M Nov 08-Awards2
  # WC Winterberg 4M Nov 08-NED01
  $parts = explode(' ', $name);
  $abbr = $parts[5];
  $abbr = preg_replace(array('/\d+/', '/\.JPG/i'), array("",""), $abbr);
  
  $countryName = $countriesIndex[$abbr];
  
  if(empty($countryName))
  {
    if(preg_match('/award/i', $name) != 0)
    {
      $countryName = $countriesIndex['awa'];
    }
  }
  
  return $countryname;
}

$downloadDir = "/home/users/web/b2373/pow.fti/data/bobsleighphotos_downloads";
$optionName = 'Licensing Options';

$optionValueDollars = array(
  'Personal use only' => '0',
  'Commercial use level 1' => '40',
  'Commercial use level 2' => '65',
  'Commercial use level 3' => '90',
  'Commercial use level 4' => '115',
  'Commercial use level 5' => '140'
);

# array corresponding country abbreviations to full country category names
$countriesIndex = array(
	'AUS' => 'Australia',
	'AUT' => 'Austria',
	'Awa' => 'Awards Ceremonies',
	'awa' => 'Awards Ceremonies',
	'BER' => 'Bermuda',
	'BIG' => 'Bosnia',
	'BUL' => 'Bulgaria',
	'BRA' => 'Brazil',
	'CAN' => 'Canada',
	'CZE' => 'Czech Republic',
	'CRO' => 'Croatia',
	'ESP' => 'Spain',
	'FIN' => 'Finland',
	'FRA' => 'France',
	'GER' => 'Germany',
	'GBR' => 'Great Britain',
	'GRE' => 'Greece',
	'HUN' => 'Hungary',
	'IRE' => 'Ireland',
	'IRL' => 'Ireland',
	'ITA' => 'Italy',
	'JPN' => 'Japan',
	'KOR' => 'South Korea',
	'LAT' => 'Latvia',
	'MIS' => 'Miscellaneous',
	'MON' => 'Monaco',
	'NED' => 'Netherlands',
	'NZL' => 'New Zealand',
	'NOR' => 'Norway',
	'POL' => 'Poland',
	'ROU' => 'Romania',
	'ROM' => 'Romania',
	'RUS' => 'Russia',
	'SLO' => 'Slovenia',
	'SRB' => 'Serbia',
	'SVK' => 'Slovakia',
	'SWE' => 'Sweden',
	'SUI' => 'Switzerland',
	'ISV' => 'US Virgin Islands',
	'USA' => 'United States of America',
);

# connect to the database and select the database we're going to be using
if(!mysql_connect("fti.powwebmysql.com",'cubecart_user','cubecart_friend3'))
  print "Could not connect to db server: ".mysql_error();
if(!mysql_select_db("cc_bobsleighphotos"))
  print "Could not select db: ".mysql_error();

# get the id of the licensing option name, named above that we want to add
# to each product here.  Then, we get all the option values associated
# with this option name
$q = "SELECT option_id
        FROM cc_CubeCart_options_top
        WHERE option_name = '$optionName'";
$optionNames = mysql_query($q);
# there should only be one result, if not, well, we just take the first one then
$optionNameId = mysql_fetch_assoc($optionNames);
$optionNameId = $optionNameId['option_id'];

# now we get all the option values associated with this option name
$q = "SELECT *
        FROM cc_CubeCart_options_mid
        WHERE father_id = $optionNameId";
$optionValuesResult = mysql_query($q);
$optionValues = array();
# collect the option value ids into an array for later use
while($optionValue = mysql_fetch_assoc($optionValuesResult))
  $optionValues[] = $optionValue;

# select the products that are not set as digital downloads yet
# all products on the site are digital downloads.  So, these selected
# products should be the ones just uploaded to the catalogue
$q = "SELECT *
        FROM cc_CubeCart_inventory
        WHERE digital = 0";
# select ALL photos
// $q = "SELECT *
//         FROM cc_CubeCart_inventory";
# selects photos from the bodine challenge
// $q = "SELECT * 
//         FROM cc_CubeCart_inventory
//         WHERE productCode LIKE '%bodine%'";
# selects photos from the koenigsee 2007 championship
// $q = "SELECT * 
//         FROM cc_CubeCart_inventory
//         WHERE productCode LIKE '%koenigsee%'";
$photos = mysql_query($q);

# step through each photo that has not yet been marked as a digital download
# and perform the necessary querys to mark it as a digital download and
# designate the location of the downloadable file
while($row = mysql_fetch_assoc($photos))
{
  // # convert the download file location to john's site from mine
  // $oldPath = "/home/users/web/b2932/pow.bramey/cubecart4/downloads";
  // $newPath = "/home/users/web/b2373/pow.fti/data/bobsleighphotos_downloads";
  // $newLocation = str_replace($oldPath,$newPath,$row['digitalDir']);
  // $q = "UPDATE cc_CubeCart_inventory
  //         SET digitalDir = '$newLocation'
  //         WHERE productId = {$row['productId']}";
  // mysql_query($q);
  // if($err = mysql_error())
  //   print "Could not update {$row['name']}: $err<br />";
  // 
  // continue;
    
  $addDigital = true;
  # add this product to the correct country category
  # the first three letters of the product name are the country abbreviation
  # used by John Nielsen to designate which country this photo belongs to
  $countryName = GetCountryName($row['name']);
  
  $q = "SELECT *
          FROM cc_CubeCart_category
          WHERE cat_name = '$countryName'";
  $countries = mysql_query($q);
  
  # check for mysql errors
  if($err = mysql_error())
    print "Couldn't find country $countryName in database for {$row['name']}: $err<br />";
  # check if the country was found in the data base
  else if(($numRows = mysql_num_rows($countries)) !== 1)
  {
    if($numRows < 1)
      print "No category found for $countryName for {$row['name']}. $numRows rows were found.<br />";
    else
      print "Multiple category matches found for $countryName for {$row['name']}.<br />";
      
    $addDigital = false;
  }
  # there should only be one country by the name we're looking for
  # so we can just fetch the first row from the result set
  else
  {
    $country = mysql_fetch_assoc($countries);
    
    # check to see if this product is already associated with this country
    $q = "SELECT *
            FROM cc_CubeCart_cats_idx
            WHERE cat_id = {$country['cat_id']}
              AND productId = {$row['productId']}";
    $catAssociations = mysql_query($q);
    
    # if the number of rows is 0, then we can go ahead and add the product-
    # category association, if it is great than zero, then we don't want to 
    # add duplicates
    if(mysql_num_rows($catAssociations) > 0)
      print "Skipping $countryName for {$row['name']}: association already in DB.<br />";
    else
    {
      # now we insert a new row into the cats_idx table to relate this product
      # to the countries
      $q = "INSERT INTO cc_CubeCart_cats_idx
              (cat_id, productId)
              VALUES({$country['cat_id']},{$row['productId']})";
      mysql_query($q);
  
      if($err = mysql_error())
        print "Could not insert new cat_idx row for {$row['name']}: $err <br />";
    }
  }
  
  # normally, we don't want to add the digital download information unless
  # we found a category in the above code.  But, we can pass a param that 
  # forces this adding each time no matter what--this is exactly what is needed
  # in some cases
  if($addDigital || $_GET['forceDigital'])
  {
    # set 'digital' to 1 to mark this product as a digital (downloadable) product
    # also create the path to the downloadable file--this path is relative
    # to the downloads dir outside of htdocs folder.  The image path relative
    # to the uploads folder is the same relative to the downloads folder
    # so we use it
    $q = "UPDATE cc_CubeCart_inventory
            SET digital = 1,
                digitalDir = '$downloadDir/{$row['image']}'
            WHERE productId = {$row['productId']}";
    mysql_query($q);

    if($err = mysql_error())
      print "Could not update product {$row['name']} with digital info: $err <br />";
  }
  
  # now add the option values for this product
  foreach($optionValues as $optionValue)
  {
    # if this option value/name pair already exists for this product,
    # don't try to add it again
    $q = "SELECT *
            FROM cc_CubeCart_options_bot
            WHERE product = {$row['productId']}
              AND value_id = {$optionValue['value_id']}
              AND option_id = $optionNameId";
    $presentValues = mysql_query($q);
    if($err = mysql_error())
    {
      print "Query error when looking for option values: $err<br />";
      continue;
    }
    else if(mysql_num_rows($presentValues) > 0)
    {
      print "Skipping adding options to {$row['name']}, some already exist.<br />";
      continue;
    }
    
    # if we did not continue above, then try to insert the option now
    $valueDollars = $optionValueDollars[$optionValue['value_name']];
    $q = "INSERT INTO cc_CubeCart_options_bot
            (product,option_id,value_id,option_price,option_symbol)
            VALUES({$row['productId']},$optionNameId,{$optionValue['value_id']},$valueDollars,'+')";
    mysql_query($q);
    if($err = mysql_error())
      print "Could not insert option value $valueId for option name $optionNameId
        for {$row['name']}: $err<br />";
  }
}
$rowCount = mysql_num_rows($photos);
if($rowCount < 1)
  print "No work done, no products selected.";
else
  print "Done. Yay. $rowCount products examined and possibly updated.";
exit;

?>