# -*- coding: utf-8 -*-
"""
Created on Tue Jan 23 23:23:01 2018
@author: Vadim Shkaberda
"""
import pyodbc

class DBConnect(object):
    ''' Provides connection to database and functions to work with server.
    '''
    def __enter__(self):
        # Connection properties
        conn_str = (
            r'Driver={SQL Server};'
            r'Server=s-kv-center-s64;'
            r'Database=SILPOAnalitic;'
            r'Trusted_Connection=yes;'
            )
        self.__db = pyodbc.connect(conn_str)
        self.__cursor = self.__db.cursor()
        return self

    def __exit__(self, type, value, traceback):
        self.__db.close()

    def file_to_update(self):
        ''' Fetching one file to be updated next.
        '''
        self.__cursor.execute('''SELECT top 1 UploadFileName, FirstResourceLink, ReportID
                  FROM [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]
                  where 1=1
                	and
                	((datepart(weekday,ExecutedJob) = iif(DAY1=1, 1, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY2=1, 2, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY3=1, 3, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY4=1, 4, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY5=1, 5, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY6=1, 6, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY7=1, 7, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate())))
                	AND
                	ExecutedJob > ISNULL(LastDateUpdate, 0)
                	and
                	StatusID = 1 --рабочий
                	and
                	ScheduleTypeID = 0 --прямой график
                	and
                	convert(time,getdate()) >= timefrom  -- обновлять позже назначеного
                and isnull(Error, 0) != 1
                order by [priority]''')
        return self.__cursor.fetchone()

    def successful_update(self, rID, update_time):
        ''' Update data on server that file update was succeeded.
        '''
        print('Update succeeded! Loading data to server.')
        self.__cursor.execute('UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp] \
                              SET LastDateUpdate = cast(? as datetime) \
                              WHERE ReportID = ?', (update_time, rID))
        self.__db.commit()

    def failed_update(self, rID, update_time):
        ''' Update data on server in case if update was failed.
        '''
        print('Update failed!')
        self.__cursor.execute('UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp] \
                              SET Error = 1, LastDateUpdate = cast(? as datetime) \
                              WHERE ReportID = ?', (update_time, rID))
        self.__db.commit()
3ae189f4632013e9828fbd28a5da38eaa2b8dd91
<mask token>


class DBConnect(object):
    <mask token>
    <mask token>
    <mask token>

    def file_to_update(self):
        """ Fetching one file to be updated next.
        """
        self.__cursor.execute(
            """SELECT top 1 UploadFileName, FirstResourceLink, ReportID
                  FROM [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]
                  where 1=1
                	and
                	((datepart(weekday,ExecutedJob) = iif(DAY1=1, 1, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY2=1, 2, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY3=1, 3, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY4=1, 4, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY5=1, 5, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY6=1, 6, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY7=1, 7, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate())))
                	AND
                	ExecutedJob > ISNULL(LastDateUpdate, 0)
                	and
                	StatusID = 1 --рабочий
                	and
                	ScheduleTypeID = 0 --прямой график
                	and
                	convert(time,getdate()) >= timefrom  -- обновлять позже назначеного
                and isnull(Error, 0) != 1
                order by [priority]"""
            )
        return self.__cursor.fetchone()

    def successful_update(self, rID, update_time):
        """ Update data on server that file update was succeeded.
        """
        print('Update succeeded! Loading data to server.')
        self.__cursor.execute(
            'UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]                               SET LastDateUpdate = cast(? as datetime)                               WHERE ReportID = ?'
            , (update_time, rID))
        self.__db.commit()
    <mask token>

<mask token>


