aonit-v2/perco_web.py at main · djars500/aonit-v2 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
from datetime import datetime,date,timedelta
import json
import pyodbc
from mysql import connector

class PercoWebApi():
    def __init__(self):
        with open('conf.json') as f:
            self.data = json.load(f)

        if self.data['selected_version'] == "perco_web":
            self.reconnect()

    def reconnect(self):
        if hasattr(self, 'con') and self.con.is_connected():
            self.con.close()
        self.con = connector.connect(
            host=self.data['percoweb']['host'],
            user=self.data['percoweb']['user'],
            password=self.data['percoweb']['password'],
            database=self.data['percoweb']['database'],
            port=self.data['percoweb']['port'],
            connection_timeout=120
        )


    def get_events(self,todayday):
        self.reconnect()
        cur = self.con.cursor()
        cur.execute(
            f"SELECT time_label, event_type, device_id, user_id, resource_number from event WHERE time_label BETWEEN '{todayday.strftime('%Y-%m-%d')} 00:00:00' AND '{todayday.strftime('%Y-%m-%d')} 23:59:59' AND event_type = 17 ORDER BY time_label DESC;"
            )
        events = cur.fetchall()
        cur.close()
        return events

    def get_events_not_proccesed(self):
        self.reconnect()

        cur = self.con.cursor()
        cur.execute(
            f"SELECT time_label, event_type, device_id, user_id, resource_number from event WHERE is_sended != 1 AND event_type = 17 ORDER BY time_label DESC;"
            )
        events = cur.fetchall()
        cur.close()
        return events

    def get_date_from_data(self, record):
        return record[3]

    def collect_events(self,todayday=None, processed=False):
        collect_events = []
        if processed:
            events = self.get_events_not_proccesed()
        else:
            events = self.get_events(todayday)


        for event in events:
            message = event[2]
            userID = event[3]
            dateTo = event[0]
            resource = event[4]
            datetime_object = dateTo
            cursor = self.con.cursor()

            if processed is True:
                todayday = datetime_object.date()

            if userID != None:
                try:
                    cursor.execute(
                        f"SELECT tabel_number,user_id FROM user_staff WHERE user_id = {userID}")
                    user = cursor.fetchone()
                    iin = user[0]
                    fullName = user[0]
                except Exception as e:
                    pass
                    # print(userID, e, event)

                if resource == 2:
                    collect_events.append(
                            (
                                iin,
                                fullName,
                            0,
                        datetime_object.strftime("%d.%m.%Y %H:%M:%S"),
                            todayday
                        ))
                elif resource == 1:
                    collect_events.append(
                        (
                            iin,
                            fullName,
                            1,
                        datetime_object.strftime("%d.%m.%Y %H:%M:%S"),
                        todayday
                    ))
        return collect_events

    def updateSended(self, records):
        self.reconnect()
        cur = self.con.cursor()
        for record in records:
            cur.execute(f"""UPDATE event SET is_sended = 1
                        WHERE
                        event_type = 17 AND
                        time_label = '{datetime.strptime(record[3], '%d.%m.%Y %H:%M:%S').strftime("%Y.%m.%d %H:%M:%S")}';
                        """)
            self.con.commit()