#!/usr/bin/php
<?php

/*
 * bomgen.php -- Automatically export a CSV BOM to a spreadsheet.
 *
 * Copyright (C) 2004 Hugo Villeneuve <hugo@hugovil.com>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 */

require_once 'Bomgen/conf.inc.php';
require_once 'Spreadsheet/Excel/Writer.php';
require_once 'Bomgen/functions.inc.php';
require_once 'Bomgen/refdes.inc.php';
require_once 'Bomgen/digikey.inc.php';

$debug = 0;

$digikey_bom = false;
$kits = 1;
$use_inventory = false;

define("DATA_ROW_START", 3);

define("PAPER_US_LETTER", 1);

/* width = 0 -> Ne pas afficher la colonne. */
$csv_infos =
  array(QTY_COL_NAME     => array("nom"   => "Qty",
                                  "width" => 5),
        DESIGNATOR_COL_NAME => array("nom"   => "Designator",
                                  "width" => 30),
        "Description"    => array("nom"   => "Description",
                                  "width" => 45),
        "Value"          => array("nom"   => "Value",
                                  "width" => 15),
        "Voltage"        => array("nom"   => "Voltage",
                                  "width" => 10),
        "Current"        => array("nom"   => "Current",
                                  "width" => 10),
        "Power"          => array("nom"   => "Power",
                                  "width" => 10),
        "Manufacturer"   => array("nom"   => "Manufacturer",
                                  "width" => 30),
        "Part Number"    => array("nom"   => "Manuf. P/N",
                                  "width" => 30),
        COMPANY_PN_COL_NAME => array("nom"   => "LSI P/N",
                                  "width" => 15),
        "PatternName"    => array("nom"   => "Footprint",
                                  "width" => 20),
        ASSEMBLY_COL_NAME => array("nom"   => "Assembly",
                                  "width" => 0),
        "UnitCost"       => array("nom"   => "Unit Cost",
                                  "width" => 7),
        "TotalCost"      => array("nom"   => "Total Cost", /* Colonne ajoutée par ce script. */
                                  "width" => 8),
        "Type"           => array("nom"   => "Type", /* Colonne ajoutée par ce script. */
                                  "width" => 0),
        );

$short_opts = "hci:kn:";

function usage()
{
  echo "Usage: bomgen.php [OPTIONS] [INPUT FILE]\n";
  echo "Generate an Excel BOM from a PCAD CSV-exported BOM.\n";
  echo "Output file has same name as input file, but with a .xls or .txt extension.\n";
  echo "\n";
  echo "Options:\n";
  echo "  -c  Add cost information to the BOM\n";
  echo "  -i  Specifiy inventory CSV file\n";
  echo "  -k  Create digikey BOM submission file (tab-separated fields)\n";
  echo "  -h  Display this help and exit\n";
  echo "  -n  Number of cards/kits for digikey BOM submission\n";
}

/*
 * Clé    = Nom de la colonne.
 * Valeur = Index de la colonne affichée dans la feuille Excel.
 */
$col_id_to_xls_num = array();

/*
 * Clé    = Nom de la colonne.
 * Valeur = Index de la colonne dans le fichier CSV.
 */
$col_id_to_num = array();

/*
 * Clé    = Index de la colonne.
 * Valeur = Nom de la colonne.
 */
$col_num_to_id = array();

$entete = array();

/*
 * value: clé
 * c:     Numéro de colonne dans le fichier CSV.
 * xls_c: Numéro de colonne dans Excel.
 */
function determine_col_index($value, $c, $xls_c)
{
  global $col_id_to_num, $col_num_to_id, $col_id_to_xls_num;

  $col_id_to_num[$value] = $c;
  $col_num_to_id[$c] = $value;
  $col_id_to_xls_num[$value] = $xls_c;
}

/* Conversion colonne numérique à lettre:
 *   0 -> A
 *   1 -> B
 *   ...
 */
function col_to_letter($c)
{
  $c = intval($c + 1); /* c part à 0. */

  if ($c <= 0)
    return '';

  $letter = '';

  while($c != 0) {
    $p = ($c - 1) % 26;
    $c = intval(($c - $p) / 26);
    $letter = chr(65 + $p) . $letter;
  }

  return $letter;
}

function xls_configure($workbook, $worksheet)
{
  global $col_id_to_xls_num, $csv_infos;

  /* Configration des couleurs de cellules. */
  xls_set_colors($workbook);

  foreach ($csv_infos as $key => $value) {
    $width = $value['width'];

    /* Ajustement de la largeur de chaque colonne. */
    if ($width) {
      /* S'assure que la colonne a été exportée dans le fichier CSV. */
      if (isset($col_id_to_xls_num[$key])) {
        $worksheet->setColumn($col_id_to_xls_num[$key], $col_id_to_xls_num[$key],
                              $width);
      } else {
        echo "Colonne '$key' manquante.\n";
      }
    }
  }

  $worksheet->setLandscape();
  $worksheet->setPaper(PAPER_US_LETTER);
}

