#!/usr/bin/awk -f

#   Copyright (C) 2011 Charles
#
#   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.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#   Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, US

# Name: Outlook_CSV_to_OTRS_CSV.awk

# Purpose: 
#   Filters an Outlook contacts format CSV into a CSV for import to OTRS 
#   database table customer_user

# Usage: -h option for help (runs the usage function)
 
# Environment:
#   Developed and tested on Slackware64 13.1 using
#    * Outlook CSV from Google Mail
#    * GNU Awk 3.1.8
#    * OTRS 3.0.8

# History:
#  24jun11 Version 0.0. Charles
#    * Initial version

# TODO: in approx descending priority
#   * TBA

# Programmers' notes:
#   * Global variables (distinguished by an initial upper-case letter):
#     - Debug_level  0 for no debugging. Usage message gives further detail.
#     - Email_field[ ]  Email field names
#     - Email_field_maxidx
#     - Fax_field[ ]  Fax field names
#     - Fax_field_maxidx
#     - Field[]  Field values from the current Outlook CSV record
#     - Header_NF  Number of fields in the Outlook CSV header
#     - My_name  Last component of path by which this script was called
#     - Num_warings  Number of warning messages
#     - Optarg  An option's argument
#     - Opterr  Function get_opt error control; set to 0 to suppress error messages
#     - Optind  Index of the first non-option argument in ARGV
#     - Optopt  An option's letter
#     - Phone_field[ ]  Phone field names
#     - Phone_field_maxidx
#     - Record_num  Input record number
#     - Address_fields_prefix[ ]  Street field name prefixes
#     - Address_fields_prefix_maxidx
#     - Version  This script's version
#   * Global variables for setter's use only (distinguished by an initial _):
#     - _opti  Set by the get_opt() function
#   * Function call tree:
#
#   BEGIN
#   |
#   +-- initialise
#   |   |
#   |   +-- get_opt
#   |   |
#   |   +-- usage
#   |   |
#   |   +-- ck_file
#   |   |
#   |   +-- analyse_header
#   |
#   +-- get_record
#   |   |
#   |   +-- get_field
#   |
#   +-- filter_record
#   |
#   +-- write_record
#   |
#   +-- finalise
#
# Utility functions not listed above
#  camel_case, ck_email, ck_phone, msg, my_getline

# Function definitions in alphabetical order.  Execution begins after the
# last function definition (search for BEGIN).

