#!/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 'Console/Getopt.php';

define("XLS_DATA_ROW_START", 3);

define("PAPER_US_LETTER", 1);

$debug = 0;

/* Default values. */
$add_cost = false;
$digikey_bom = false;
$kits = 1;
$use_inventory = false;
$comp_total = 0;
$titre = "BOM";
$bom_type = "";

/*
 * 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();

function usage()
{
  echo "Usage: bomgen.php [OPTIONS] [INPUT FILE]\n";
  echo "Generate an Excel BOM from a PCAD or Altium 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 [FILE]  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 [QTY]   Number of cards/kits for digikey BOM submission\n";
  echo "  -p [NAME]  Specify project or board name\n";
  echo "  -r [REV]   Specify BOM revision\n";
  echo "  -t [TYPE]  BOM input type: pcad or altium\n";
}

$short_opts = "hci:kn:p:r:t:";

$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 'p':
          $project_name = $o[1];
          break;
        case 'r':
          $revision = $o[1];
          break;
        case 'h':
          usage();
          exit(1);
        case 't':
          $bom_type = strtolower($o[1]);
          break;
        }
    }
}

/* 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);
}

switch ($bom_type) {
case 'pcad':
  require_once 'Bomgen/pcad.inc.php';
  break;
case 'altium':
  require_once 'Bomgen/altium.inc.php';
  break;
default:
  echo "Invalid BOM type: " . $bom_type . "\n";
  exit(1);
  break;
}

/* These files must be included before others. */
require_once 'Bomgen/mapping.inc.php';
require_once 'Bomgen/digikey.inc.php';

require_once 'Spreadsheet/Excel/Writer.php';

require_once 'Bomgen/conf.inc.php';
require_once 'Bomgen/functions.inc.php';
require_once 'Bomgen/refdes.inc.php';

$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;
}

/* Read and import BOM source file. */
bom_import_source($src);

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

$voltage_present = false;

/* 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']];

  if (isset($col_id_to_num['Voltage'])) {
    $voltage_present = true;
    $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.
 */
if ($voltage_present) {
  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);
} else {
  array_multisort($sort_type, SORT_ASC, SORT_STRING,
                  $sort_desc, SORT_ASC, SORT_STRING,
                  $sort_val,  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 = XLS_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"]) .
                    (XLS_DATA_ROW_START + 2) . ":" . col_to_letter($col_id_to_xls_num["TotalCost"]) .
                    $row_num . ")");
  }

  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: " . $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);
  }
}

?>