tindiemunge.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import codecs
import csv
import itertools
import sys

country_codes = {
  "AF": "AFGHANISTAN",
  "AX": "ÅLAND ISLANDS",
  "AL": "ALBANIA",
  "DZ": "ALGERIA",
  "AS": "AMERICAN SAMOA",
  "AD": "ANDORRA",
  "AO": "ANGOLA",
  "AI": "ANGUILLA",
  "AQ": "ANTARCTICA",
  "AG": "ANTIGUA AND BARBUDA",
  "AR": "ARGENTINA",
  "AM": "ARMENIA",
  "AW": "ARUBA",
  "AU": "AUSTRALIA",
  "AT": "AUSTRIA",
  "AZ": "AZERBAIJAN",
  "BS": "BAHAMAS",
  "BH": "BAHRAIN",
  "BD": "BANGLADESH",
  "BB": "BARBADOS",
  "BY": "BELARUS",
  "BE": "BELGIUM",
  "BZ": "BELIZE",
  "BJ": "BENIN",
  "BM": "BERMUDA",
  "BT": "BHUTAN",
  "BO": "BOLIVIA, PLURINATIONAL STATE OF",
  "BQ": "BONAIRE, SINT EUSTATIUS AND SABA",
  "BA": "BOSNIA AND HERZEGOVINA",
  "BW": "BOTSWANA",
  "BV": "BOUVET ISLAND",
  "BR": "BRAZIL",
  "IO": "BRITISH INDIAN OCEAN TERRITORY",
  "BN": "BRUNEI DARUSSALAM",
  "BG": "BULGARIA",
  "BF": "BURKINA FASO",
  "BI": "BURUNDI",
  "KH": "CAMBODIA",
  "CM": "CAMEROON",
  "CA": "CANADA",
  "CV": "CAPE VERDE",
  "KY": "CAYMAN ISLANDS",
  "CF": "CENTRAL AFRICAN REPUBLIC",
  "TD": "CHAD",
  "CL": "CHILE",
  "CN": "CHINA",
  "CX": "CHRISTMAS ISLAND",
  "CC": "COCOS (KEELING) ISLANDS",
  "CO": "COLOMBIA",
  "KM": "COMOROS",
  "CG": "CONGO",
  "CD": "CONGO, THE DEMOCRATIC REPUBLIC OF THE",
  "CK": "COOK ISLANDS",
  "CR": "COSTA RICA",
  "CI": "CÔTE D'IVOIRE",
  "HR": "CROATIA",
  "CU": "CUBA",
  "CW": "CURAÇAO",
  "CY": "CYPRUS",
  "CZ": "CZECH REPUBLIC",
  "DK": "DENMARK",
  "DJ": "DJIBOUTI",
  "DM": "DOMINICA",
  "DO": "DOMINICAN REPUBLIC",
  "EC": "ECUADOR",
  "EG": "EGYPT",
  "SV": "EL SALVADOR",
  "GQ": "EQUATORIAL GUINEA",
  "ER": "ERITREA",
  "EE": "ESTONIA",
  "ET": "ETHIOPIA",
  "FK": "FALKLAND ISLANDS (MALVINAS)",
  "FO": "FAROE ISLANDS",
  "FJ": "FIJI",
  "FI": "FINLAND",
  "FR": "FRANCE",
  "GF": "FRENCH GUIANA",
  "PF": "FRENCH POLYNESIA",
  "TF": "FRENCH SOUTHERN TERRITORIES",
  "GA": "GABON",
  "GM": "GAMBIA",
  "GE": "GEORGIA",
  "DE": "GERMANY",
  "GH": "GHANA",
  "GI": "GIBRALTAR",
  "GR": "GREECE",
  "GL": "GREENLAND",
  "GD": "GRENADA",
  "GP": "GUADELOUPE",
  "GU": "GUAM",
  "GT": "GUATEMALA",
  "GG": "GUERNSEY",
  "GN": "GUINEA",
  "GW": "GUINEA-BISSAU",
  "GY": "GUYANA",
  "HT": "HAITI",
  "HM": "HEARD ISLAND AND MCDONALD ISLANDS",
  "VA": "HOLY SEE (VATICAN CITY STATE)",
  "HN": "HONDURAS",
  "HK": "HONG KONG",
  "HU": "HUNGARY",
  "IS": "ICELAND",
  "IN": "INDIA",
  "ID": "INDONESIA",
  "IR": "IRAN, ISLAMIC REPUBLIC OF",
  "IQ": "IRAQ",
  "IE": "IRELAND",
  "IM": "ISLE OF MAN",
  "IL": "ISRAEL",
  "IT": "ITALY",
  "JM": "JAMAICA",
  "JP": "JAPAN",
  "JE": "JERSEY",
  "JO": "JORDAN",
  "KZ": "KAZAKHSTAN",
  "KE": "KENYA",
  "KI": "KIRIBATI",
  "KP": "KOREA, DEMOCRATIC PEOPLE'S REPUBLIC OF",
  "KR": "KOREA, REPUBLIC OF",
  "KW": "KUWAIT",
  "KG": "KYRGYZSTAN",
  "LA": "LAO PEOPLE'S DEMOCRATIC REPUBLIC",
  "LV": "LATVIA",
  "LB": "LEBANON",
  "LS": "LESOTHO",
  "LR": "LIBERIA",
  "LY": "LIBYA",
  "LI": "LIECHTENSTEIN",
  "LT": "LITHUANIA",
  "LU": "LUXEMBOURG",
  "MO": "MACAO",
  "MK": "MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF",
  "MG": "MADAGASCAR",
  "MW": "MALAWI",
  "MY": "MALAYSIA",
  "MV": "MALDIVES",
  "ML": "MALI",
  "MT": "MALTA",
  "MH": "MARSHALL ISLANDS",
  "MQ": "MARTINIQUE",
  "MR": "MAURITANIA",
  "MU": "MAURITIUS",
  "YT": "MAYOTTE",
  "MX": "MEXICO",
  "FM": "MICRONESIA, FEDERATED STATES OF",
  "MD": "MOLDOVA, REPUBLIC OF",
  "MC": "MONACO",
  "MN": "MONGOLIA",
  "ME": "MONTENEGRO",
  "MS": "MONTSERRAT",
  "MA": "MOROCCO",
  "MZ": "MOZAMBIQUE",
  "MM": "MYANMAR",
  "NA": "NAMIBIA",
  "NR": "NAURU",
  "NP": "NEPAL",
  "NL": "NETHERLANDS",
  "NC": "NEW CALEDONIA",
  "NZ": "NEW ZEALAND",
  "NI": "NICARAGUA",
  "NE": "NIGER",
  "NG": "NIGERIA",
  "NU": "NIUE",
  "NF": "NORFOLK ISLAND",
  "MP": "NORTHERN MARIANA ISLANDS",
  "NO": "NORWAY",
  "OM": "OMAN",
  "PK": "PAKISTAN",
  "PW": "PALAU",
  "PS": "PALESTINE, STATE OF",
  "PA": "PANAMA",
  "PG": "PAPUA NEW GUINEA",
  "PY": "PARAGUAY",
  "PE": "PERU",
  "PH": "PHILIPPINES",
  "PN": "PITCAIRN",
  "PL": "POLAND",
  "PT": "PORTUGAL",
  "PR": "PUERTO RICO",
  "QA": "QATAR",
  "RE": "RÉUNION",
  "RO": "ROMANIA",
  "RU": "RUSSIAN FEDERATION",
  "RW": "RWANDA",
  "BL": "SAINT BARTHÉLEMY",
  "SH": "SAINT HELENA, ASCENSION AND TRISTAN DA CUNHA",
  "KN": "SAINT KITTS AND NEVIS",
  "LC": "SAINT LUCIA",
  "MF": "SAINT MARTIN (FRENCH PART)",
  "PM": "SAINT PIERRE AND MIQUELON",
  "VC": "SAINT VINCENT AND THE GRENADINES",
  "WS": "SAMOA",
  "SM": "SAN MARINO",
  "ST": "SAO TOME AND PRINCIPE",
  "SA": "SAUDI ARABIA",
  "SN": "SENEGAL",
  "RS": "SERBIA",
  "SC": "SEYCHELLES",
  "SL": "SIERRA LEONE",
  "SG": "SINGAPORE",
  "SX": "SINT MAARTEN (DUTCH PART)",
  "SK": "SLOVAKIA",
  "SI": "SLOVENIA",
  "SB": "SOLOMON ISLANDS",
  "SO": "SOMALIA",
  "ZA": "SOUTH AFRICA",
  "GS": "SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS",
  "SS": "SOUTH SUDAN",
  "ES": "SPAIN",
  "LK": "SRI LANKA",
  "SD": "SUDAN",
  "SR": "SURINAME",
  "SJ": "SVALBARD AND JAN MAYEN",
  "SZ": "SWAZILAND",
  "SE": "SWEDEN",
  "CH": "SWITZERLAND",
  "SY": "SYRIAN ARAB REPUBLIC",
  "TW": "TAIWAN, PROVINCE OF CHINA",
  "TJ": "TAJIKISTAN",
  "TZ": "TANZANIA, UNITED REPUBLIC OF",
  "TH": "THAILAND",
  "TL": "TIMOR-LESTE",
  "TG": "TOGO",
  "TK": "TOKELAU",
  "TO": "TONGA",
  "TT": "TRINIDAD AND TOBAGO",
  "TN": "TUNISIA",
  "TR": "TURKEY",
  "TM": "TURKMENISTAN",
  "TC": "TURKS AND CAICOS ISLANDS",
  "TV": "TUVALU",
  "UG": "UGANDA",
  "UA": "UKRAINE",
  "AE": "UNITED ARAB EMIRATES",
  "GB": "UNITED KINGDOM",
  "US": "UNITED STATES",
  "UM": "UNITED STATES MINOR OUTLYING ISLANDS",
  "UY": "URUGUAY",
  "UZ": "UZBEKISTAN",
  "VU": "VANUATU",
  "VE": "VENEZUELA, BOLIVARIAN REPUBLIC OF",
  "VN": "VIET NAM",
  "VG": "VIRGIN ISLANDS, BRITISH",
  "VI": "VIRGIN ISLANDS, U.S.",
  "WF": "WALLIS AND FUTUNA",
  "EH": "WESTERN SAHARA",
  "YE": "YEMEN",
  "ZM": "ZAMBIA",
  "ZW": "ZIMBABWE",
}