class DBConnect(object):
    <mask token>

    def __enter__(self):
        conn_str = (
            'Driver={SQL Server};Server=s-kv-center-s64;Database=SILPOAnalitic;Trusted_Connection=yes;'
            )
        self.__db = pyodbc.connect(conn_str)
        self.__cursor = self.__db.cursor()
        return self

    def __exit__(self, type, value, traceback):
        self.__db.close()

    def file_to_update(self):
        """ Fetching one file to be updated next.
        """
        self.__cursor.execute(
            """SELECT top 1 UploadFileName, FirstResourceLink, ReportID
                  FROM [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]
                  where 1=1
                	and
                	((datepart(weekday,ExecutedJob) = iif(DAY1=1, 1, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY2=1, 2, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY3=1, 3, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY4=1, 4, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY5=1, 5, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY6=1, 6, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY7=1, 7, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate())))
                	AND
                	ExecutedJob > ISNULL(LastDateUpdate, 0)
                	and
                	StatusID = 1 --рабочий
                	and
                	ScheduleTypeID = 0 --прямой график
                	and
                	convert(time,getdate()) >= timefrom  -- обновлять позже назначеного
                and isnull(Error, 0) != 1
                order by [priority]"""
            )
        return self.__cursor.fetchone()

    def successful_update(self, rID, update_time):
        """ Update data on server that file update was succeeded.
        """
        print('Update succeeded! Loading data to server.')
        self.__cursor.execute(
            'UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]                               SET LastDateUpdate = cast(? as datetime)                               WHERE ReportID = ?'
            , (update_time, rID))
        self.__db.commit()
    <mask token>

<mask token>


class DBConnect(object):
    """ Provides connection to database and functions to work with server.
    """

    def __enter__(self):
        conn_str = (
            'Driver={SQL Server};Server=s-kv-center-s64;Database=SILPOAnalitic;Trusted_Connection=yes;'
            )
        self.__db = pyodbc.connect(conn_str)
        self.__cursor = self.__db.cursor()
        return self

    def __exit__(self, type, value, traceback):
        self.__db.close()

    def file_to_update(self):
        """ Fetching one file to be updated next.
        """
        self.__cursor.execute(
            """SELECT top 1 UploadFileName, FirstResourceLink, ReportID
                  FROM [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]
                  where 1=1
                	and
                	((datepart(weekday,ExecutedJob) = iif(DAY1=1, 1, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY2=1, 2, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY3=1, 3, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY4=1, 4, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY5=1, 5, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY6=1, 6, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY7=1, 7, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate())))
                	AND
                	ExecutedJob > ISNULL(LastDateUpdate, 0)
                	and
                	StatusID = 1 --рабочий
                	and
                	ScheduleTypeID = 0 --прямой график
                	and
                	convert(time,getdate()) >= timefrom  -- обновлять позже назначеного
                and isnull(Error, 0) != 1
                order by [priority]"""
            )
        return self.__cursor.fetchone()

    def successful_update(self, rID, update_time):
        """ Update data on server that file update was succeeded.
        """
        print('Update succeeded! Loading data to server.')
        self.__cursor.execute(
            'UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]                               SET LastDateUpdate = cast(? as datetime)                               WHERE ReportID = ?'
            , (update_time, rID))
        self.__db.commit()

    def failed_update(self, rID, update_time):
        """ Update data on server in case if update was failed.
        """
        print('Update failed!')
        self.__cursor.execute(
            'UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]                               SET Error = 1, LastDateUpdate = cast(? as datetime)                               WHERE ReportID = ?'
            , (update_time, rID))
        self.__db.commit()

<mask token>
import pyodbc


