''#!/usr/bin/python
# coding=utf-8
import pandas as pd
import datetime as dt
import getpass
import requests


def main():
    '''
    Main function

    '''


    def get_from_snow(pwd,user,url):
        '''
        Function to get data via Service-Now API. Results in Pandas DataFrame.

        '''

        headers = {'Accept':'application/json'}
        response = requests.get(url, auth=(user, pwd), headers=headers)

        if response.status_code == 401:

            print ('Error in API call:' + str(response.status_code))
            print ('Invalid credentials.')

            input('Press enter to exit.')
            exit()

        if response.status_code == 403:

            return('Security category')

        if response.status_code != 200:

            print ('Error in API call: ' + str(response.status_code))

            input('Press enter to exit.')
            exit()

        json = response.json()
        data = pd.DataFrame(json['result'])

        return data


    def get_json_from_snow(pwd,user,url):
        '''
        Function to get data via Service-Now API. Results in JSON.

        '''

        headers = {'Accept':'application/json'}
        response = requests.get(url, auth=(user, pwd), headers=headers)

        if response.status_code == 401:

            print ('Error in API call:' + str(response.status_code))
            print ('Invalid credentials.')

            input('Press enter to exit.')
            exit()

        if response.status_code == 403:

            return('Security category')

        if response.status_code != 200:

            print ('Error in API call: ' + str(response.status_code))

            input('Press enter to exit.')
            exit()

        json = response.json()
        data = json['result']

        return data


    def close_inc(pwd,user,sysid):
        '''
        Function to close incident from dynamo

        '''

        url = ('https://dynamo.appelsiini.com/api/now/v2/table/incident/'
            + sysid
            + '?sysparm_fields=number,incident_state,close_notes')
        headers = {'Content-Type':'application/json','Accept':'application/json'}

        response = requests.put(
            url,
            auth=(user, pwd),
            headers=headers,
            data="{'incident_state':6,'close_code':'Cancelled','close_notes':'Suljettu massana 29.1.2018 liittyen ongelmaan PRBYLE0041311. Ratkaisuviesti Tiksussa.'}")

        if response.status_code != 200:

            print(
                'Status:',
                response.status_code,
                'Headers:',
                response.headers,
                'Error Response:',
                response.json()
            )

            exit()

        json = response.json()
        data = json['result']

        print('Incident ' + data['number'] + ' resolved!')


    def get_false_open_valionow_incidents(dynamo_list,valionow_list):
        '''
        Function to get open Tiksu incidents that are not open in Dynamo

        '''

        false_open = valionow_list[
            ~valionow_list['Dynamo numero'].isin(dynamo_list['Dynamo numero'])
        ]

        return false_open


    def get_false_open_dynamo_incidents(dynamo_list,valionow_list):
        '''
        Function to get open Tiksu incidents that are not open in Dynamo

        '''

        false_open = dynamo_list[
            ~dynamo_list['Dynamo numero'].isin(valionow_list['Dynamo numero'])
        ]

        return false_open


    def sysid_to_name(group):
        '''
        Function to convert Assignment group sys_id to name

        '''

        if group:

            group = group['display_value']

            return group

        else:

            pass


    def validate_close_note(inc_number):
        '''
        Function to check if close note has XML escape character

        '''

        url = ('https://yle.service-now.com/api/now/v2/table/'
            + 'incident?'
            + 'sysparm_query='
            + 'number='
            + inc_number
            + '&sysparm_fields='
            + 'close_notes')

        close_note = get_json_from_snow(tiksu_pwd,tiksu_user,url)

        if close_note:

            data = close_note[0]['close_notes']

            if '&' in data:

                return('&')

            elif '<' and '>' in data:

                return('<>')

            else:

                return('')

        else:

            pass


    def get_dynamo_duplicate(row):
        '''
        Function to check if false open Dynamo incident has duplicate

        '''

        url = ('https://valio.service-now.com/api/now/v2/table/'
            + 'incident?'
            + 'sysparm_query='
            + 'number='
            + row.loc['Valio NOW numero']
            + '&sysparm_fields='
            + 'u_external_id')

        valionow_dynamo_id = get_json_from_snow(valionow_pwd,valionow_user,url)

        if valionow_dynamo_id == 'Security category':

            return('Security category')

        else:

            if valionow_dynamo_id[0]['correlation_id'] != row.loc['Dynamo numero']:

                return valionow_dynamo_id[0]['correlation_id']

            else:

                return('')


    def get_tiksu_duplicate(row):
        '''
        Function to check if false open Tiksu incident has duplicate

        '''

        url = ('https://dynamo.appelsiini.com/api/now/v2/table/'
            + 'incident?'
            + 'sysparm_query='
            + 'number='
            + row.loc['Dynamo numero']
            + '&sysparm_fields='
            + 'u_tiksu_id')

        dynamo_valionow_id = get_json_from_snow(dynamo_pwd,dynamo_user,url)

        if dynamo_valionow_id == 'Security category':

            return('Security category')

        else:

            if dynamo_valionow_id[0]['correlation_id'] != row.loc['Valio NOW numero']:

                return dynamo_valionow_id[0]['correlation_id']

            else:

                return('')


    def save_to_excel (all_incs,false_tiksu_incs,false_dynamo_incs,filename,datestamp):
        '''
        Function to save to .xlsx file

        '''


        writer = pd.ExcelWriter(
            filename,
            engine='xlsxwriter',
            # datetime_format='dd.mm.yyyy hh:mm:ss'
        )


        all_incs.to_excel(
            writer,
            sheet_name='Tiksu ja Valio NOW avoimet',
            columns=[
                'Valio NOW palvelujono',
                'Valio NOW tila',
                'Valio NOW numero',
                'Valio NOW prioriteetti',
                'Valio NOW luotu',
                'Dynamo numero',
                'Dynamo palvelujono',
                'Dynamo tila',
                'Dynamo prioriteetti',
                'Dynamo luotu'
            ]
        )


        false_tiksu_incs.to_excel(
            writer,
            sheet_name='Valio NOW virheellisesti avoimet',
            columns=[
                'Valio NOW palvelujono',
                'Valio NOW tila',
                'Valio NOW numero',
                'Valio NOW SysID',
                'Valio NOW prioriteetti',
                'Valio NOW luotu',
                'Dynamo numero',
                'Duplikaatti'
            ]
        )


        false_dynamo_incs.to_excel(
            writer,
            sheet_name='Dynamo virheellisesti avoimet',
            columns=[
                'Dynamo palvelujono',
                'Dynamo tila',
                'Dynamo numero',
                'Dynamo SysID',
                'Dynamo prioriteetti',
                'Dynamo luotu',
                'Valio NOW numero',
                'Duplikaatti'
            ]
        )


        try:

            writer.save()

            print('List saved to file: ' + filename)


        except IOError:

            print('Error: Permission denied when saving.')


    '''
    SCRIPT AND USER INPUT START

    '''


    # Get open incidents from dynamo
    print('Enter Dynamo username and password:')

    dynamo_user = input('Username: ')
    dynamo_pwd = getpass.getpass()
    dynamo_inc_query = (
        'https://dynamo.appelsiini.com/api/now/v2/table/'
        + 'incident?'
        + 'sysparm_query='
        + 'company=90a01e350ac8960e002782f991785d0a'
        + '^incident_stateNOT IN6,7'
        + '^correlation_idISNOTEMPTY'
        + '&sysparm_fields='
        + 'number,'
        + 'correlation_id,'
        + 'assignment_group,'
        + 'incident_state,'
        + 'sys_created_on,'
        + 'sys_id,'
        + 'priority'
        + '&sysparm_display_value=true'
    )

    print('Loading open incidents from dynamo...')

    dynamo_inc_list = get_from_snow(dynamo_pwd,dynamo_user,dynamo_inc_query)

    print(str(len(dynamo_inc_list.index)) + ' open incidents loaded from Dynamo.')


    # Get open incidents from Tiksu
    print('Enter Valio NOW username and password:')

    valionow_user = input('Username: ')
    valionow_pwd = getpass.getpass()
    valionow_inc_query = (
        'https://valio.service-now.com/api/now/v2/table/'
        + 'incident?'
        + 'sysparm_query='
        + 'incident_stateNOT IN6,7'
        + '^elisa_correlation_idISNOTEMPTY'
        + '&sysparm_fields='
        + 'number,'
        + 'elisa_correlation_id,'
        + 'incident_state,'
        + 'assignment_group,'
        + 'sys_created_on,'
        + 'sys_id,'
        + 'priority'
        + '&sysparm_display_value=true'
    )

    print('Loading open incidents from Valio NOW...')

    valionow_inc_list = get_from_snow(valionow_pwd,valionow_user,valionow_inc_query)

    print(str(len(valionow_inc_list.index)) + ' open incidents loaded from Valio NOW.')


    # Convert Assignment groups sysids to names
    print('Converting assignment groups to text...')

    dynamo_inc_list['assignment_group'] = dynamo_inc_list['assignment_group'].apply(sysid_to_name)
    valionow_inc_list['assignment_group'] = valionow_inc_list['assignment_group'].apply(sysid_to_name)

    print('Converting assignment group names done.')


    # Rename column headers
    dynamo_inc_list.columns = [
        'Dynamo palvelujono',
        'Dynamo tila',
        'Dynamo numero',
        'Dynamo prioriteetti',
        'Dynamo luotu',
        'Dynamo SysID',
        'Valio NOW numero'
    ]

    valionow_inc_list.columns = [
        'Valio NOW palvelujono',
        'Valio NOW tila',
        'Valio NOW numero',
        'Valio NOW prioriteetti',
        'Valio NOW luotu',
        'Valio NOW SysID',
        'Dynamo numero'
    ]


    # Convert date fields to date type
    dynamo_inc_list['Dynamo luotu'] = pd.to_datetime(
        dynamo_inc_list['Dynamo luotu'],
        dayfirst=True
    )

    valionow_inc_list['Valio NOW luotu'] = pd.to_datetime(
        valionow_inc_list['Valio NOW luotu'],
        dayfirst=True
    )


    # Merge Dynamo and Tiksu open inc lists
    open_inc_list = pd.merge(
        valionow_inc_list, dynamo_inc_list,
        on=['Dynamo numero', 'Valio NOW numero']
    )


    # Get open Tiksu incidents that are not open in Dynamo
    false_open_valionow_list = get_false_open_valionow_incidents(
        dynamo_inc_list,
        valionow_inc_list
    )


    # Get open Dynamo incidents that are not open in Tiksu
    false_open_dynamo_list = get_false_open_dynamo_incidents(
        dynamo_inc_list,
        valionow_inc_list
    )


    # # Close false open out dated Dynamo incidents
    # print('Resloving out dated incidents...')
    # resolved_counter = 0
    # for index, row in false_open_dynamo_list.iterrows():
    #     sys_id = row['Dynamo SysID']
    #     close_inc(dynamo_pwd,dynamo_user,sys_id)
    #     resolved_counter += 1
    # print(str(resolved_counter) + ' incidents resolved.')


    # # Add column to indicate if incident was closed in Tiksu with sepacial characters
    # print('Checking for special characters in close notes..')
    # false_open_dynamo_list['Sulkuviestissä erikoismerkki'] = false_open_dynamo_list['Tiksu numero'].apply(validate_close_note)
    #
    #
    # # Close false open Dynamo incidents closed in Tiksu with special characters
    # print("Resloving incidents closed in Tiksu with special characters '&' or '< >'...")
    # resolved_counter = 0
    # for index, row in false_open_dynamo_list.iterrows():
    #     if row['Sulkuviestissä erikoismerkki']:
    #         sys_id = row['Dynamo SysID']
    #         close_inc(dynamo_pwd,dynamo_user,sys_id)
    #         resolved_counter += 1
    # print(str(resolved_counter) + ' incidents resolved.')


    # Add column to indicate if false open Dynamo incident has duplicate

    if len(false_open_dynamo_list.index) > 0:

        print('Checking for duplicates in false open Dynamo incidents...')

        false_open_dynamo_list['Duplikaatti'] = false_open_dynamo_list.apply(
            get_dynamo_duplicate,
            axis=1
        )


    # Add column to indicate if false open Tiksu incident has duplicate

    if len(false_open_valionow_list.index) > 0:

        print('Checking for duplicates in false open Valio NOW incidents...')

        false_open_valionow_list['Duplikaatti'] = false_open_valionow_list.apply(
            get_valionow_duplicate,
            axis=1
        )


    # Save lists to .xlsx file
    print('Saving incident lists to excel file...')

    datestamp = pd.datetime.today().strftime('%y%m%d_%H%M')
    filename = 'reports\{}_open_inc_report.xlsx'.format(datestamp)

    save_to_excel(
        open_inc_list,
        false_open_valionow_list,
        false_open_dynamo_list,
        filename,
        datestamp
    )


# Run main function only if called directly
if __name__ == '__main__':
    main()