if ($argc < 2) {
  echo "Missing source filename\n";
  usage();
  exit(1);
}

$add_cost = false;

$comp_total = 0;
$titre = "BOM";

$cg = new Console_Getopt();

/* Read the command line. */
$args = $cg->readPHPArgv();

/* Get the options. */
$ret = $cg->getopt($args, $short_opts);

/* Check for errors and die with an error message if there was a problem. */
if (PEAR::isError($ret)) {
  die ("Error in command line: " . $ret->getMessage() . "\n");
}

/* Now parse the options array. */
$opts = $ret[0];

if (sizeof($opts) > 0) {
    foreach ($opts as $o) {
        switch ($o[0]) {
        case 'c':
          $add_cost = true;
          break;
        case 'i':
          $use_inventory = true;
          $inventory_filename = $o[1];
          break;
        case 'k':
          $digikey_bom = true;
          break;
        case 'n':
          $kits = $o[1];
          break;
        case 'h':
          usage();
          exit(1);
        }
    }
}

/* Now deal with the non-option arguments. */
$args = $ret[1];

if (sizeof($args) == 0) {
  echo "Missing source filename\n";
  usage();
  exit(1);
} else if (sizeof($args) == 1) {
  $src = $args[0];
} else {
  echo "Extraneous arguments\n";
  usage();
  exit(1);
}

$dest = strip_ext($src);

/* Importation du BOM CSV. */
$data = array();
$row_num = 1;

if ($add_cost == false) {
  /* Pas d'affichage des prix. */
  $csv_infos['TotalCost']['width'] = 0;
  $csv_infos['UnitCost']['width'] = 0;
}

if (($handle = fopen($src, "r")) !== FALSE) {
  while (($row = fgetcsv($handle, 1024, ',')) !== false) {
    $num = count($row);

    if ($num < 2) {
      /* Ligne vide. */
      continue;
    }

    if ($row_num == 1) {
      /* Entête. */

      if ($add_cost == true) {
        /* Ajout colonne temporaire pour le total cost. */
        $row[$num] = "TotalCost";
        $num++;
      }

      /* Ajout colonne temporaire selon le type de composant. Cela est
       * nécessaire pour faire le tri correctement. */
      $row[$num] = "Type";
      $num++;

      /* Index de la prochaine colonne disponible pour affichage. */
      $k = 0;

      for ($c = 0; $c < $num; $c++) {
        $id = $row[$c];

        determine_col_index($id, $c, $k);

        if (isset($csv_infos[$id])) {
          if ($csv_infos[$id]['width'] != 0) {
            $nom = $csv_infos[$id]['nom'];

            $entete[$k] = $nom;

            $k++;
          }
        } else {
          echo "Colonne '$id' non supportée.\n";
        }
      }
    } else {
      $ref_prefix2 = substr($row[$col_id_to_num[DESIGNATOR_COL_NAME]], 0, 2);
      $company_pn = substr($row[$col_id_to_num[COMPANY_PN_COL_NAME]], 0, 1); /* Pour les vieux BOMs qui n'ont pas
                                                                 * l'attribut NOPOP. */
      if (($ref_prefix2 == "MH") ||
          ($ref_prefix2 == "TP") ||
          ($ref_prefix2 == "FI") ||
          (array_key_exists(ASSEMBLY_COL_NAME, $col_id_to_num) && ($row[$col_id_to_num[ASSEMBLY_COL_NAME]] == DO_NOT_POPULATE_KEYWORD)) ||
          ($company_pn == "*")) {
        /* Enlève les composants non désirés. */
        continue;
      }

      $ref_prefix = substr($ref_prefix2, 0, 1);
      $row[$col_id_to_num['Type']] = $ref_prefix;
      $data[] = $row;
    }

    $row_num++;
  }
  fclose($handle);
}

/* Combinaison de plusieurs lignes en une seule pour PCAD. */
refdes_combine($data, $num, $col_num_to_id, $col_id_to_num);

/* Génère une liste des colonnes pour le tri. */
foreach ($data as $key => $row) {
  $sort_type[$key]  = $row[$col_id_to_num['Type']];
  $sort_desc[$key] = $row[$col_id_to_num['Description']];
  $sort_val[$key] = $row[$col_id_to_num['Value']];
  $sort_volt[$key] = $row[$col_id_to_num['Voltage']];
}

/*
 * Triage des lignes selon la référence (colonne DESIGNATOR_COL_NAME),
 * et ensuite selon la description, la valeur et enfin le voltage.
 */
array_multisort($sort_type, SORT_ASC, SORT_STRING,
                $sort_desc, SORT_ASC, SORT_STRING,
                $sort_val,  SORT_ASC, SORT_NUMERIC,
                $sort_volt, SORT_ASC, SORT_NUMERIC,
                $data);

