bomExportCSVwComma.ulp

#require 6.5100

// Revision history:
//
// 1.00
// - Initial release
//
// 1.01
// - Dialog mask for text or html output selection corrected
//
// 1.02
// - CSV export added after a suggestion of Christian Schlittler
// - File extension for text file output changed from .bom to .txt
//
// 1.03
// - Added missing description column in value mode.
//
// 1.04
// - List also Packages bei Value
//
// 1.05
// - 2012-03-01 change PartValue[], PartDevice[], PartPackage[], PartHeadline[], PartDescription[] to normal string. alf@cadsoft.de
//   numeric strings with only numericasl characters, sorts up to 8 characters!
//
// 1.06
// - 2012-05-25 support now populated variants
//              switch on/off attributes
//              is now the standard bom.ulp  alf@cadsoft.de
//
// 1.07
// - 2012-06-22 set correct variant
//
// 1.08
// - 2012-11-08 list different values of attributes
//
// 1.09
// - 2012-12-04 now can change the separator character for CSV files.
//              change the character in line Separator = ',';
//
// 1.10
// - 2014-08-07 extended to V7 hierarchical schematic
//
// Revision: 1.10
//

string Version = "1.10";

char   Separator = ','; // Paul changed this back


#usage "en: <b>Export a Bill Of Material</b>\n"
           "<p>"
           "Generates a project's <i>Bill Of Material</i>&nbsp;&nbsp;including the attributes introduced in"
           " version 5.0.0."
           "<p>"
           "<author>Author: support@cadsoft.de</author><br>"
           "<author>Modified to use the new attributes by Carsten Wille.</author><br>"
           "<author>.csv export added by Christian Schlittler.</author>"
           "<author>Select variant and switch on/off attributes alf@cadsoft.de</author>",
       "de: <b>Stückliste exportieren</b>\n"
           "<p>"
           "Erzeugt die <i>Stückliste</i> (Bill Of Material) eines Projekts, einschließlich der"
           " mit Version 5.0.0 neu eingeführten Attribute."
           "<p>"
           "<author>Autor: support@cadsoft.de</author><br>"
           "<author>Modifiziert von Carsten Wille, um die neuen Attribute zu nutzen.</author><br>"
           "<author>Export als .csv-Datei durch Christian Schlittler hinzugefügt.</author>"
           "<author>Auswählen von Bestückungs-Varianten und Attribute können ein/ausgeschaltet werden alf@cadsoft.de</author>"

// THIS PROGRAM IS PROVIDED AS IS AND WITHOUT WARRANTY OF ANY KIND, EXPRESSED OR IMPLIED

string HelpTextEN =
  "<b>How to generate the Bill Of Material</b>\n"
  "<p>\n"
  "<b>List type</b>\n"
  "<p>\n"
  "The <i>Bill Of Material</i> can be generated either as a list\n"
  "of parts (where every part is listed on a line of its own),\n"
  "or as a list of values, where all parts with the same value are grouped\n"
  "together in one line. Use the <b><u>P</u>arts</b> and <b><u>V</u>alues</b>\n"
  "radio buttons to select the list type.\n"
  "<p>\n"
  "<b>Output format</b>\n"
  "<p>\n"
  "Choose between pure ASCII <b><u>T</u>ext</b> format, <b><u>C</u>SV</b> or <b><u>H</u>TML</b>.\n"
  ;

string HelpTextDE =
  "<b>Erzeugen der Stückliste</b>\n"
  "<p>\n"
  "<b>Listen-Typ</b>\n"
  "<p>\n"
  "Die <i>Stückliste</i> kann entweder als Liste der Bauteile generiert werden\n"
  "(wobei jedes Bauteil in einer eigenen Zeile aufgeführt wird),\n"
  "oder als Liste der Werte, wobei alle Bauteile mit dem gleichen Wert in einer Zeile\n"
  "zusammengefasst werden. Mit den Radio-Buttons <b><u>B</u>auteile</b> und <b><u>W</u>erte</b>\n"
  "kann zwischen den beiden Listen-Typen gewählt werden.\n"
  "<p>\n"
  "<b>Ausgabeformat</b>\n"
  "<p>\n"
  "Wählen Sie zwischen reinem ASCII-<b><u>T</u>ext</b>, <b><u>C</u>SV</b> oder <b><u>H</u>TML</b>"
  "-Format.\n"
  ;

