germanyRates.php

<?php

error_reporting(E_ALL); 
ini_set("display_errors", 1);

require_once('../php/Commun/FUS_SCM_ini.php');
require_once('../php/Commun/FUS_SCM_database.php');
$o_db = db_connect($_db_host, $_db_user, $_db_passw);


$new_ppcs = array();             // PPCs from CSV
$existing_ppcs = array();        // PPCs from Database
$mailclasses = getMailClasses(); // MailClasses from database
$ppc_options = getPPCOptions();  // MailClass options from database
$brand_new_ppcs = array();       // Brand new PPCs
$changed = 0;                    // Number of PPCs that have changed

// Next MailClass ID to use for new mailclasses
$next_mailclass_id = getNextMailClassID();

$new_mailclasses = array();      // SQL for new MailClasses
$weight_range_updates = array(); // SQL for new WeightRanges
$mailclass_updates = array();    // SQL for updated MailClasses
$translation_updates = array();  // SQL for new Translations

// Load new rate information from CSV
if (($handle = fopen('MAS_GE1001A_PL22.csv', "r")) !== FALSE) 
{
  while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
  {
    // Remove keine if it's there
    $options = trim(str_replace('keine', '', $data[2]));
    
    $new_ppc = new PPC();
    $new_ppc->PP_Code = trim($data[0]);
    $new_ppc->MailClass = trim($data[1]);
    $new_ppc->PP_MC_MailClassID = array_search($new_ppc->MailClass, $mailclasses);
    $new_ppc->PP_Options = ($options) ? explode(" + ", $options) : array();
    $new_ppc->PP_MaxWeight = trim(str_replace(';', '', $data[4]));
    $new_ppcs[$new_ppc->PP_Code] = $new_ppc;
    
    // Maybe this PPC has an entirely new mailclass?
    if ($new_ppc->PP_MC_MailClassID === FALSE)
    {
      $insert_mailclass = "INSERT INTO \"MailClass\" (\"MC_MailClassID\", \"MC_Code\", \"MC_CA_CarrierID\", \"MC_IsActive\", \"MC_IsRetired\", \"MC_TranslationsTextKey\", \"MC_IsVATApplied\") VALUES ($next_mailclass_id, '$next_mailclass_id', 3, '1', '0', $next_mailclass_id, '0');\n";
      $insert_translation = "INSERT INTO \"Translations\" (\"TS_LA_Code\", \"TS_ModuleID\", \"TS_TextKey\", \"TS_TextValue\") VALUES ('DE', 'MAILCLASS', $next_mailclass_id, '{$new_ppc->MailClass}');\n";
      $new_ppc->PP_MC_MailClassID = $next_mailclass_id;
      $next_mailclass_id++;
      $new_mailclasses[] = $insert_mailclass;
      $translation_updates[] = $insert_translation;
    }
  }
  
  fclose($handle);
}

// Load existing rate information from database
$result = db_query($o_db, 'SELECT * FROM "PPCodes"');
while ($row = db_fetch_object($result))
{
  $ppc = new PPC($row);
  $existing_ppcs[$ppc->PP_Code] = $ppc;
}

// Build table of differences
$html = "<table>";
foreach ($new_ppcs as $new_ppc)
{
  // See if the PPC from the CSV is in the database
  if (isset($existing_ppcs[$new_ppc->PP_Code]))
  {
    $existing_ppc = $existing_ppcs[$new_ppc->PP_Code];
    
    $mailclass_color = $options_color = $weight_color = '';
    
    // See if anything changed
    if ($new_ppc->MailClass != $existing_ppc->MailClass)
    {
      // This PPC's mailclass is changing
      $sql = "UPDATE \"PPCodes\" SET \"PP_MC_MailClassID\" = '{$new_ppc->PP_MC_MailClassID}' WHERE \"PP_Code\" = {$new_ppc->PP_Code};\n";
      $mailclass_updates[] = $sql;
      $mailclass_color = ' bgcolor="yellow"';
    }
    
    if ($new_ppc->PP_Options != $existing_ppc->PP_Options)
    {
      $options_color = ' bgcolor="yellow"';
    }
    
    if ((int)$new_ppc->PP_MaxWeight != (int)$existing_ppc->PP_MaxWeight)
    {
      $weight_color = ' bgcolor="yellow"';
      
      if ($new_ppc->PP_MC_MailClassID)
      {
        $sql = "UPDATE \"WeightRanges\" SET \"WR_Max\" = '{$new_ppc->PP_MaxWeight}.0000' WHERE \"WR_MC_MailClassID\" = {$new_ppc->PP_MC_MailClassID};\n";
        if (!in_array($sql, $weight_range_updates))
          $weight_range_updates[] = $sql;
      }
    }
    
    if ($mailclass_color != '' or $options_color != '' or $weight_color != '')
    {
      $html .= "<tr>
                <td>{$new_ppc->PP_Code}</td>
                <td$mailclass_color>{$new_ppc->MailClass}</td>
                <td$options_color>".implode(' + ', $new_ppc->PP_Options)."</td>
                <td$weight_color>{$new_ppc->PP_MaxWeight}</td>
             </tr>";
      $changed++;
    }
  } 
  else 
  {
    $html .= "<tr bgcolor=\"green\">
                <td>{$new_ppc->PP_Code}</td>
                <td>{$new_ppc->MailClass}</td>
                <td>".implode(' + ', $new_ppc->PP_Options)."</td>
                <td>{$new_ppc->PP_MaxWeight}</td>
             </tr>";
    
    $new_ppc->SQL = "INSERT INTO \"PPCodes\" (\"PP_Code\", \"PP_MC_MailClassID\", \"PP_IsNeopostCode\") VALUES ('{$new_ppc->PP_Code}', '{$new_ppc->PP_MC_MailClassID}', '0');\n";
    
    $brand_new_ppcs[] = $new_ppc;
  }
}