function analyse_header ( \
    i, field_name )
{
    # Get number of fields from the header
    # (assumed not to contain commas or newlines in the field names)
    FS = ","
    my_getline()
    Header_NF = NF
    msg( "D10", "Header_NF is " Header_NF )

    # Note the field numbers required
    for ( i = 1; i < Header_NF; i++ )
    {
        field_name = gensub( /"/, "", "g", $(i) )
        msg( "D50", "analyse_header: field_name: " field_name )
        if ( field_name == "First Name" )
        {
            Outlook_CSV_field_idx[ "First Name" ] = i
        }
        else if ( field_name == "Middle Name" )
        {
            Outlook_CSV_field_idx[ "Middle Name" ] = i
        }
        else if ( field_name == "Last Name" )
        {
            Outlook_CSV_field_idx[ "Last Name" ] = i
        }
        else if ( field_name == "Title" )
        {
            Outlook_CSV_field_idx[ "Title" ] = i
        }
        else if ( field_name == "E-mail Address" )
        {
            msg( "D50", "analyse_header: E-mail Address is field " i )
            Outlook_CSV_field_idx[ "E-mail Address" ] = i
        }
        else if ( field_name == "E-mail 2 Address" )
        {
            Outlook_CSV_field_idx[ "E-mail 2 Address" ] = i
        }
        else if ( field_name == "E-mail 3 Address" )
        {
            Outlook_CSV_field_idx[ "E-mail 3 Address" ] = i
        }
        else if ( field_name == "Primary Phone" )
        {
            Outlook_CSV_field_idx[ "Primary Phone" ] = i
        }
        else if ( field_name == "Home Phone" )
        {
            Outlook_CSV_field_idx[ "Home Phone" ] = i
        }
        else if ( field_name == "Home Phone 2" )
        {
            Outlook_CSV_field_idx[ "Home Phone 2" ] = i
        }
        else if ( field_name == "Mobile Phone" )
        {
            Outlook_CSV_field_idx[ "Mobile Phone" ] = i
        }
        else if ( field_name == "Home Fax" )
        {
            Outlook_CSV_field_idx[ "Home Fax" ] = i
        }
        else if ( field_name == "Home Address" )
        {
            Outlook_CSV_field_idx[ "Home Address" ] = i
        }
        else if ( field_name == "Home Street" )
        {
            Outlook_CSV_field_idx[ "Home Street" ] = i
        }
        else if ( field_name == "Home Street 2" )
        {
            Outlook_CSV_field_idx[ "Home Street 2" ] = i
        }
        else if ( field_name == "Home Street 3" )
        {
            Outlook_CSV_field_idx[ "Home Street 3" ] = i
        }
        else if ( field_name == "Home City" )
        {
            Outlook_CSV_field_idx[ "Home City" ] = i
        }
        else if ( field_name == "Home Postal Code" )
        {
            Outlook_CSV_field_idx[ "Home Postal Code" ] = i
        }
        else if ( field_name == "Home Country" )
        {
            Outlook_CSV_field_idx[ "Home Country" ] = i
        }
        else if ( field_name == "Company Main Phone" )
        {
            Outlook_CSV_field_idx[ "Company Main Phone" ] = i
        }
        else if ( field_name == "Business Phone" )
        {
            Outlook_CSV_field_idx[ "Business Phone" ] = i
        }
        else if ( field_name == "Business Phone 2" )
        {
            Outlook_CSV_field_idx[ "Business Phone 2" ] = i
        }
        else if ( field_name == "Business Fax" )
        {
            Outlook_CSV_field_idx[ "Business Fax" ] = i
        }
        else if ( field_name == "Company" )
        {
            Outlook_CSV_field_idx[ "Company" ] = i
        }
        else if ( field_name == "Organizational ID Number" )
        {
            Outlook_CSV_field_idx[ "Organizational ID Number" ] = i
        }
        else if ( field_name == "Business Address" )
        {
            Outlook_CSV_field_idx[ "Business Address" ] = i
        }
        else if ( field_name == "Business Street" )
        {
            Outlook_CSV_field_idx[ "Business Street" ] = i
        }
        else if ( field_name == "Business Street 2" )
        {
            Outlook_CSV_field_idx[ "Business Street 2" ] = i
        }
        else if ( field_name == "Business Street 3" )
        {
            Outlook_CSV_field_idx[ "Business Street 3" ] = i
        }
        else if ( field_name == "Business City" )
        {
            Outlook_CSV_field_idx[ "Business City" ] = i
        }
        else if ( field_name == "Business Postal Code" )
        {
            Outlook_CSV_field_idx[ "Business Postal Code" ] = i
        }
        else if ( field_name == "Business Country" )
        {
            Outlook_CSV_field_idx[ "Business Country" ] = i
        }
        else if ( field_name == "Other Phone" )
        {
            Outlook_CSV_field_idx[ "Other Phone" ] = i
        }
        else if ( field_name == "Other Fax" )
        {
            Outlook_CSV_field_idx[ "Other Fax" ] = i
        }
    }

}

function camel_case ( string, \
    array, i, idxmax, my_return )
{
    if ( string ~ /[A-Z]/ ) return string
    idxmax = split( string, array )
    for ( i = 1; i <= idxmax; i++ ) {
        my_return = my_return " " toupper( substr( array[ i ], 1, 1 ) ) tolower( substr( array[ i ], 2 ) )
    }
    return substr( my_return, 2 )
}

function ck_email ( email, field )
{
    msg( "D90", "ck_email: checking: '" email "'" )
    if ( email == "" ) return ""
    # The regular exression range operator {n,m} cannot be used with gawk because
    # it requires a comand line option which cannot be supplied on an awk shebang
    # line; hence the use of two ? operators in the last component below.
    if ( email !~ /^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z][A-Za-z][A-Za-z]?[A-Za-z]?$/ )
    {
        msg( "W", "Record " Record_num ", field " field " invalid; discarded: " email )
        return ""
    }
    if ( length( email ) > DB_email_maxlen )
    {
        msg( "W", "Record " Record_num ", field " field " too long for OTRS; discarded: " email )
        return ""
    }
    return email
}