string I18N[] = {
  "en\v"
  "de\v"
  ,
  "<hr><b>ERROR: No schematic!</b><p>\nThis program can only work in the schematic editor.\v"
  "<hr><b>FEHLER: Kein Schaltplan!</b><p>\nDieses Programm kann nur in einem Schaltplan verwendet"
  " werden.\v"
  ,
  "Part\tValue\tDevice\tPackage\tDescription\v"
  "Bauteil\tWert\tDevice\tPackage\tDescription\v"
  ,
  "Qty\tValue\tDevice\tPackage\tParts\v"      // 2011-04-08
  "Menge\tWert\tDevice\tGehäuse\tBauteile\v"  // 2011-04-08
  ,
  "Partlist exported from %s at %s\v"
  "Stückliste exportiert aus %s am %s\v"
  ,
  "Bill Of Material - Preview\v"
  "Stückliste - Vorschau\v"
  ,
  "-Close\v"
  "-Schließen\v"
  ,
  "Save Bill Of Material\v"
  "Stückliste speichern\v"
  ,
  "File '\v"
  "Datei '\v"
  ,
  "' exists\n\nOverwrite?\v"
  "' existiert\n\nÜberschreiben?\v"
  ,
  "+&Yes\v"
  "+&Ja\v"
  ,
  "-&No\v"
  "-&Nein\v"
  ,
  "&No\v"
  "&Nein\v"
  ,
  "Name already defined!\v"
  "Name ist bereits definiert!\v"
  ,
  " Header\v"
  " Spaltenüberschrift\v"
  ,
  "&Name:\v"
  "&Name:\v"
  ,
  "+OK\v"
  "+OK\v"
  ,
  "Name can't be empty!\v"
  "Name kann nicht leer sein!\v"
  ,
  "-Cancel\v"
  "-Abbrechen\v"
  ,
  "&Headers\v"
  "&Spaltenüberschriften\v"
  ,
  "Bill Of Material - Help\v"
  "Stückliste - Hilfe\v"
  ,
  "Bill Of Material\v"
  "Stückliste\v"
  ,
  "List type\v"
  "Listen-Typ\v"
  ,
  "&Parts\v"
  "&Bauteile\v"
  ,
  "&Values\v"
  "&Werte\v"
  ,
  "Output format\v"
  "Ausgabeformat\v"
  ,
  "&Text\v"
  "&Text\v"
  ,
  "&CSV\v"
  "&CSV\v"
  ,
  "&HTML\v"
  "&HTML\v"
  ,
  "+Vie&w\v"
  "+&Vorschau\v"
  ,
  "&Save...\v"
  "&Speichern...\v"
  ,
  "H&elp\v"
  "H&ilfe\v"
  ,
  "Current &variant \v"
  "Aktuelle &Variante \v"
  ,
  "List &attributes\v"
  "&Attribute auflisten\v"
  };
int Language = strstr (I18N [0], language ()) / 3;


string tr (string s)
{
  string t = lookup (I18N, s, Language, '\v');
  return t ? t : s;
}


if (!schematic)
{
  dlgMessageBox (usage + tr ("<hr><b>ERROR: No schematic!</b><p>\nThis program can only work in"
                 " the schematic editor."));
  exit (1);
}

string SeparatorString;
int     NumParts;
numeric string Lines[];
numeric string PartName[];
string  PartValue[], PartDevice[], PartPackage[], PartHeadline[];
numeric string PartDescription [];
int     PartValueOn[];
int     Selected;

string CurrentVariant = "";
string Variants[] = { "" };  // 2012-04-16
int    cntVD = 0;
int    VDsel = 0;


// cwi: Added arrays for an arbitraty number of attributes.
int UseAttributes = 1;
int FoundAttributes = 0;            // # of different attribute names found in schematic.
numeric string AttributesList[];    // Sorted list of all attributes found in the schematic.
numeric string PartAttributes[];    // Adjusted list of attributes per part.

enum { ltParts, ltValues };         // List Types
enum { ofText, ofCSV, ofHTML };     // Output Formats
int ListType = 0;
int OutputFormat = 0;


