import bots.botslib as botslib
import bots.transform as transform
from bots.botsconfig import *
import pyodbc
from StringIO import StringIO
import csv


def connect(channeldict):

    dbHost = channeldict['host']
    dbUser = channeldict['username']
    dbPass = channeldict['secret']
    dbName = channeldict['path']

    return pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+dbHost+';DATABASE='+dbName+';UID='+dbUser+';PWD='+ dbPass)


def incommunicate(channeldict,dbconnection):
    cursor = dbconnection.cursor()
    last_id = '0'
    loop = 1
    results = list()

    while loop > 0:
        cursor.execute('''
                    SELECT TOP 1 'HEA',
                    [Outbound Document No_],
                    [No_]
                    FROM [dbo].[EDI Outgoing Sales Header] 
                    WHERE [Status] = 0 
                    AND [Outbound Document No_] > \''''+last_id+'''\' ''')
        headers = cursor.fetchall()
        if not headers:
            loop = 0

        for header in headers:
            results.append(header)
            row_id = str(header[1])
            cursor.execute(''' SELECT 'LIN',
                            [Outbound Document No_],
                            [EAN Item No_]
                            FROM [dbo].[EDI Outgoing Sales line]
                            WHERE [Outbound Document No_] = \''''+row_id+'''\' ''')
            last_id = row_id

            lines = cursor.fetchall()
            for line in lines:
                results.append(line)
    dbconnection.commit()
    cursor.close()

    output = StringIO()
    csvwriter = csv.writer(output, delimiter=';', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    for result in results:
        csvwriter.writerow(result)


    ta_to = botslib.NewTransaction(filename='test_ordrsp_sql2csv',
                                   status=FILEIN,
                                    editype='csv',
                                    frompartner='12345678',
                                    messagetype='test_ordrsp_sql2csv')
    tofilename = unicode(ta_to.idta)
    tofile = botslib.opendata(tofilename,'wb')
    tofile.write(output.getvalue())
    tofile.close()
    ta_to.update(statust=OK,filename=tofilename)


    return results

def disconnect(channeldict,dbconnection):
    dbconnection.close()