function ck_file ( file, use, \
    dir, escaped_dir, escaped_file )
{
    escaped_file = gensub( /'/, "'\"'\"'", "g", file )
    if ( use == "input" ) {
        if ( system( "test -r '" escaped_file "'" ) == 0) return "OK"
        if ( system( "test -e '" escaped_file "'" ) == 0) return "does not exist"
        return "is not readable"
    }
    else
    {
        if ( system( "test -w '" escaped_file "'" ) == 0) return "OK"
        if ( system( "test -e '" escaped_file "'" ) == 0) return "not writeble"
        dir = gensub( /\/[^/]*$/, "", "", file )
        escaped_dir = gensub( /'/, "'\"'\"'", "g", dir )
        if ( system( "test -w '" escaped_dir "'" ) == 0) return "OK"
        return "does not exist and cannot be created" 
    }
}

function ck_phone ( phone, field, \
    invalid_chars, len, s )
{
    # Validate characters
    # TODO: make this configurable
    invalid_chars = gensub( /[-+ ()0-9]\//, " ", "g", phone )
    len = length( invalid_chars )
    if ( len > 0 ) {
        if ( len > 1 ) s = "s"
        msg( "W", "Invalid character" s " in record " Record_num ", field " field ": " \
             invalid_chars "; phone number discarded" )     
        return ""
    }

    # Normalise
    # TODO: make this configurable
    phone = gensub( /[^+ ()0-9]/, " ", "g", phone )
    phone = gensub( /  /, " ", "g", phone )
    phone = gensub( /^ /, "", "", phone )
    phone = gensub( / $/, "", "", phone )

    # No number
    if ( phone == "" ) return ""

    # Validate format
    # TODO: make this configurable
    if ( phone != /^(+[1-9][0-9]+ ?)?[0-9 ]*(\([0-9]*\))? ?[0-9 ]*$/ ) {
        msg( "W", "Invalid phone number format in record " Record_num ", field " field ": " \
             phone " (after normalisation); discarded" )     
        return ""
    }

    # Check length
    if ( length( phone ) > DB_phone_maxlen ) {
        msg( "W", "Phone number too long for OTRS in record " Record_num ", field " field ": " \
             phone " (after normalisation); discarded" )     
        return ""
    }

    return phone
}

function filter_record ( \
    array, change_by, change_time, city, country, create_by, create_time, \
    customer_id, data, email, fax, first_name, first_name_part, i, idxmax, \
    last_name, last_name_part, login, prefix, mobile, phone, \
    postal_address_found, state, street, title, valid_id, zip )
{
    # Address fields
    # These are processed together because a consistent set is required

    # street
    postal_address_found = ""
    street = ""
    for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
        prefix = Address_fields_prefix[ i ]
        street = Field[ Outlook_CSV_field_idx[ prefix " Street" ] ]
        if ( street == "" ) continue
        postal_address_found = prefix
        data = Field[ Outlook_CSV_field_idx[ prefix " Street 2" ] ]
        if ( data == "" ) break
        street = street ", " data
        data = Field[ Outlook_CSV_field_idx[ prefix " Street 3" ] ]
        if ( data == "" ) break
        street = street ", " data
        break
    }
    if ( postal_address_found ) {
        state = Field[ Outlook_CSV_field_idx[ prefix " State" ] ]
        if ( state != "" ) street = street ", " state
    }
    if ( length( street ) > DB_street_maxlen ) {
        street = substr( street, 1, DB_street_maxlen )
        msg( "W", "Record " Record_num ": Street truncated for OTRS: '" street "'" )
    }

    # zip 
    if ( postal_address_found ) {
        zip = Field[ Outlook_CSV_field_idx[ prefix "Postal Code" ] ]
    }
    else {
        for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
            prefix = Address_fields_prefix[ i ]
            zip = Field[ Outlook_CSV_field_idx[ prefix " Postal Code" ] ]
            if ( zip == "" ) continue
            postal_address_found = prefix
            break
        }
    }
    if ( length( zip ) > DB_zip_maxlen ) {
        zip = substr( zip, 1, DB_zip_maxlen )
        msg( "W", "Record " Record_num ": Postal Code truncated for OTRS: '" zip "'" )
    }

    # city 
    if ( postal_address_found ) {
        city = Field[ Outlook_CSV_field_idx[ prefix " City" ] ]
    }
    else {
        for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
            prefix = Address_fields_prefix[ i ]
            city = Field[ Outlook_CSV_field_idx[ prefix " City" ] ]
            if ( city == "" ) continue
            postal_address_found = prefix
            break
        }
    }
    city = camel_case( city )
    if ( length( city ) > DB_city_maxlen ) {
        city = substr( city, 1, DB_city_maxlen )
        msg( "W", "Record " Record_num ": City truncated for OTRS: '" city "'" )
    }
    
    # country 
    if ( postal_address_found ) {
        country = Field[ Outlook_CSV_field_idx[ prefix " Country" ] ]
    }
    else {
        for ( i = 1; i <= Address_fields_prefix_maxidx; i++ ) {
            prefix = Address_fields_prefix[ i ]
            country = Field[ Outlook_CSV_field_idx[ prefix " Country" ] ]
            if ( country == "" ) continue
            postal_address_found = prefix
            break
        }
    }
    country = camel_case( country )
    if ( length( country ) > DB_country_maxlen ) {
        country = substr( country, 1, DB_country_maxlen )
        msg( "W", "Record " Record_num ": Country truncated for OTRS: '" country "'" )
    }
    
    # The remaining fields (OTRS names) in alpahbetical order

    # change_by (required)
    change_by = 1

    # change_time (required)
    change_time = OTRS_time

    # create_by (required)
    create_by = 1

    # create_time (required)
    create_time = OTRS_time

    # customer_id 
    # No eqivalent Outlook field; leave empty
    customer_id = ""

    # email (required)
    for ( i = 1; i <= Email_field_maxidx; i++ ) {
        email = Field[ Outlook_CSV_field_idx[ Email_field[ i ] ] ]
        email = ck_email( email, Email_field[ i ] )
        if ( email != "" ) break
    }
    if ( email == "" ) {
        msg( "W", "Record " Record_num ": no Email address; record discarded" )
        return ""
    }

    # fax 
    for ( i = 1; i <= Fax_field_maxidx; i++ ) {
        fax = Field[ Outlook_CSV_field_idx[ Fax_field[ i ] ] ]
        fax = ck_phone( fax, Fax_field[ i ] )
        if ( fax != "" ) break
    }
    
    # first_name (required)
    first_name = Field[ Outlook_CSV_field_idx[ "First Name" ] ]
    if ( first_name != "" ) first_name = camel_case( first_name )
    else {
        msg( "W", "Record " Record_num " (" email "): no First Name" )
        # Fudge a First Name from any email address
        if ( email == "" ) {
            msg( "W", "... and no valid email address to fudge a First Name from; record discarded" )
            return ""
        }
        split( email, array, /[^[:alpha:]]/ )        
        first_name = camel_case( array[ 1 ] ) " (fudged from email address)"
        msg( "W", "... set to " first_name )
    }
    if ( length( first_name ) > DB_first_name_maxlen ) {
        first_name = substr( first_name, 1, DB_first_name_maxlen )
        msg( "W", "Record " Record_num ": First Name truncated for OTRS: '" first_name "'" )
    }

    # last_name (required )
    last_name = Field[ Outlook_CSV_field_idx[ "Last Name" ] ]
    if ( last_name != "" ) {
        last_name = camel_case( last_name )
        if ( length( last_name ) > DB_last_name_maxlen ) {
            last_name = substr( last_name, 1, DB_last_name_maxlen )
            msg( "W", "Record " Record_num ": Last Name truncated for OTRS: '" last_name "'" )
        }
    }
    else {
        last_name = "."
        msg( "W", "Record " Record_num " (" email "): no Last Name; set to ." )
    }

    # login (required, must be unique)
    # TODO: make this configurable
    first_name_part = tolower( gensub( / \(fudged from email address\)/, "", "", first_name ) )
    if ( last_name != "." ) {
        last_name_part = "_" tolower( gensub( / \(fudged from email address\)/, "", "", last_name ) )
    }
    else last_name_part = ""
    login = first_name_part last_name_part Uniquing_no++

    # phone
    for ( i = 1; i <= Phone_field_maxidx; i++ ) {
        phone = Field[ Outlook_CSV_field_idx[ Phone_field[ i ] ] ]
        phone = ck_phone( phone, Phone_field[ i ] )
        if ( phone != "" ) break
    }

    # mobile
    mobile = Field[ Outlook_CSV_field_idx[ "Mobile Phone" ] ]
    mobile = ck_phone( mobile, "Mobile Phone" )

    # title 
    title = Field[ Outlook_CSV_field_idx[ "Title" ] ]
    title = camel_case( title )
    if ( length( title ) > DB_title_maxlen ) {
        title = substr( title, 1, DB_title_maxlen )
        msg( "W", "Record " Record_num ": Title truncated for OTRS: '" title "'" )
    }
    
    # valid_id (required)
    valid_id = 1

    return change_by ",\"" change_time "\",\"" city "\",\"" country "\"," \
		create_by ",\"" create_time "\",\"" customer_id "\",\"" \
		email "\",\"" fax "\",\"" first_name "\",\"" last_name "\",\"" \
		login "\",\"" phone "\",\"" mobile "\",\"" street "\",\"" \
		title "\"," valid_id ",\"" zip "\""
}

