software/python/kicad_util/rw_orderlist_from_bom.py

   1 #! /usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3 # LICENSE
   4 #
   5 # This file is part of the Ruwai project.
   6 #
   7 # If you use this code in any program or publication, please inform and
   8 # acknowledge its author Stefan Mertl (stefan@mertl-research.at).
   9 #
  10 # This program is free software: you can redistribute it and/or modify
  11 # it under the terms of the GNU General Public License as published by
  12 # the Free Software Foundation, either version 3 of the License, or
  13 # (at your option) any later version.
  14 #
  15 # This program is distributed in the hope that it will be useful,
  16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  18 # GNU General Public License for more details.
  19 #
  20 # You should have received a copy of the GNU General Public License
  21 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
  22
  23 '''
  24 This script creates orderlist for a BOM created with bom_csv_grouped_components.py
  25 using an CSV inventory file. The BOM file hast to be a csv formatted file with the
  26 mandatory columns ref, value, manufacturer, manufacturer part number, quantity.
  27
  28 The orderlists can be used to easily place an order at a distributor for
  29 electronic components.
  30
  31 The inventory file has to be in the following format:
  32 number,type,manufacturer,manufacturer part number,mount,package,description,distributor,distributor part number,minimum order qty,packaging,cost per unit,stock
  33 1,battery,Keystone,500,THM,THM,"Batteriehalter PCB-Montage, für 1 Knopfzelle Ø 12mm, mit Federarm-Kontakt",RS-Components,430653,1,RS-standard,2.65,0
  34 2,capacitor,AVX,08051A100JAT2A,SMD,805,"AVX Vielschicht Keramikkondensator 10pF 100 V dc, C0G 805 SMD ±5%",RS-Components,4646559,50,RS-standard,0.076,0
  35 3,capacitor,AVX,08051A330JAT2A,SMD,805,"AVX Vielschicht Keramikkondensator 33pF 100 V dc, C0G 805 SMD ±5%",RS-Components,4646600P,50,RS-standard,0.061,0
  36
  37 The Ruwai git repository contains an inventory file for all components used in the Ruwai project.
  38 The Ruwai inventory file is located in the subfolder hardware/bom
  39
  40 The script searches the inventory for the components listed in the BOM, extracts
  41 the according information, computes the quantity to order based on the stock
  42 column and creates one orderlist for each distributor as well as one combined
  43 orderlist as an overview.
  44 '''
  45 from __future__ import print_function
  46
  47 import csv
  48 import sys
  49 import os
  50 import copy
  51 import datetime
  52 import argparse
  53 import logging
  54
  55
  56 def create_orderlist(args):
  57     # Open a file to write to, if the file cannot be opened output to stdout
  58     # instead
  59     #output_dir = os.path.dirname(bom_filename)
  60
  61     bom_filename = args.bom
  62     inv_filename = args.inv
  63     n_boards = args.nboards
  64     ignore_lines = args.ignore_lines
  65     ignore_stock = args.ignore_stock
  66
  67     cur_time = datetime.datetime.now()
  68     timestamp = cur_time.strftime('%Y%m%d-%H%M%S')
  69     subdir = 'orderlist_' + timestamp
  70     output_dir = os.path.join('.', subdir)
  71     if not os.path.exists(output_dir):
  72         os.mkdir(output_dir)
  73     [bom_basename, bom_ext] = os.path.splitext(os.path.basename(bom_filename))
  74
  75
  76     # Read the inventory information.
  77     inventory = {}
  78     with open(inv_filename, 'rb') as inv_fid:
  79         inv_reader = csv.DictReader(inv_fid, delimiter=',')
  80         for row in inv_reader:
  81             cur_manufacturer = row['manufacturer'].lower().strip()
  82             cur_part_num = row['manufacturer part number'].lower().strip()
  83             part_attr = copy.copy(inv_reader.fieldnames)
  84             part_attr.remove('manufacturer')
  85             part_attr.remove('manufacturer part number')
  86             cur_part = {}
  87             for cur_attr in part_attr:
  88                 cur_part[cur_attr] = row[cur_attr]
  89
  90             if cur_manufacturer not in inventory.keys():
  91                 inventory[cur_manufacturer] = {}
  92
  93             inventory[cur_manufacturer][cur_part_num] = cur_part
  94
  95
  96     # Read the BOM, assign the inventory information and write it to output file.
  97     with open(bom_filename, 'r') as bom_fid:
  98         header = []
  99         for k in range(ignore_lines):
 100             header.append(bom_fid.readline())
 101         bom_reader = csv.DictReader(bom_fid, delimiter=',')
 102
 103         # Select the fields of the inventory to export to the order list.
 104         export_attr = ['number', 'package', 'distributor', 'distributor part number', 'description', 'cost per unit', 'stock', '# boards', 'rem. stock', 'quantity order']
 105         export_inv_header = ['number', 'manufacturer package', 'distributor', 'distributor part number', 'distributor description', 'cost per unit', 'stock', '# boards', 'rem. stock', 'quantity order']
 106
 107         # Select the fields of the bom to export to the order list.
 108         export_bom_attr = ['ref', 'value', 'quantity', 'manufacturer', 'manufacturer part number']
 109         #output_fieldnames = [x for x in bom_reader.fieldnames if x in export_bom_attr]
 110         #output_fieldnames.extend(export_inv_header)
 111
 112         order_list = {}
 113         order_list['missing_distributor'] = []
 114         for row in bom_reader:
 115             try:
 116                 cur_manu = row['manufacturer'].lower().strip()
 117                 cur_part_num = row['manufacturer part number'].lower().strip()
 118                 inv_data = inventory[cur_manu][cur_part_num]
 119
 120                 # Compute the quantity to order.
 121                 qty_needed = float(row['quantity']) * n_boards
 122                 qty_stock = float(inv_data['stock'])
 123                 if ignore_stock:
 124                     qty_order = qty_needed
 125                     rem_stock = qty_stock
 126                 else:
 127                     if qty_needed < qty_stock:
 128                         qty_order = 0
 129                     else:
 130                         qty_order = qty_needed - qty_stock
 131                     rem_stock = qty_stock - qty_needed
 132
 133                     if rem_stock < 0:
 134                         rem_stock = 0
 135
 136
 137                 inv_data['# boards'] = n_boards
 138                 inv_data['quantity order'] = str(qty_order)
 139                 inv_data['rem. stock'] = rem_stock
 140             except:
 141                 inv_data = None
 142
 143             if inv_data is not None:
 144                 inv_row = [inv_data[x] for x in export_attr]
 145             else:
 146                 inv_row = ['' for x in export_attr]
 147
 148             output_row = [row[x] for x in export_bom_attr]
 149             output_row.extend(inv_row)
 150
 151             if inv_data is not None:
 152                 if inv_data['distributor'] not in order_list.keys():
 153                     order_list[inv_data['distributor']] = []
 154
 155                 order_list[inv_data['distributor']].append(output_row)
 156             else:
 157                 order_list['missing_distributor'].append(output_row)
 158
 159
 160         overview_filename = os.path.join(output_dir, bom_basename + '_orderlist_' + timestamp + '.csv')
 161         overview_fid = open(overview_filename, 'w')
 162         overview_writer = csv.writer(overview_fid, lineterminator='\n', delimiter=',', quoting=csv.QUOTE_MINIMAL)
 163         export_header = []
 164         export_header.extend(export_bom_attr)
 165         export_header.extend(export_inv_header)
 166         overview_writer.writerow(export_header)
 167
 168         for cur_key, cur_order_list in order_list.iteritems():
 169
 170             output_filename = os.path.join(output_dir, bom_basename + '_orderlist_' + timestamp + '_' + cur_key + '.csv')
 171             try:
 172                 out_fid = open(output_filename, 'w')
 173             except IOError as e:
 174                 print(__file__, ":", e, file=sys.stderr)
 175                 out_fid = sys.stdout
 176
 177             # Create a new csv writer object to use as the output formatter, although we
 178             # are created a tab delimited list instead!
 179             out_writer = csv.writer(out_fid, lineterminator='\n', delimiter=',', quoting=csv.QUOTE_MINIMAL)
 180
 181             #for cur_header in header:
 182             #    out_writer.writerow(cur_header.strip().split(','))
 183             export_header = []
 184             export_header.extend(export_bom_attr)
 185             export_header.extend(export_inv_header)
 186             out_writer.writerow(export_header)
 187
 188             out_writer.writerows(cur_order_list)
 189             overview_writer.writerows(cur_order_list)
 190
 191             logging.info('Wrote file %s for distributor %s.', output_filename, cur_key)
 192
 193         logging.info('Wrote overview file %s.', overview_filename)
 194
 195
 196 if __name__ == '__main__':
 197     parser = argparse.ArgumentParser(description='Create orderlists in CSV format from bill-of-materials and inventory CSV files.')
 198     parser.set_defaults(func = create_orderlist)
 199     parser.add_argument('bom', help = 'The bill-of-material csv file.',
 200                         type = str, metavar = 'BOM_FILENAME')
 201     parser.add_argument('inv', help = 'The inventory csv file.',
 202                         type = str, metavar = 'INV_FILENAME')
 203     parser.add_argument('--nboards', help = 'The number of pcb boards for which the orderlist is created.',
 204                         type = int, metavar = 'N_BOARDS', default = 1)
 205     parser.add_argument('--ignore-lines', help = 'The number of lines to ignore at the beginning of the file.',
 206                         type = int, metavar = 'ignore_lines', default = 4)
 207     parser.add_argument('--loglevel', help = 'Specify the log level.',
 208                         type = str, choices = ['DEBUG', 'INFO', 'WARNING', 'ERROR', 'CRITICAL', 'NOTSET'],
 209                         default = 'INFO')
 210     parser.add_argument('--ignore-stock', help = 'Ignore the parts available in stock when computing the quantity to order.',
 211                         action = 'store_true', dest = 'ignore_stock')
 212
 213     args = parser.parse_args()
 214
 215     logging.basicConfig(level = args.loglevel,
 216                         format = "#LOG# - %(asctime)s - %(process)d - %(levelname)s: %(message)s")
 217
 218     args.func(args)
 219
 220