284 lines
9.6 KiB
PHP
284 lines
9.6 KiB
PHP
<?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;
|
|
|
|
?>
|