import pandas as pd
import configparser
import pymssql

config = configparser.ConfigParser()
config.read('settings.txt')

conn = pymssql.connect(server=config.get('Connection', 'server'),
                       user=config.get('Connection', 'user'),
                       password=config.get('Connection', 'password'),
                       database=config.get('Connection', 'database'))
print('Connection established')

core = pd.DataFrame(columns=['Supplier reference',
                             'Customer PO',
                             'No',
                             'Cross Reference',
                             'Code',
                             'Description',
                             'Quantity',
                             'Unit of Measure',
                             'Plant',
                             'Storage location',
                             'Price, $',
                             'Batch',
                             'Amount, $',
                             'Country of Origin PANIN'])

query = config.get('Queries', 'reg_orders')
df = pd.read_sql(query, conn)
df['No'] = (df.groupby('TaskId').cumcount() + 1) * 10

orders_list = df['TaskId'].unique().tolist()
print('Total orders: {}'.format(len(orders_list)))

for i in orders_list:
    temp_df = df.loc[df['TaskId'] == i]
    file_name = '{}{}{}{}_{}.xlsx'.format(
        config.get('Destination', 'output_path'),
        temp_df.get_value(0, 'OrderName'),
        temp_df.get_value(0, 'RecepientDescription'),
        temp_df.get_value(0, 'IssuerDescription'),
        temp_df.get_value(0, 'IssuerPlant'))
    core['No'], core['Code'] = temp_df['No'], temp_df['Material']
    core['Quantity'], core['Plant'] = temp_df['Q'], temp_df['IssuerPlant']
    core['Storage location'] = temp_df['StLoc']
    writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
    core.to_excel(writer, sheet_name='Sheet1', index=False)
    writer.save()