string StripWhiteSpace (string s)
{
  while (s && isspace (s[0]))
    s = strsub (s, 1);
  while (s && isspace (s[strlen (s) - 1]))
    s = strsub (s, 0, strlen (s) - 1);
  return s;
}


// Collect part data from the schematic.
//
// Arguments: -
//
// Returns:   NumParts            - # of found parts
//            ParteName[]
//            PartValue[]
//            PartDevice[]
//            PartPackage[]
//            PartHeadline[]
//            PartDescription []
//            PartValueOn[]      - 0=part value off, 1= part value on, 2=override with attr. VAL
//            FoundAttributes     - # of different attribute names found in schematic.
//            AttributesList[]   - Sorted list of all attributes found in the schematic.
//            PartAttributes[]   - Adjusted list of attributes per part.

void CollectPartData (string var)
{
  int Found = 0;
  int i;
  string attr[];

  NumParts = 0;

  // First, collect the names of all available attributes.
  FoundAttributes = 0;
  if (UseAttributes) {
    schematic (SCH)
    {
      SCH.allparts (P) // 2014-08-07
      {
        if (P.device.package)
        {
          if (P.populate)
          {
            P.attributes (A)
            {
              if (0 == FoundAttributes)
              {
                // First one
                AttributesList[0] = A.name;
                FoundAttributes = 1;
              }
              else
              {
                Found = 0;
                for (i = 0; i < FoundAttributes; i ++)
                {
                  if (A.name == AttributesList[i])
                  {
                    // Found an already listed atrribute
                    Found = 1;
                    break;
                  }
                }
                if (0 == Found)
                {
                  // Attribute not listed, add at the end.
                  AttributesList[FoundAttributes] = A.name;
                  FoundAttributes ++;
                }
              }
            }
          }
        }
      }
    }
    sort (FoundAttributes, AttributesList);
  }
  // Second, collect all data
  schematic (SCH)
  {
    SCH.allparts (P)
    {
      if (P.device.package)
      {
        if (P.populate)
        {
          PartName[NumParts] = P.name;
          PartValue[NumParts] = P.value;
          PartDevice[NumParts] = P.device.name;
          PartPackage[NumParts] = P.device.package.name;
          PartHeadline[NumParts] = P.device.headline;
          PartDescription [NumParts] = P.device.description;
          PartValueOn[NumParts] = P.device.value == "On";
          // Zero all strings
          for (i = 0; i < FoundAttributes; i ++)
            attr[i] = "";
          P.attributes(A)
          {
            for (i = 0; i < FoundAttributes; i ++)
              if (A.name == AttributesList[i])
              {
                attr[i] = A.value;
                break;
              }
            if ("VALUE" == A.name && 0 < strlen (A.value))
             // Override old fashioned value information!
            PartValueOn[NumParts] = 2;
          }
          PartAttributes[NumParts] = strjoin(attr, Separator);
          NumParts ++;
        }
      }
    }
  }
}


void GeneratePartList(void)
{
  int NumLines = 0;
  string attr[], s;

  if (UseAttributes) s = strjoin(AttributesList, '\t');
  Lines[NumLines ++] = tr ("Part\tValue\tDevice\tPackage\tDescription\t") + s;
  for (int i = 0; i < NumParts; i ++)
  {
    strsplit (attr, PartAttributes[i], Separator);
    if (UseAttributes) s = strjoin(attr, '\t');
    Lines[NumLines] = PartName[i] + "\t" + PartValue[i] + "\t" + PartDevice[i] + "\t" +
                       PartPackage[i] + "\t" + PartHeadline[i] + "\t" + s;
    NumLines ++;
  }
  Lines[NumLines] = "";
}


// Generate list with one entry per value.
// 'VALUE' is replaced by the value of attribute 'VAL', if existing.