function get_field ( n_field )
{
    msg( "D100", "Started get_field()" )
    Field[ n_field ] = ""
    if ( length( $0 ) == 0 ) { 
        msg( "D100", "Setting EoR empty field" )
        return
    }
    char = substr( $0, 1, 1 )
    msg( "D100", "Character is " char )
    if ( char == "," ) {
        $0 = substr( $0, 2 )
        return
    }
    if ( char != "\"" ) {
        msg( "E", "Line " Record_num ": char is '" char "' not \" as expected" )
        exit 1
    }
    for ( i = 2; ; i++ )
    {
        if ( length( $0 ) == i - 1 ) { 
            my_getline() 
            i = 1
            Field[ n_field ] = Field[ n_field ] "\\n"
            if ( length( $0 ) == 0 ) continue
        }
        char = substr( $0, i, 1 )
        msg( "D100", "Character is " char )
        if ( char == "\"" )
        {
            if ( length( $0 ) > i ) {
                next_char = substr( $0, i + 1, 1 )
                if ( next_char == "," ) {
                    # End of field; discard following "," and return
                    $0 = substr( $0, i + 2 )
                    msg( "D100", "Field " n_field " is " Field[ n_field ] )
                    return
                }
                else if (next_char == "\"" ) {
                    # Is doubled " which is an embedded "
                    i++
                }
                else {
                    msg( "E", \
                        "Line " Record_num ": char is '" char "' not \" as expected" )
                }
            }
            else {
                # End of field and end of record
                $0 = ""
                msg( "D100", "Field " n_field " is " Field[ n_field ] )
                return
            }
        }
        Field[ n_field ] = Field[ n_field ] char
    }
    
}