class DBConnect(object):
    """ Provides connection to database and functions to work with server.
    """

    def __enter__(self):
        conn_str = (
            'Driver={SQL Server};Server=s-kv-center-s64;Database=SILPOAnalitic;Trusted_Connection=yes;'
            )
        self.__db = pyodbc.connect(conn_str)
        self.__cursor = self.__db.cursor()
        return self

    def __exit__(self, type, value, traceback):
        self.__db.close()

    def file_to_update(self):
        """ Fetching one file to be updated next.
        """
        self.__cursor.execute(
            """SELECT top 1 UploadFileName, FirstResourceLink, ReportID
                  FROM [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]
                  where 1=1
                	and
                	((datepart(weekday,ExecutedJob) = iif(DAY1=1, 1, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY2=1, 2, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY3=1, 3, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY4=1, 4, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY5=1, 5, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY6=1, 6, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY7=1, 7, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate())))
                	AND
                	ExecutedJob > ISNULL(LastDateUpdate, 0)
                	and
                	StatusID = 1 --рабочий
                	and
                	ScheduleTypeID = 0 --прямой график
                	and
                	convert(time,getdate()) >= timefrom  -- обновлять позже назначеного
                and isnull(Error, 0) != 1
                order by [priority]"""
            )
        return self.__cursor.fetchone()

    def successful_update(self, rID, update_time):
        """ Update data on server that file update was succeeded.
        """
        print('Update succeeded! Loading data to server.')
        self.__cursor.execute(
            'UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]                               SET LastDateUpdate = cast(? as datetime)                               WHERE ReportID = ?'
            , (update_time, rID))
        self.__db.commit()

    def failed_update(self, rID, update_time):
        """ Update data on server in case if update was failed.
        """
        print('Update failed!')
        self.__cursor.execute(
            'UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]                               SET Error = 1, LastDateUpdate = cast(? as datetime)                               WHERE ReportID = ?'
            , (update_time, rID))
        self.__db.commit()

# -*- coding: utf-8 -*-
"""
Created on Tue Jan 23 23:23:01 2018
@author: Vadim Shkaberda
"""
import pyodbc

class DBConnect(object):
    ''' Provides connection to database and functions to work with server.
    '''
    def __enter__(self):
        # Connection properties
        conn_str = (
            r'Driver={SQL Server};'
            r'Server=s-kv-center-s64;'
            r'Database=SILPOAnalitic;'
            r'Trusted_Connection=yes;'
            )
        self.__db = pyodbc.connect(conn_str)
        self.__cursor = self.__db.cursor()
        return self

    def __exit__(self, type, value, traceback):
        self.__db.close()

    def file_to_update(self):
        ''' Fetching one file to be updated next.
        '''
        self.__cursor.execute('''SELECT top 1 UploadFileName, FirstResourceLink, ReportID
                  FROM [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp]
                  where 1=1
                	and
                	((datepart(weekday,ExecutedJob) = iif(DAY1=1, 1, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY2=1, 2, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY3=1, 3, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY4=1, 4, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY5=1, 5, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY6=1, 6, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate()))
                	OR
                	(datepart(weekday,ExecutedJob) = iif(DAY7=1, 7, 0) AND
                    datepart(weekday,ExecutedJob) =  datepart(weekday, getdate())))
                	AND
                	ExecutedJob > ISNULL(LastDateUpdate, 0)
                	and
                	StatusID = 1 --рабочий
                	and
                	ScheduleTypeID = 0 --прямой график
                	and
                	convert(time,getdate()) >= timefrom  -- обновлять позже назначеного
                and isnull(Error, 0) != 1
                order by [priority]''')
        return self.__cursor.fetchone()

    def successful_update(self, rID, update_time):
        ''' Update data on server that file update was succeeded.
        '''
        print('Update succeeded! Loading data to server.')
        self.__cursor.execute('UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp] \
                              SET LastDateUpdate = cast(? as datetime) \
                              WHERE ReportID = ?', (update_time, rID))
        self.__db.commit()

    def failed_update(self, rID, update_time):
        ''' Update data on server in case if update was failed.
        '''
        print('Update failed!')
        self.__cursor.execute('UPDATE [SILPOAnalitic].[dbo].[VV_Hermes_Reports_Temp] \
                              SET Error = 1, LastDateUpdate = cast(? as datetime) \
                              WHERE ReportID = ?', (update_time, rID))
        self.__db.commit()