product_weights = {
    "PCB Fixture block for solder paste stencilling": 1.62,
    "Re:load, the simple, robust, affordable dummy load": 0.1,
    "Epic Re:load, the simple, robust, powerful adjustable dummy load": 0.1,
    "Re:load PCB only": 0.01,
}

product_prices = {
    "PCB Fixture block for solder paste stencilling": 85,
    "Re:load, the simple, robust, affordable dummy load": 15,
    "Epic Re:load, the simple, robust, powerful adjustable dummy load": 25,
    "Re:load PCB only": 3,
}

local_country_codes = set([
        'AL', 'AD', 'AM', 'AT', 'BY', 'BE', 'BA', 'BG', 'CH', 'CY', 'CZ', 'DE',
  	'DK', 'EE', 'ES', 'FO', 'FI', 'FR', 'GB', 'GE', 'GI', 'GR', 'HU', 'HR',
		'IE', 'IS', 'IT', 'LT', 'LU', 'LV', 'MC', 'MK', 'MT', 'NO', 'NL', 'PO',
		'PT', 'RO', 'RU', 'SE', 'SI', 'SK', 'SM', 'TR', 'UA', 'VA',
])

def main(args):
    reader = csv.reader(x.encode('cp1252') for x in codecs.open(args[1], 'r', 'utf-8', 'ignore'))
    writer = csv.writer(open(args[2], 'wb'))

    cn = reader.next()
    ocn = list(cn) + ["Weight", "Value", "CN22"]
    writer.writerow(ocn)

    column_names = dict(zip(cn, itertools.count()))
    output_column_names = dict(zip(ocn, itertools.count()))

    for id, group in itertools.groupby(reader, lambda r: r[column_names['ID']]):
        items = list(group)
        if any(x[column_names['Shipped']] == 'True' or x[column_names['Refunded']] == 'True' for x in items):
            continue
        
        row = list(items[0])
        row += [None, None, None]

        country_code = row[column_names["Country"]]        
        row[output_column_names["Country"]] = country_codes.get(country_code, country_code)            
        row[output_column_names['Product Name']] = ','.join(item[column_names['Product Name']] for item in items)
        row[output_column_names['Quantity']] = sum(int(item[column_names['Quantity']]) for item in items)
        row[output_column_names['Weight']] = "%.1f" % sum(product_weights[item[column_names['Product Name']]] * int(item[column_names['Quantity']]) for item in items)
        row[output_column_names['Value']] = "%d" % sum(product_prices[item[column_names['Product Name']]] * int(item[column_names['Quantity']]) for item in items)
        row[output_column_names['CN22']] = country_code not in local_country_codes
        
        writer.writerow(row)


if __name__ == '__main__':
    main(sys.argv)