function finalise ( \
    now )
{
	# TODO: why does the following line work in initialise() but not here?!
    #"date '+%Y/%m/%d@%H:%M:%S'" | getline now
    "date +%Y/%m/%d@%H:%M:%S" | getline now
    msg( "I", "Finishing at " now )
    if ( Num_warnings > 0 ) msg( "E", "There were " Num_warnings " warning messages" )
    msg( "I", "There were " Num_warnings " warning messages" )
}

function get_opt(argc, argv, options, \
    thisopt, i)
{
    # From http://www.gnu.org/software/gawk/manual/gawk.html#Getopt-Function
    # Modified to return option letter of any invalid option instead of ?
    if (length(options) == 0)    # no options given
        return -1
     
    if (argv[Optind] == "--") {  # all done
        Optind++
        _opti = 0
        return -1
    } else if (argv[Optind] !~ /^-[^: \t\n\f\r\v\b]/) {
        _opti = 0
        return -1
    }
    if (_opti == 0)
        _opti = 2
    thisopt = substr(argv[Optind], _opti, 1)
    Optopt = thisopt
    i = index(options, thisopt)
    if (i == 0) {
        if (Opterr)
           printf("%c -- invalid option\n",
               thisopt) > "/dev/stderr"
        if (_opti >= length(argv[Optind])) {
           Optind++
           _opti = 0
        } else
            _opti++
        # Local modification
        #return "?"
        return thisopt
    }
    if (substr(options, i + 1, 1) == ":") {
        # get option argument
        if (length(substr(argv[Optind], _opti + 1)) > 0)
           Optarg = substr(argv[Optind], _opti + 1)
        else
           Optarg = argv[++Optind]
        _opti = 0
    } else
        Optarg = ""
    if (_opti == 0 || _opti >= length(argv[Optind])) {
        Optind++
        _opti = 0
    } else
        _opti++
    return thisopt
}