if ($digikey_bom) {
  /* Exportation BOM digikey. */
  $dest .= ".txt";
  $inventory = array();

  if ($use_inventory) {
    $inventory = import_inventory($inventory_filename);
  }

  export_bom_digikey($data, $num, $col_num_to_id, $col_id_to_num, $dest, $inventory);
} else {
  /* Exportation BOM Excel */

  $dest .= ".xls";

  /* Création d'un workbook. */
  $workbook = new Spreadsheet_Excel_Writer($dest);

  /* Création du worksheet */
  $worksheet =& $workbook->addWorksheet($titre);

  if (PEAR::isError($worksheet)) {
      die($worksheet->getMessage());
  }

  $row_num = DATA_ROW_START;

  /* Écriture des colonnes d'entête. */
  $k = 0;
  foreach ($entete as $key => $value) {
    xls_write_col_header($row_num, $k, $value);
    $k++;
  }

  $row_num++;

  /* Insertion des données dans le tableau. */
  foreach ($data as $key => $row) {
    $comp_total++; /* Compte le nombre de composants total. */
    $num = count($row);

    /* Index de la prochaine colonne disponible pour affichage. */
    $k = 0;

    for ($c = 0; $c < $num; $c++) {
      if (isset($csv_infos[$col_num_to_id[$c]])) {
        $width = $csv_infos[$col_num_to_id[$c]]['width'];

        if (($add_cost == true) && ($col_num_to_id[$c] == "UnitCost")) {
          /* Lecture du prix dans Syteline. */
          $price = get_price($row[$col_id_to_num[COMPANY_PN_COL_NAME]]);
          if ($price == 0) {
            /* Si pas de prix Syteline, on utilise le prix dans Altium. */
            $price = $row[$c];
          }

          if ($price == 0) {
            $price = "";
          }

          xls_write_price($row_num, $k, $price);

          $k++;
        } else if (($add_cost == true) && ($col_num_to_id[$c] == "TotalCost")) {
          /* Formule prix total = qté * unit_cost */
          xls_write_price($row_num, $k, "=" . col_to_letter($col_id_to_xls_num["Quantity"]) .
                          ($row_num + 1) . "*" . col_to_letter($col_id_to_xls_num["UnitCost"]) .
                          ($row_num + 1) . ")");

          $k++;
        } else if ($col_num_to_id[$c] == "MPN") {
          $mpn = $row[$c];

          if (empty($mpn)) {
            /* Lecture du "MPN" dans Syteline si celui dans Altium est absent. */
            $mpn = get_mpn($row[$col_id_to_num[COMPANY_PN_COL_NAME]]);
          }

          xls_write_cell($row_num, $k, $mpn, XLS_FORMAT_TEXT);

          $k++;
        } else if ($col_num_to_id[$c] == "Manufacturer") {
          $manufacturer = $row[$c];

          //if (empty($manufacturer)) {
          /* Lecture du "Manufacturer" dans Syteline si celui dans Altium est absent. */
          //$manufacturer = get_manufacturer($row[$col_id_to_num[COMPANY_PN_COL_NAME]]);
          //}

          xls_write_cell($row_num, $k, $manufacturer, XLS_FORMAT_TEXT);

          $k++;
        } else {
          if ($width) {
            $value = $row[$c];

            /* Ligne de données. */
            xls_write_cell($row_num, $k, $value, XLS_FORMAT_TEXT);

            $k++;
          }
        }
      }
    }

    $row_num++;
  }

  if ($add_cost == true) {
    /* Ajout cellule prix total. */
    xls_write_price($row_num, $col_id_to_xls_num["TotalCost"],
                    "=SUM(" . col_to_letter($col_id_to_xls_num["TotalCost"]) .
                    (DATA_ROW_START + 2) . ":" . col_to_letter($col_id_to_xls_num["TotalCost"]) .
                    $row_num . ")");
  }

  /* TODO... */
  if (empty($project_name)) {
    $project_name = "PROJECT NAME";
  }
  if (empty($revision)) {
    $revision = "REV";
  }

  /* Écriture des informations globales relatives au BOM. */
  xls_write_cell_gen(0, 0, "BOM " . $project_name,
                     XLS_FORMAT_TEXT, false, 18, FALSE, FALSE);

  xls_write_cell_gen(1, 0, "Révision (schéma): " . $revision,
                     XLS_FORMAT_TEXT, false, 16, FALSE, FALSE);

  xls_write_cell_gen(1, 2, "Composants: " . $comp_total,
                     XLS_FORMAT_TEXT, false, 16, FALSE, FALSE);

  xls_configure($workbook, $worksheet);

  $workbook->close();

  if (!file_exists($dest)) {
    echo "Cannot create destination file: " . $dest . "\n";
    exit(1);
  }
}

?>