echo "<table><tr><td># in spreadsheet: ".count($new_ppcs)."</td><td># in database: ".count($existing_ppcs)."</td></tr></table>";
echo "<table><tr><td bgcolor=\"green\">".count($brand_new_ppcs)."</td><td bgcolor=\"yellow\">$changed</td></tr></table>";
$html .= "</table>";

echo $html;

echo "<pre>";
echo sqlHeader("MailClass");
foreach ($new_mailclasses as $new_mailclass) echo $new_mailclass;
echo "\n";
foreach ($mailclass_updates as $mailclass_update) echo $mailclass_update;
echo sqlHeader("PPCodes");
foreach ($brand_new_ppcs as $brand_new_ppc) echo $brand_new_ppc->SQL;
echo sqlHeader("WeightRange");
foreach ($weight_range_updates as $weight_range_update) echo $weight_range_update;
echo sqlHeader("Translations");
foreach ($translation_updates as $translation_update) echo $translation_update;
echo "</pre>";

class PPC {
  
  public $PP_Code;
  public $SQL;
  public $PP_MC_MailClassID;
  public $PP_Options;
  public $PP_MaxWeight;
  public $MailClass;
  
  public function __construct($row = NULL)
  {
    global $o_db, $mailclasses, $ppc_options;
    
    if ($row !== NULL)
    {
      $this->PP_Code = $row->PP_Code;
      $this->PP_IsNeopostCode = $row->PP_IsNeopostCode;
      $this->PP_MC_MailClassID = $row->PP_MC_MailClassID;
      $this->PP_IsVATApplied = $row->PP_IsVATApplied;
      $this->PP_MinWeight = $row->PP_MinWeight;
      $this->PP_MaxWeight = (int)$row->PP_MaxWeight;
      $this->PP_AmountToSave = $row->PP_AmountToSave;
      
      $this->MailClass = $mailclasses[$this->PP_MC_MailClassID];
    
      if (!$this->PP_MaxWeight)
      {
        $this->PP_MaxWeight = 0;
        $result = db_query($o_db, "SELECT * FROM \"WeightRanges\" WHERE \"WR_MC_MailClassID\" = {$this->PP_MC_MailClassID}");
        if ($result === FALSE) die("SELECT * FROM \"WeightRanges\" WHERE \"WR_MC_MailClassID\" = {$this->PP_MC_MailClassID}");
        while ($row = db_fetch_object($result))
        {
          $this->PP_MaxWeight = (int)$row->WR_Max;
        }
      }
    }
    
    $this->PP_Options = array();
    $result = db_query($o_db, "SELECT * FROM \"PPCOptionsList\" WHERE \"PO_PP_Code\" = '{$this->PP_Code}'");
    while ($row = db_fetch_object($result))
    {
      $option = $ppc_options[$row->PO_MO_MailClassOptionID];
      if (!in_array($option, $this->PP_Options))
        $this->PP_Options[] = $option;
    }
  }
    
  public function __toString()
  {
    return "PPC = {$this->PP_Code}\n";
  }
}

function getMailClasses()
{
  global $o_db;
  $mailclasses = array();
  $result = db_query($o_db, "SELECT * FROM \"Translations\" WHERE \"TS_ModuleID\" = 'MAILCLASS'");
  while ($row = db_fetch_object($result))
    $mailclasses[$row->TS_TextKey] = $row->TS_TextValue;
  return $mailclasses;
}

function getPPCOptions()
{
  global $o_db;
  $options = array();
  $result = db_query($o_db, "SELECT * FROM \"Translations\" WHERE \"TS_ModuleID\" = 'MAILCLASSOPTION'");
  while ($row = db_fetch_object($result))
    $options[$row->TS_TextKey] = $row->TS_TextValue;
  return $options;
}

function getNextMailClassID()
{
  global $o_db;
  $result = db_query($o_db, 'SELECT MAX("MC_MailClassID") FROM "MailClass"');
  $row = db_fetch_object($result);
  return $row->MAX + 1;
}

function sqlHeader($text, $width = 78, $height = 5)
{
	$stars = str_repeat('*', $width);
	$string = "\n\n".str_repeat("/$stars/\n", floor(($height - 1) / 2));
	$string .= "/".str_pad("  $text  ", $width, '*', STR_PAD_BOTH)."/\n";
	$string .= str_repeat("/$stars/\n", floor($height / 2));
	return $string;
}

if (!function_exists("db_fetch_object"))
{
  function db_fetch_object($o_result)
  {
    global $sz_DatabaseEngine;
    
    switch ($sz_DatabaseEngine)
    {
      case "ibase":
        return ibase_fetch_object($o_result);
      case "mysql": 
        return mysql_fetch_object($o_result);
      case "pgsql": 
        return pg_fetch_object($o_result);
      case "sqlite":
        return sqlite_fetch_object($o_result);
      default:
        return FALSE;
    }
  }
}