function get_record ( \
    n_field )
{
    n_field = 1
    while ( n_field < Header_NF ) {
        get_field( n_field )
        msg( "D90", "Record " Record_num ", field " n_field ": " Field[ n_field] )
        n_field++
    }
}

function initialise ( \
    array, cmdline, errmsg, i, now, msg, opt )
{
    Version = "0.0"

    # Get last component of path by which this script called
    # Has to be done now in case needed for the -h help option
    # /proc file system used because ARGV[0] value is system-dependent
    getline cmdline < "/proc/self/cmdline"
    split( cmdline, array, "\0" )
    My_name = substr( array[3], match( array[3], /\/[^\/]*$/ ) + 1 )

    # Parse command line
    Opterr = 0    # Suppress get_opt( ) error messages
    Optind = 1    # Skip ARGV[0]
    Debug_level = 0
    Input_file = "/dev/stdin"
    Log_file = "/dev/stderr"
    Output_file = "/dev/stdout"
    while ( ( opt = get_opt(ARGC, ARGV, "d:hi:l:o:u:V")) != -1)
    {
        msg( "D70", "initialise: opt: " opt )
        if ( opt == "d" ) {
            Debug_level = strtonum( Optarg )
        }
        else if ( opt == "h" ) {
            usage( "verbose" )
            exit 0
        }
        else if ( opt == "i" ) {
            Input_file = Optarg
        }
        else if ( opt == "l" ) {
            Log_file = Optarg
        }
        else if ( opt == "o" ) {
            Output_file = Optarg
        }
        else if ( opt == "u" ) {
            Uniquing_no = strtonum( Optarg )
        }
        else if ( opt == "V" ) {
            msg( "I", My_name " version " Version )
            exit 0
        }
        else {
            errmsg = "\n    Invalid command line option: " opt 
            show_usage = "true"
        }
    }
    for ( i = 1; i < Optind; i++ ) ARGV[i] = ""

    # Initialise logging
    Num_warnings = 0
    if ( Log_file != "/dev/stderr" ) {
        msg = ck_file( Log_file, "output" )
        if ( msg == "OK" ) printf "" > Log_file
        else {
            errmsg = "\n    Log file " msg ": " Log_file
            Log_file = "/dev/stderr"
        }
    }
    "date '+%Y/%m/%d@%H:%M:%S'" | getline now
    cmdline = substr( cmdline, length( array[ 1 ] ) + length( array[ 2 ] ) + 3 )
    gsub( "\0", " ", cmdline )
    msg( "I", "Started version " Version " at " now " with command line " cmdline )

    # Check input and output files
    if ( Input_file != "/dev/stdin" ) {
        msg = ck_file( Input_file, "input" )
        if ( msg != "OK" ) errmsg = "\n    Input file " msg ": " Input_file
    }
    if ( Output_file != "/dev/stdout" ) {
        msg = ck_file( Output_file, "output" )
        if ( msg != "OK" ) errmsg = "\n    Output file " msg ": " Output_file
    }

    # Trap mandatory argument not given
    if ( Uniquing_no == "" ) {
        errmsg = "\n    Mandatory option -u not given or had an empty argument"
    }
    
    # Report any errors
    if ( errmsg != "" ) {
        if ( show_usage ) usage( "quiet" )
        msg( "E", substr( errmsg, 2 ) )
    }

    # OTRS DB table customer_user
    # (output of MySQL show columns from customer_user; )
    # +-------------+--------------+------+-----+---------+----------------+
    # | Field       | Type         | Null | Key | Default | Extra          |
    # +-------------+--------------+------+-----+---------+----------------+
    # | id          | int(11)      | NO   | PRI | NULL    | auto_increment |
    # | login       | varchar(200) | NO   | UNI | NULL    |                |
    # | email       | varchar(150) | NO   |     | NULL    |                |
    # | customer_id | varchar(150) | NO   |     | NULL    |                |
    # | pw          | varchar(64)  | YES  |     | NULL    |                |
    # | title       | varchar(50)  | YES  |     | NULL    |                |
    # | first_name  | varchar(100) | NO   |     | NULL    |                |
    # | last_name   | varchar(100) | NO   |     | NULL    |                |
    # | phone       | varchar(150) | YES  |     | NULL    |                |
    # | fax         | varchar(150) | YES  |     | NULL    |                |
    # | mobile      | varchar(150) | YES  |     | NULL    |                |
    # | street      | varchar(150) | YES  |     | NULL    |                |
    # | zip         | varchar(200) | YES  |     | NULL    |                |
    # | city        | varchar(200) | YES  |     | NULL    |                |
    # | country     | varchar(200) | YES  |     | NULL    |                |
    # | comments    | varchar(250) | YES  |     | NULL    |                |
    # | valid_id    | smallint(6)  | NO   | MUL | NULL    |                |
    # | create_time | datetime     | NO   |     | NULL    |                |
    # | create_by   | int(11)      | NO   | MUL | NULL    |                |
    # | change_time | datetime     | NO   |     | NULL    |                |
    # | change_by   | int(11)      | NO   | MUL | NULL    |                |
    # +-------------+--------------+------+-----+---------+----------------+

    # Set max lengths for columns that may be populated from Outlook CSV
    DB_email_maxlen=150
    DB_title_maxlen=50
    DB_first_name_maxlen=100
    DB_last_name_maxlen=100
    DB_phone_maxlen=150
    DB_fax_maxlen=150
    DB_mobile_maxlen=150
    DB_street_maxlen=150
    DB_zip_maxlen=200
    DB_city_maxlen=200
    DB_country_maxlen=200
    DB_comments_maxlen=100

    # Note numbers of Outlook CSV fields that may contain required data
    analyse_header( )

    # Email fields
    # The first listed which has a value in the input CSV is the one used.
    i = 0
    Email_field[ ++i ] = "E-mail Address"
    Email_field[ ++i ] = "E-mail 2 Address"
    Email_field[ ++i ] = "E-mail 3 Address"
    Email_field_maxidx = i

    # Fax number fields
    # The first listed which has a value in the input CSV is the one used.
    i = 0
    Fax_field[ ++i ] = "Home Fax"
    Fax_field[ ++i ] = "Business Fax"
    Fax_field[ ++i ] = "Other Fax"
    Fax_field_maxidx = i

    # Phone number fields
    # The first listed which has a value in the input CSV is the one used.
    # Mobile Phone should be last because it is itself an OTRS field.
    # Car Phone should probably be second last because few people now have a 
    # car phone that is not their mobile number
    i = 0
    Phone_field[ ++i ] = "Primary Phone"
    Phone_field[ ++i ] = "Home Phone"
    Phone_field[ ++i ] = "Business Phone"
    Phone_field[ ++i ] = "Other Phone"
    Phone_field[ ++i ] = "Home Phone 2"
    Phone_field[ ++i ] = "Business Phone 2"
    Phone_field[ ++i ] = "Assistant's Phone"
    Phone_field[ ++i ] = "Company Main Phone"
    Phone_field[ ++i ] = "Radio Phone"
    Phone_field[ ++i ] = "TTY/TDD Phone"
    Phone_field[ ++i ] = "Car Phone"
    Phone_field[ ++i ] = "Mobile Phone"
    Phone_field_maxidx = i

    # Address fields prefixes
    # Outlook CSVs have three sets of fields for postal address data; OTRS has one
    i = 0
    Address_fields_prefix[ ++i ] = "Home"
    Address_fields_prefix[ ++i ] = "Business"
    Address_fields_prefix[ ++i ] = "Other"
    Street_field_maxidx = i

    # Generate OTRS change_time and modify_time string
    # Format is as displayed by MySQL for datetimes
    "date '+%Y-%m-%d %H:%M:%S'" | getline OTRS_time
}