void GenerateValueList (void)
{
  int NumLines = 0;
  int Index [];
  string attr[], s, s_val;

  if (UseAttributes) s = strjoin(AttributesList, '\t');

  // 2010-04-17 cwi: Included description.
  Lines[NumLines ++] = tr ("Qty\tValue\tDevice\tPackage\tParts\tDescription\t") + s;                 // 2011-04-08
  sort (NumParts, Index, PartValue, PartDevice, PartPackage, PartAttributes, PartName, PartHeadline); // 2011-11-08 Partattribute jetzt nach Package alf@cadsoft.de
  for (int n1 = 0, n2 = 0; ++ n2 <= NumParts; )
  {
    int i1 = Index [n1];
    strsplit (attr, PartAttributes[i1], Separator);
    if (UseAttributes) s = strjoin(attr, '\t');
    s_val = attr[i1];
    if (n2 < NumParts)
    {
      int i2 = Index [n2]; // 2012-11-08
      strsplit (attr, PartAttributes[i2], Separator);
      if (PartValue[i1] == PartValue[i2] && PartDevice[i1] == PartDevice[i2] && PartAttributes[i1] == PartAttributes[i2]) // 2012-11-08 check diffent values of attributes
        continue;
    }
    string Quantity;
    sprintf (Quantity, "%d", n2 - n1);
    Lines[NumLines] = Quantity + "\t" + PartValue[i1] + "\t" + PartDevice[i1] + "\t" + PartPackage[i1] + "\t";
    for (;;)
    {
      Lines[NumLines] += PartName[i1];
      if (++n1 < n2)
      {
        i1 = Index [n1];
        Lines[NumLines] += ", ";
      }
      else
        break;
    }
    // cwi: add extra information from attributes
    // 2010-04-17 cwi: Included description.
    Lines[NumLines] += "\t" + PartHeadline[i1] + "\t" + s;
    NumLines ++;
  }
  Lines[NumLines] = "";
}


void GenerateList (void)
{
  switch (ListType)
  {
    case ltParts:  GeneratePartList (); break;
    case ltValues: GenerateValueList (); break;
  }
}


string MakeListHeader (void)
{
  string s;
  schematic(SCH)
    sprintf (s, tr ("Partlist exported from %s at %s"), SCH.name, t2string (time ()));
  return s;
}


string MakeListText(void)
{
  int l, Width [];
  string List;
  int numHeaders;

  for (l = 0; Lines[l]; l ++)
  {
    string a [];
    for (int n = strsplit (a, Lines[l], '\t'); n --; )
      Width [n] = max (Width [n], strlen (a [n]));
  }

  List = MakeListHeader () + "\n\n";

  for (l = 0; Lines[l]; l ++)
  {
    string line, a [];

    int n = strsplit (a, Lines[l], '\t');
    if (l == 0)
      numHeaders = n;
    else
      n = numHeaders; // for the hidden key!
    for (int i = 0; i < n; i ++)
    {
      string s;

      sprintf (s, "%s%-*s", line ? " " : "", Width [i], a [i]);
      line += s;
    }
    List += line + "\n";
  }
  return List;
}


// 2008-11-24 Christian Schlittler:
// Make comma-serparated list, with all values double-quoted.

string MakeListCSV (void)
{
  string List;
  int numHeaders;

  for (int l = 0; Lines[l]; l ++)
  {
    string a [];
    int n = strsplit (a, Lines[l], '\t');
    if (l == 0)
      numHeaders = n;
    else
      n = numHeaders; // for the hidden key!
    for (int i = 0; i < n; i ++)
      List += "\"" + a[i] + "\"" + SeparatorString;
    List += "\n";
  }
  return List;
}


string MakeListHTML (void)
{
  string List;
  int numHeaders;

  List = "<b>" + MakeListHeader() + "</b>\n<p>\n";
  List += "<table>\n";
  for (int l = 0; Lines[l]; l++)
  {
    List += "<tr>";
    string a [];
    int n = strsplit (a, Lines[l], '\t');
    if (l == 0)
      numHeaders = n;
    else
      n = numHeaders; // for the hidden key!
    for (int i = 0; i < n; i ++)
    {
      if (l == 0)
        a[i] = "<b>" + a[i] + "</b>";
      List += "<td>" + a[i] + "</td>";
    }
    List += "</tr>\n";
  }
  List += "</table>\n";
  return List;
}


string MakeList (void)
{
  switch (OutputFormat)
  {
    case ofText: return MakeListText(); break;
    case ofCSV:  return MakeListCSV(); break;
    case ofHTML: return MakeListHTML(); break;
  }
  return "";
}


