"""
Any table to txt file
"""


def xls_to_txt(inXls, delimiter, outTxt, en_coding='utf-8'):
    """
    Excel to Text File
    
    Create a new txt table which columns are delimited by a delimiter specfified
    by the user.
    """
    
    def sanitize(v):
        if type(v) == type(10.0):
            if v == int(v):
                return unicode(int(v))
            else:
                return unicode(v)
        else:
            return unicode(v)
    
    
    import xlrd
    import codecs
    
    
    # Open input
    xls_data = xlrd.open_workbook(inXls)
    sheets = xls_data.sheet_names()
    sheet = xls_data.sheet_by_name(sheets[0])
    # Create output
    with codecs.open(outTxt, 'w', encoding=en_coding) as f:
        for row in range(sheet.nrows):
            l = [sheet.cell(row, col).value for col in range(sheet.ncols)]
            for i in range(len(l)):
                if i+1 == len(l):
                    f.write(sanitize(l[i]) + '\n')
                else:
                    f.write(sanitize(l[i]) + delimiter)
        f.close()


def pgsql_to_txt(pg_table, out_txt, delimiter, where_clause=None,
                 _encoding='utf-8',
                 dic_con={
                     'HOST': 'localhost', 'PASSWORD': 'admin',
                     'USER': 'postgres', 'DATABASE': 'matuc_db', 'PORT': '5432'
                     }):
    """
    Method to export data from PostgreSQL to a text file
    """
    
    def sanitize(v):
        if type(v) == type(10.0):
            if v == int(v):
                return unicode(str(int(v)), _encoding)
            else:
                return unicode(str(v), _encoding)
        else:
            return unicode(str(v), _encoding)    

    import os
    import codecs
    
    from gasp.frompsql     import sql_query
    from gasp.pgsql.fields import get_columns_name

    # Delete output file if exists
    if os.path.exists(out_txt):
        os.remove(out_txt)

    # Get columns names
    cols_name = get_columns_name(dic_con, pg_table)

    # Get data from postgresql
    if where_clause != None:
        table_data = sql_query(
            dic_con,
            "SELECT {col_s} FROM {tbl} WHERE {whr};".format(
                col_s=', '.join(cols_name),
                tbl=pg_table, whr=where_clause
            )
        )

    else:
        table_data = sql_query(
            dic_con,
            "SELECT {col_s} FROM {tbl};".format(
                col_s=', '.join(cols_name), tbl=pg_table
            )
        )

    # Create txt file
    with codecs.open(out_txt, 'w', encoding=_encoding) as txt:
        for i in range(len(cols_name)):
            if i+1 == len(cols_name):
                txt.write(sanitize(cols_name[i]) + '\n')
            else:
                txt.write(sanitize(cols_name[i]) + delimiter)

        for row in table_data:
            l = [value for value in row]

            for i in range(len(cols_name)):
                if i+1 == len(cols_name):
                    txt.write(sanitize(l[i]) + '\n')
                else:
                    txt.write(sanitize(l[i]) + delimiter)
        txt.close()


def pgsqldb_to_txt(delimiter, workspace, __conPSQL, encoding='utf-8'):
    """
    Execute pgsql_to_txt for all tables in a database
    """
    
    import os
    
    from gasp.pgsql.tables import list_tables
    
    tables = list_tables(__conPSQL)
    
    for table in tables:
        pgsql_to_txt(
            table,
            os.path.join(workspace, '{}.txt'.format(table)),
            delimiter, _encoding=encoding,
            dic_con=__conPSQL
        )


def dbf_to_txt(dbf, delimiter, out_txt, encoding='utf-8'):
    """
    Export dbf table to a txt file
    """
    
    import codecs
    import os
    from osgeo                   import ogr
    from gasp.gdal               import get_driver_name
    from gasp.gdal.manage.fields import ogr_list_fields
    
    def sanitize(v):
        if type(v) == float:
            if v == int(v):
                return unicode(str(int(v)), "utf-8")
            else:
                return unicode(str(v), "utf-8")
        elif type(v) == int:
            return unicode(str(v), "utf-8")
        else:
            return unicode(v, "utf-8")
    
    if os.path.exists(dbf):
        if os.path.splitext(dbf)[1] != '.dbf':
            raise ValueError('The specified file is not a dbf file')
    
    else:
        raise ValueError('The specified file does not exist')
    
    # Open input
    table = ogr.GetDriverByName("ESRI Shapefile").Open(dbf, 0)
    
    viewTable = table.GetLayer()
    
    cols = ogr_list_fields(dbf)
    
    # Create txt file
    with codecs.open(out_txt, 'w', encoding=encoding) as txt:
        for i in range(len(cols)):
            if i+1 == len(cols):
                txt.write(sanitize(cols[i]) + '\n')
            else:
                txt.write(sanitize(cols[i]) + delimiter)
        
        for row in viewTable:
            for i in range(len(cols)):
                value = row.GetField(cols[i])
                if i+1 == len(cols):
                    txt.write(sanitize(value) + '\n')
                else:
                    txt.write(sanitize(value) + delimiter)
        
        txt.close()