function msg ( msg_class, msg_text,
    exit_flag, msg_debug_level )
{
    exit_flag = 0
    if ( msg_class ~ /^D/ )
    {
        if ( ! Debug_level ) return
        if ( length( msg_class ) > 1 ) 
            msg_debug_level = strtonum( substr( msg_class, 2 ) )
        else msg_debug_level = 10 # Sane default
        if ( msg_debug_level > Debug_level ) return
        msg_text = "DEBUG: " msg_text
    }
    else if ( msg_class == "I" )
    {
    }
    else if ( msg_class == "W" )
    {
        msg_text = "WARNING: " msg_text
        Num_warnings++
    }
    else if ( msg_class == "E" )
    {
        msg_text = "ERROR: " msg_text
        exit_flag = 1
    }
    else
    {
        msg_text =  "ERROR: msg: called with invalid message class " msg_class \
            "(and message text: " msg_text " )"
        exit_flag = 1
    }
    print msg_text >> Log_file
    if ( exit_flag ) exit 1
}

function my_getline ()
{
    if ( ( getline < Input_file ) <= 0 ) { 
        msg( "E", "Unexpected end of file or error: " ERRNO )
    } 
    Record_num++
}

function usage ( verbosity )
{
    msg( "I", "Usage:" \
        "\n  " My_name " [-d debug_level] [-h] [-i file] [-l file] [-o file] [-V] [-v] u number" \
    )
    if ( verbosity == "verbose" )
        msg( "I", "  Where:" \
            "\n    -d sets the debug level" \
            "\n       1 to 100 gives progressively more detail" \
            "\n    -h prints this help message and exits" \
            "\n    -i specifies the input file (default stdin)" \
            "\n    -l specifies the log file (default stderr)" \
            "\n    -o specifies the output file (default stdout)" \
            "\n    -u specifies the uniquing number to be used for the login field" \
            "\n       To enable loading data to OTRS, must result in a unique login value." \
            "\n       The generated login value is First Name + underscore + Last Name + incremented uniquing number." \
            "\n    -v sets all alarms to visual" \
            "\n    -V prints the version number and exits" \
        )
    else msg( "I", "(-h for help)" )
}

function write_record ( output )
{
    print output >> Output_file
}

BEGIN {
    initialise()

    # Write output header
    print "\"change_by\",\"change_time\",\"city\",\"country\",\"create_by\"" \
        ",\"create_time\",\"customer_id\",\"email\",\"fax\",\"first_name\"" \
        ",\"last_name\",\"login\",\"phone\",\"mobile\",\"street\",\"title\"" \
        ",\"valid_id\",\"zip\"" > Output_file

    # Loop over all lines in Outlook CSV
    while ( ( getline < Input_file ) > 0 ) {
        Record_num++
        #if ( Record_num > 5 ) exit 0
        msg( "D10", "Processing record " Record_num )
        get_record()
        Output = filter_record()
        if ( Output ) write_record( Output )
    }

    # Clean up and get out of here
    finalise()
}