# -*- coding: utf-8 -*-
#! /usr/bin/env python
import win32serviceutil
import win32service
import win32event
import servicemanager
import pyodbc
import mysql.connector
import time
import odbc

class AppServerSvc (win32serviceutil.ServiceFramework):
    _svc_name_ = "Solid_DB"
    _svc_display_name_ = "Solid_DB"
    _svc_description_ = "__Import data from \\c-dispatcher-sh\view\solid74..78 to my sql asdk  id(74001-78003)"

    def __init__(self,args):
        win32serviceutil.ServiceFramework.__init__(self,args)
        self.hWaitStop = win32event.CreateEvent(None,0,0,None)
        self.hWaitResume = win32event.CreateEvent(None, 0, 0, None)
        self.timeout = 400 #Пауза между выполнением основного цикла службы в миллисекундах
        self.resumeTimeout = 400
        self._paused = False

    def SvcStop(self):
        self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING)
        win32event.SetEvent(self.hWaitStop)
        servicemanager.LogMsg(servicemanager.EVENTLOG_INFORMATION_TYPE,
                              servicemanager.PYS_SERVICE_STOPPED,
                              (self._svc_name_, ''))

    def SvcPause(self):
        self.ReportServiceStatus(win32service.SERVICE_PAUSE_PENDING)
        self._paused = True
        self.ReportServiceStatus(win32service.SERVICE_PAUSED)
        servicemanager.LogInfoMsg("The %s service has paused." % (self._svc_name_, ))

    def SvcContinue(self):
        self.ReportServiceStatus(win32service.SERVICE_CONTINUE_PENDING)
        win32event.SetEvent(self.hWaitResume)
        self.ReportServiceStatus(win32service.SERVICE_RUNNING)
        servicemanager.LogInfoMsg("The %s service has resumed." % (self._svc_name_, ))



    def SvcDoRun(self):
        servicemanager.LogMsg(servicemanager.EVENTLOG_INFORMATION_TYPE,
                              servicemanager.PYS_SERVICE_STARTED,
                              (self._svc_name_,''))
        self.main()

    def timestamp(data):
        data=time.mktime(data.timetuple())
        return data

    def read_solid_74(self):
        MDB = 'z:\Solid074.mdb'
        DRV = '{Microsoft Access Driver (*.mdb, *.accdb)}'
        #PWD = 'mypassword'

        #conn = pyodbc.connect('DRIVER=%s;DBQ=%s' % (DRV,MDB))
        #curs = conn.cursor()
        cnxn = pyodbc.connect('DRIVER=%s;DBQ=%s' % (DRV,MDB), autocommit=True)

        #cnxn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DSN=solid_74;UID=GRD\nekrasov-ee;PWD=qwerty-12')
        cursor = cnxn.cursor()
        #cursor.rollback()
        cursor.execute('select DatTime, Q, Ro, Sol from Solid order by Dattime')
        rows = cursor.fetchall()
        cursor.rollback()
        cursor.close()
        cnxn.close()
        return rows
    def read_solid_78(self):
        cnxn = pyodbc.connect("DSN=solid_78")
        cursor = cnxn.cursor()
        cursor.rollback()
        cursor.execute('select DatTime, Q, Ro, Sol from Solid order by Dattime')
        rows = cursor.fetchall()
        cursor.rollback()
        cursor.close()
        cnxn.close()
        return rows


    def update_asdk_Q_74(a,b):
        cnxn2 = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='asdk')
        cursor2 = cnxn2.cursor()
        cursor2.execute('UPDATE signals SET signals.status    ='+ str(a) +' '+'WHERE signals.id=74001')#update signals set signals.status=121234 where signals.id = 74001'
        cursor2.execute('UPDATE signals SET signals.timestamp='+str(b)+' '+'WHERE signals.id=74001')#update signals set signals.status=121234 where signals.id = 74001'

        cursor2.close()
        cnxn2.commit()
        cnxn2.close()
    def update_asdk_Po_74(a,b):
        cnxn2 = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='asdk')
        cursor2 = cnxn2.cursor()
        cursor2.execute('UPDATE signals SET signals.status    ='+ str(a) +' '+'WHERE signals.id=74002')#update signals set signals.status=121234 where signals.id = 74001'
        cursor2.execute('UPDATE signals SET signals.timestamp='+str(b)+' '+'WHERE signals.id=74002')#update signals set signals.status=121234 where signals.id = 74001'

        cursor2.close()
        cnxn2.commit()
        cnxn2.close()
    def update_asdk_Sol_74(a,b):
        cnxn2 = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='asdk')
        cursor2 = cnxn2.cursor()
        cursor2.execute('UPDATE signals SET signals.status    ='+ str(a) +' '+'WHERE signals.id=74003')#update signals set signals.status=121234 where signals.id = 74001'
        cursor2.execute('UPDATE signals SET signals.timestamp='+str(b)+' '+'WHERE signals.id=74003')#update signals set signals.status=121234 where signals.id = 74001'

        cursor2.close()
        cnxn2.commit()
        cnxn2.close()
    def update_asdk_Q_78(a,b):
        cnxn2 = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='asdk')
        cursor2 = cnxn2.cursor()
        cursor2.execute('UPDATE signals SET signals.status    ='+ str(a) +' '+'WHERE signals.id=78001')#update signals set signals.status=121234 where signals.id = 74001'
        cursor2.execute('UPDATE signals SET signals.timestamp='+str(b)+' '+'WHERE signals.id=78001')#update signals set signals.status=121234 where signals.id = 74001'

        cursor2.close()
        cnxn2.commit()
        cnxn2.close()
    def update_asdk_Po_78(a,b):
        cnxn2 = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='asdk')
        cursor2 = cnxn2.cursor()
        cursor2.execute('UPDATE signals SET signals.status    ='+ str(a) +' '+'WHERE signals.id=78002')#update signals set signals.status=121234 where signals.id = 74001'
        cursor2.execute('UPDATE signals SET signals.timestamp='+str(b)+' '+'WHERE signals.id=78002')#update signals set signals.status=121234 where signals.id = 74001'

        cursor2.close()
        cnxn2.commit()
        cnxn2.close()
    def update_asdk_Sol_78(a,b):
        cnxn2 = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='asdk')
        cursor2 = cnxn2.cursor()
        cursor2.execute('UPDATE signals SET signals.status    ='+ str(a) +' '+'WHERE signals.id=78003')#update signals set signals.status=121234 where signals.id = 74001'
        cursor2.execute('UPDATE signals SET signals.timestamp='+str(b)+' '+'WHERE signals.id=78003')#update signals set signals.status=121234 where signals.id = 74001'

        cursor2.close()
        cnxn2.commit()
        cnxn2.close()


    def read_asdk2(self):
        #print 'asdasd'
        cnxn2 = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='asdk')
        cursor2 = cnxn2.cursor()
        cursor2.execute('UPDATE signals SET signals.status = 5555 WHERE signals.id=78003')
        cursor2.close()
        cnxn2.commit()
        cnxn2.close()
        return rows2



    def read_write_74(self):
        solid_74 = max(self.read_solid_74())
        if len(str(round(solid_74[1],1)))==6:
            solid_74[1]=solid_74[1]*10
        if len(str(round(solid_74[1],1)))==5:
            solid_74[1]=solid_74[1]*1000
        solid_74[2]=solid_74[2]*10000

        #if len(str(round(solid_74[2],4)))==6:
        #    solid_74[2]=solid_74[2]*10000
        #if len(str(round(solid_74[2],3)))==5:
        #    solid_74[2]=solid_74[2]*100
        self.update_asdk_Q_74  (int(solid_74[1]),timestamp(solid_74[0]))#a[0]
        self.update_asdk_Po_74 (int(solid_74[2]),timestamp(solid_74[0]))#a[0]
        self.update_asdk_Sol_74(int(solid_74[3]),timestamp(solid_74[0]))#a[0]
        #print solid_74[0]

    def read_write_78(self):
        solid_78 = max(self.read_solid_78())
        if len(str(round(solid_78[1],1)))==6:
            solid_78[1]=solid_78[1]*10
        if len(str(round(solid_78[1],1)))==5:
            solid_78[1]=solid_78[1]*1000
        solid_78[2]=solid_78[2]*10000
        #if len(str(round(solid_78[2],4)))==6:
        #    solid_78[2]=solid_78[2]*10000
        #if len(str(round(solid_78[2],3)))==5:
        #    solid_78[2]=solid_78[2]*100
        self.update_asdk_Q_78  (int(solid_78[1]),timestamp(solid_78[0]))#a[0]
        self.update_asdk_Po_78 (int(solid_78[2]),timestamp(solid_78[0]))#a[0]
        self.update_asdk_Sol_78(int(solid_78[3]),timestamp(solid_78[0]))#a[0]
        #print solid_78[0]
    #В этом методе реализовываем нашу службу
    def main(self):

        #Здесь выполняем необходимые действия при старте службы
        servicemanager.LogInfoMsg("Hello! I'm a Dummy Service.")
        while True:
            self.read_solid_74()
            servicemanager.LogInfoMsg("I'm still here.")
            #Проверяем не поступила ли команда завершения работы службы
            rc = win32event.WaitForSingleObject(self.hWaitStop, self.timeout)
            if rc == win32event.WAIT_OBJECT_0:
                #Здесь выполняем необходимые действия при остановке службы
                servicemanager.LogInfoMsg("Bye!")
                break
            #Здесь выполняем необходимые действия при приостановке службы
            if self._paused:
                servicemanager.LogInfoMsg("I'm paused... Keep waiting...")
            #Приостановка работы службы
            while self._paused:
                #Проверям не поступила ли команда возобновления работы службы
                rc = win32event.WaitForSingleObject(self.hWaitResume, self.resumeTimeout)
                if rc == win32event.WAIT_OBJECT_0:
                    self._paused = False
                    #Здесь выполняем необходимые действия при возобновлении работы службы
                    servicemanager.LogInfoMsg("Yeah! Let's continue!")
                    break

if __name__ == '__main__':
    win32serviceutil.HandleCommandLine(AppServerSvc)