void ViewList (void)
{
  dlgDialog (tr ("Bill Of Material - Preview"))
  {
    string s = MakeList ();
    if (OutputFormat == ofText || OutputFormat == ofCSV)
      s = "<pre>" + s + "</pre>";
    dlgHBoxLayout dlgSpacing (400);
    dlgHBoxLayout
    {
      dlgVBoxLayout dlgSpacing (300);
      dlgTextView (s);
    }
    dlgHBoxLayout
    {
      dlgStretch (1);
      dlgPushButton (tr ("-Close")) dlgReject ();
    }
  };
}


void SaveList (void)
{
  // 2008-11-24 cwi:
  // - Added new format extension .csv
  // - Changed from .bom to .txt for text format.
  string FileName;
  string FileExt;

  switch (OutputFormat)
  {
    case ofText: FileExt = ".txt"; break;
    case ofHTML: FileExt = ".html"; break;
    case ofCSV:  FileExt = ".csv"; break;
  }
  schematic(SCH) FileName = filesetext (SCH.name, FileExt);
  FileName = dlgFileSave (tr ("Save Bill Of Material"), FileName);
  if (FileName)
  {
    string a [];
    if (!fileglob (a, FileName) || dlgMessageBox (tr ("File '") + FileName +
        tr ("' exists\n\nOverwrite?"), tr("+&Yes"), tr("-&No")) == 0)
    {
      output (FileName, "wt")
      {
        printf ("%s", MakeList ()); // using "%s" to avoid problems if list contains any '%'
      }
    }
  }
}


void DisplayHelp (void)
{
  dlgDialog (tr ("Bill Of Material - Help"))
  {
    dlgHBoxLayout dlgSpacing (400);
    dlgHBoxLayout
    {
      dlgVBoxLayout dlgSpacing (300);
      dlgTextView (language () == "de" ? HelpTextDE : HelpTextEN);
    }
    dlgHBoxLayout
    {
      dlgStretch (1);
      dlgPushButton (tr ("-Close"))
      dlgReject ();
    }
  };
}


schematic(SCH) {
  sprintf(SeparatorString, "%c", Separator);
  CurrentVariant = variant();
  SCH.variantdefs(VD) {
    if (CurrentVariant == VD.name) VDsel = cntVD;
    sprintf(Variants[cntVD], "%s", VD.name);
    cntVD++;
  }
}

setvariant(CurrentVariant);
CollectPartData(CurrentVariant);
GenerateList();

dlgDialog (tr ("Bill Of Material"))
{
  dlgHBoxLayout {
    dlgLabel(tr ("Current &variant "));
    dlgComboBox(Variants, VDsel) {
      CurrentVariant = Variants[VDsel];
      setvariant(CurrentVariant);
      CollectPartData(CurrentVariant);
      GenerateList();
    }
    dlgStretch(1);
  }
  dlgListView ("", Lines, Selected);
  dlgHBoxLayout
  {
    dlgGroup(tr ("List type"))
    {
      dlgRadioButton(tr ("&Parts"), ListType) GeneratePartList ();
      dlgRadioButton(tr ("&Values"), ListType) GenerateValueList ();
      dlgCheckBox(tr ("List &attributes"), UseAttributes) {
        if (!UseAttributes) {
          NumParts = 0;
        }
        CollectPartData(CurrentVariant);
        GenerateList();
      }
    }
    dlgGroup (tr ("Output format"))
    {
      // 2008-10-09: Entries swapped for correct function.
      dlgRadioButton(tr ("&Text"), OutputFormat);
      // 2008-11-24 cwi: New format added.
      dlgRadioButton(tr ("&CSV"), OutputFormat);
      dlgRadioButton(tr ("&HTML"), OutputFormat);
    }
    dlgStretch(1);
  }
  dlgHBoxLayout  {
    dlgPushButton (tr ("+Vie&w"))    ViewList ();
    dlgPushButton (tr ("&Save...")) SaveList ();
    dlgPushButton (tr ("H&elp"))    DisplayHelp ();
    dlgPushButton (tr ("-Close"))   dlgAccept ();
    dlgStretch(1);
    dlgLabel("Version " + Version);
  }
};