class Main():
def __init__(self):
self.commans = (Play(), Variable(), Speech(), Info(), Sinoptik())
self.db = DBConnector()
# Очищаем список команд. Список не актуален.
self.db.IUD("delete from core_execute")
self.db.commit()
self.run()
def run(self):
while True:
try:
for row in self.db.select("select * from core_execute order by ID"):
print(str(row[1], "utf-8"))
for c in str(row[1], "utf-8").split("\n"):
self.execute(c.strip())
self.db.IUD("delete from core_execute where ID = %s" % row[0])
self.db.commit()
time.sleep(0.2)
# Дергаем секундный таймер, может кому пригодится
for cmd in self.commans:
cmd.time_handler()
except mysql.connector.Error as e:
self.execute('speech("пропала связь с базой")')
time.sleep(10)
def execute(self, command):
print("[%s] выполняется %s" % (time.strftime("%d-%m-%Y %H:%M"), command))
for cmd in self.commans:
if cmd.check_comm(self.db, command):
break
def __init__(self):
self.commans = (Play(), Variable(), Speech(), Info(), Sinoptik())
self.db = DBConnector()
# Очищаем список команд. Список не актуален.
self.db.IUD("delete from core_execute")
self.db.commit()
self.run()
def __init__(self):
if (os.environ.get('ENV') != 'prod'): # We are not in Heroku
github_token = os.environ.get('GITHUB_TOKEN')
else:
github_token = os.environ['GITHUB_TOKEN']
self.github = github3.login(token=github_token)
self.db = DBConnector()
def __init__(self):
self.player = False
self.db = DBConnector()
self.timer_id = 101
self.timer_on = 102
self.timer_len = 0.5
self.get_start_time()
self.volume = 0
self.run()
class TestDBConnector(unittest.TestCase):
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.db = DBConnector()
def test_add_and_delete_data(self):
if os.environ.get('TRAVIS') == None:
github_data_import = GitHubData(
date_updated=datetime.datetime.now(),
language='repo_name',
pull_requests=0,
open_issues=0,
number_of_commits=0,
number_of_branches=0,
number_of_releases=0,
number_of_contributors=0,
number_of_watchers=0,
number_of_stargazers=0,
number_of_forks=0
)
res = self.db.add_data(github_data_import)
self.assertTrue(isinstance(res, GitHubData))
res = self.db.delete_data(res.id, 'github_data')
self.assertTrue(res)
packagedata = PackageManagerData(
date_updated=datetime.datetime.now(),
csharp_downloads=0,
nodejs_downloads=0,
php_downloads=0,
python_downloads=0,
ruby_downloads=0
)
res = self.db.add_data(packagedata)
self.assertTrue(isinstance(res, PackageManagerData))
res = self.db.delete_data(res.id, 'package_manager_data')
self.assertTrue(res)
def test_get_data(self):
if os.environ.get('TRAVIS') == None:
github_data = self.db.get_data(GitHubData)
self.assertTrue(isinstance(github_data, list))
self.assertTrue(isinstance(github_data[0], GitHubData))
class GitHub(object):
"""Collect time stamped repository data from GitHub and store in a DB"""
def __init__(self):
if (os.environ.get('ENV') != 'prod'): # We are not in Heroku
github_token = os.environ.get('GITHUB_TOKEN')
else:
github_token = os.environ['GITHUB_TOKEN']
self.github = github3.login(token=github_token)
self.db = DBConnector()
def update_library_data(self, repo_user, repo_name):
"""Gets data from a given GitHub repo and adds it to the DB
:param repo_user: the username of the repo's owner
:param repo_name: the name of the GitHub repo
:type repo_user: string
:type repo_name: string
:returns: Returns the data object that was added to the DB
:rtype: Data object
"""
github_data = self.github.repository(repo_user, repo_name)
lib_data = {}
lib_data['num_pull_requests'] \
= sum(1 for i in github_data.iter_pulls())
lib_data['num_issues'] \
= sum(1 for i in github_data.iter_issues())
lib_data['num_commits'] \
= sum(1 for i in github_data.iter_commits())
lib_data['num_branches'] \
= sum(1 for i in github_data.iter_branches())
lib_data['num_releases'] \
= sum(1 for i in github_data.iter_releases())
lib_data['num_contributors'] \
= sum(1 for i in github_data.iter_contributors())
lib_data['num_watchers'] \
= sum(1 for i in github_data.iter_subscribers())
lib_data['num_stargazers'] \
= sum(1 for i in github_data.iter_stargazers())
lib_data['num_forks'] = github_data.forks_count
github_data_import = GitHubData(
date_updated=datetime.datetime.now(),
language=repo_name,
pull_requests=lib_data['num_pull_requests'],
open_issues=lib_data['num_issues'],
number_of_commits=lib_data['num_commits'],
number_of_branches=lib_data['num_branches'],
number_of_releases=lib_data['num_releases'],
number_of_contributors=lib_data['num_contributors'],
number_of_watchers=lib_data['num_watchers'],
number_of_stargazers=lib_data['num_stargazers'],
number_of_forks=lib_data['num_forks']
)
return self.db.add_data(github_data_import)
def __init__(self):
self.db = DBConnector()
self.db.IUD("update core_scheduler set ACTION_DATETIME = NULL")
self.db.commit()
print("-- Предстоящие задачи --")
for row in self.db.select("select ID, COMM, ACTION, ACTION_DATETIME, INTERVAL_TIME_OF_DAY, INTERVAL_DAY_OF_TYPE, INTERVAL_TYPE from core_scheduler"):
next_time = self.parse_time(None, str(row[4], "utf-8"), str(row[5], "utf-8"), row[6])
print("[%s] %s" % (datetime.datetime.fromtimestamp(next_time), str(row[1], "utf-8")))
print("------------------------")
self.check_time()
self.run()
def write_to_redis(num_to_generate=0):
db = DBConnector(config.REDIS_HOST,config.REDIS_PORT, config.REDIS_PASSWORD).redis_connection()
num = 0
if num_to_generate == 0:
num_to_generate = int(raw_input("Please provide a number of users you would like to spawn: "))
start = datetime.now()
print "Starting to insert {} docs at {}.".format(num_to_generate,start)
for num in xrange(num_to_generate):
doc = dg.generate_user_doc()
print doc
print doc['last_name']
print doc['first_name']
print doc['age']
print doc['date_joined']
to_hash = "{}{}{}{}".format(doc['last_name'],
doc['first_name'],doc['age'],doc['date_joined'])
print to_hash
doc_hash = hashlib.sha256(to_hash).hexdigest()
print doc_hash
db.hmset(doc_hash, doc)
db.lpush('doc_list', doc_hash)
num += 1
end = datetime.now()
print "Finished inserting {} docs at {}.".format(num,end)
print "Took {} to complete.".format(end-start)
return db.hgetall('users')
def do_GET(self):
try:
s = "/speech_text?text="
self.path.index(s)
url_data = parse.urlsplit(self.path)
p = parse.parse_qs(url_data.query)
s = p["text"][0]
self.send_response(200)
self.send_header('content-type', "text/html")
self.send_header('charset', 'UTF8')
self.end_headers()
self.flush_headers()
from db_connector import DBConnector
db = DBConnector()
db.IUD("insert into core_execute (COMMAND) values ('speech(\"%s\")')" % s)
db.commit()
db.disconnect()
except:
self._execute_query(parse.urlsplit(self.path))
class TestPackageManagers(unittest.TestCase):
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.pm = PackageManagers()
self.db = DBConnector()
self.config = Config()
def test_update_package_manager_data(self):
if os.environ.get('TRAVIS') == None:
res = self.pm.update_package_manager_data(
self.config.package_manager_urls)
self.assertTrue(isinstance(res, PackageManagerData))
res = self.db.delete_data(res.id, 'package_manager_data')
self.assertTrue(res)
class TestGitHub(unittest.TestCase):
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.github = GitHub()
self.db = DBConnector()
self.config = Config()
def test_update_library_data(self):
if os.environ.get('TRAVIS') == None:
res = self.github.update_library_data(self.config.github_user,
self.config.github_repos[0])
self.assertTrue(isinstance(res, GitHubData))
res = self.db.delete_data(res.id, 'github_data')
self.assertTrue(res)
def __init__(self):
self.fast_timeput = 0.05
self.check_lan_error = False
self.sync_error_counter = []
# Connect to serial port
try:
self.serialPort = serial.Serial(self.SERIAL_PORT, self.SERIAL_SPEED, parity='O', timeout=self.fast_timeput)
except:
print("Ошибка подключения к '%s'" % self.SERIAL_PORT)
self.db = DBConnector()
self.db.load_controllers()
self.queue = []
# Run main loop
self.run()
class Main():
def __init__(self):
self.db = DBConnector()
self.run()
def run(self):
while True:
relIds = []
for keys in self.db.select("select CORE_GET_LAST_CHANGE_ID()"):
if keys[0] - 1 > self.db.lastVarChangeID:
# c.ID, c.VARIABLE_ID, c.VALUE, v.APP_CONTROL, v.GROUP_ID
for row in self.db.variable_changes():
if row[3] == 1: # Слежение за светом
relIds += [str(row[1]), ","]
if len(relIds) > 0:
for row in self.db.select("select v.APP_CONTROL, c.NAME, p.NAME, v.VALUE "
" from core_variables v, core_variable_controls c, plan_parts p "
" where v.ID in (%s) "
" and v.APP_CONTROL = c.ID "
" and v.GROUP_ID = p.ID "
" order by v.ID" % ("".join(relIds[:-1]),)):
s = [str(row[2], "utf-8"), ". ", str(row[1], "utf-8"), " "]
if row[3]:
s += ["включен"]
else:
s += ["выключен"]
self._add_command('speech("%s")' % "".join(s).lower())
time.sleep(0.2)
def _add_command(self, command):
print("[%s] %s" % (time.strftime("%d-%m-%Y %H:%M"), command))
"""
for row in self.db.select("select ID from core_execute where COMMAND = '%s'" % command):
self.db.IUD("delete from core_execute where ID = %s" % row[0])
self.db.commit()
"""
if self.get_quiet_time():
try:
command.index("speech(")
command = "speech(\"\")"
except:
pass
self.db.IUD("insert into core_execute (COMMAND) values ('%s')" % command)
self.db.commit()
def get_quiet_time(self):
for rec in self.db.select("select VALUE from core_variables where NAME = 'QUIET_TIME'"):
if rec[0]:
return True
return False
from interface import Interface
from config_manager import ConfigManager
from db_connector import DBConnector
from logger_connector import LoggerConnector
from file_connector import LoggerFileConnector
from file_connector import DbFileConnector
import i18n
config_manager = ConfigManager("config.json")
# i18n setup
i18n.load_path.append('./translations')
i18n.set('filename_format', '{locale}.{format}')
i18n.set('fallback', 'en')
i18n.set('file_format', 'json')
i18n.set('locale', config_manager.language)
logger_file_connector = LoggerFileConnector(config_manager)
db_file_connector = DbFileConnector(config_manager)
logger_connector = LoggerConnector(logger_file_connector)
db_connector = DBConnector(db_file_connector)
application = Interface(db_connector, logger_connector, config_manager)
application.menu()
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.github = GitHub()
self.db = DBConnector()
self.config = Config()
def __init__(self):
self.prev_val = 0
#self.play_file("samples/on.wav")
self.db = DBConnector()
self.run()
class RecipientManager:
"""
RecipientManager provides an interface that allows the user to manage
the list of individuals who are notified when notifications are sent
out. Recipient manager lets the user add or remove recipients from the
list as well as list all current recipients.
The list/database managed by RecipientManager SHOULD be remote because
the purpose of this tool is to verify system integrity. Relying on a
local list means that the integrity of the tool could be compromised
in the same manner as the files whose interity it is trying to verify.
The list/database CAN be kept on the same server as the checksum database
because the integrity of that server is assumed to be intact for the
purposes of verification.
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| email | varchar(254)| YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
Questions/Notes:
Should the recipient manager be allowed to add the same email twice?
How do we protect against sql injections?
Errors should be logged, not printed.
How do we customize the manager? Config file? Passed in as argument?
How much customization should be allowed?
More options means more opportunities for bugs.
Pass arguments to functions vs using class member variables.
What to return case where affect already exists. I.e. creating table that already exists. Return True?
To above: Unix Philosophy is only complain if there is an error.
More specialized handeling of errors.
"""
def __init__(self, table_name="RECIPIENTS", db_connector=None):
"""
Connects to the database that stores the recipient table.
:param table_name: Table in the database where we will store the
recipient information.
"""
if db_connector == None:
self.connector = DBConnector()
else:
self.connector = db_connector
self.connection = self.connector.get_connection()
self.table_name = table_name
self.email_field_length = 254
self.logger = Logger()
def create_recipient_table(self):
"""
Creates a table if it doesn't exits in database to hold recipients.
:return: bool -- True if the table is created or already exists,
False otherwise
"""
try:
cursor = self.connection.cursor()
sql = """CREATE TABLE IF NOT EXISTS %s (EMAIL VARCHAR(%d) NOT
NULL PRIMARY KEY)""" % (self.table_name, self.email_field_length)
cursor.execute(sql)
self.logger.log_generic_message("Table created: {}".format(
self.table_name))
return True
except Exception as err:
self.logger.log_generic_message(err)
return False
def delete_recipient_table(self):
"""
Deletes the database table containing all recipient information.
:return: bool -- True if the table is deleted or does not exist.
False otherwise
"""
try:
cursor = self.connection.cursor()
sql = "DROP TABLE IF EXISTS %s" % self.table_name
cursor.execute(sql)
self.logger.log_generic_message("Table deleted: {}".format(
self.table_name))
return True
except Exception as err:
self.logger.log_generic_message(err)
return False
def table_exists(self):
"""
Checks if the table with the table named passed to the recipient
manager exists.
:return: bool -- True if the table exists. False otherwise.
"""
try:
cursor = self.connection.cursor()
sql = "SHOW TABLES LIKE '%s'" % self.table_name
cursor.execute(sql)
result = cursor.fetchone()
if result:
return True
else:
return False
except Exception as err:
self.logger.log_generic_message(err)
return False
def add_recipient(self, recipient):
"""
Adds a new recipient to the database of users who recieve an email
when notifications are sent
:param recipient: Email to be added to the recipient list
:type subject: string
:returns: bool -- True if add was successful, False otherwise
"""
if not validate_email(recipient, verify=False):
return False
if len(recipient) > 254:
return False
if not self.create_recipient_table():
return False
try:
cursor = self.connection.cursor()
sql = """INSERT INTO %s (
EMAIL) VALUES ('%s')""" % (self.table_name, recipient)
cursor.execute(sql)
self.connection.commit()
self.logger.log_generic_message(
"Recipient added: {}".format(recipient))
return True
except Exception as err:
print err
self.logger.log_generic_message(err)
self.connection.rollback()
return False
def print_table(self):
"""
For Debugging purposes, a print of table.
"""
try:
sql = "SELECT * FROM %s" % self.table_name
cursor = self.connector.connection.cursor()
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
print(row[0])
return True
except Exception as err:
self.logger.log_generic_message(err)
return False
def remove_recipient(self, recipient):
"""
Removes emails from the list of recipients that matches the
string 'recipient'
:param recipient: Email to be removed from the recipient list
:type subject: string
:returns: bool -- True if remove was successful, False otherwise
"""
if not self.table_exists():
msg = "Table does not exist from which email is to be removed"
self.logger.log_generic_message(msg)
return False
try:
cursor = self.connector.connection.cursor()
sql = "DELETE FROM %s WHERE EMAIL = '%s'" % (self.table_name,
recipient)
cursor.execute(sql)
results = cursor.fetchall(
) #: To let the system know that there are no results to fetch from and it return False
self.connector.connection.commit()
self.logger.log_generic_message(
"Recipient removed: {}".format(recipient))
return True
except Exception as err:
self.logger.log_generic_message(err)
self.connector.connection.rollback()
return False
def get_recipients(self):
"""
Get a list of emails contained in the recipient list.
returns: list -- String list containing recipient emails.
"""
if not self.table_exists():
return []
try:
recipient_emails = []
sql = "SELECT * FROM %s" % self.table_name
cursor = self.connector.connection.cursor()
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
recipient_emails.append(str(row[0]))
return recipient_emails
except Exception as err:
self.logger.log_generic_message(err)
return []
class ChecksumManager:
"""
Provides an interface to control checksum/filename pair table.
Checksum Database Table
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| filename | varchar(255) | YES | | NULL | |
| checksum | varchar(64) | YES | | NULL | |
| filepath | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
The filepath in the table is stored as the absolute filename.
NOTE: The checksum manager should be able to specify the absolute path
of the file, but the filename itself needs to be stored as the key
so that the user can delete it by just specifying the filename. It has to be
split and added as both a file and filename.
"""
def __init__(self, table_name="CHECKSUMS", db_connector=None):
self.filename_field_length = 255
self.checksum_field_length = 64
self.filepath_field_length = 255
if db_connector == None:
self.connector = DBConnector()
else:
self.connector = db_connector()
self.connection = self.connector.get_connection()
self.table_name = table_name
self.checksum_calculator = ChecksumCalculator()
self.logger = Logger(__name__)
def checksum_table_exists(self):
"""
Check to see if the checksum table exists in the database.
:return: bool -- True if the table exists. False otherwise.
"""
try:
cursor = self.connection.cursor()
sql = "SHOW TABLES LIKE '%s'" % self.table_name
cursor.execute(sql)
result = cursor.fetchone()
if result:
return True
else:
return False
except Exception as err:
self.logger.log_generic_message(err)
return False
def create_checksum_table(self):
"""
Creates a new checksum table in the database with the same properties
as described in the class documentation.
:return: bool -- True if the table was created or already existed.
False otherwise.
"""
try:
cursor = self.connection.cursor()
sql = """CREATE TABLE IF NOT EXISTS %s (filename VARCHAR(%d) NOT
NULL PRIMARY KEY, checksum VARCHAR(%d) NOT NULL, filepath VARCHAR(%d) NOT NULL)""" % (
self.table_name, self.filename_field_length,
self.checksum_field_length, self.filepath_field_length)
cursor.execute(sql)
self.logger.log_generic_message("Table created: {}".format(
self.table_name))
return True
except Exception as err:
self.logger.log_generic_message(err)
return False
def add_checksum_pair(self, filename):
"""
Calculates the checksum of file filename and then add the new
checksum/filename entry to the database. If the table does
not yet exist, then the table is first created and then the checksum
pair is added.
TODO: If the user supplies an absolute path at the filename, the name
of the file has to be stripped of the path to be used as the key.
TODO: Check the robustness of connection rollback.
:param filename: The name of the file whose filename/checksum is added
:type filename: string
:return: bool -- True if added successfuly. False otherwise.
"""
if not self.create_checksum_table():
return False
if len(filename) > self.filename_field_length:
return False
checksum = self.checksum_calculator.calculate_checksum(filename)
filepath = self.get_abspath(filename)
if checksum:
try:
cursor = self.connection.cursor()
sql = """INSERT INTO %s (
filename,checksum,filepath) VALUES ('%s','%s','%s')""" % (
self.table_name, filename, checksum, filepath)
cursor.execute(sql)
self.connection.commit()
self.logger.log_generic_message("Pair added: {}({})".format(
filename, checksum))
return True
except Exception as err:
self.logger.log_generic_message(err)
self.connection.rollback()
return False
else:
return False
def remove_checksum_pair(self, filename):
"""
Removes the entry with filename filename in the checksum table. If
the checksum pair does not exist in the database or was not removed,
the function returns False.
:param filename: The name of the file whose filename/checksum pair is being removed.
:type filename: string
:return: bool -- True if removed successfuly. False otherwise.
"""
if not self.checksum_table_exists():
return True
try:
cursor = self.connector.connection.cursor()
sql = "DELETE FROM %s WHERE filename = '%s'" % (self.table_name,
filename)
cursor.execute(sql)
self.connector.connection.commit()
self.logger.log_generic_message(
"file removed: {}".format(filename))
return True
except Exception as err:
self.logger.log_generic_message(err)
self.connector.connection.rollback()
return False
def get_checksum_pairs(self):
"""
Returns a list of tuples formated as follows: (filename, hash, absolute filename)
:return: list -- List of string tuples
Return an empty list if no checksum pairs exist
or the table/database does not exist.
"""
if not self.checksum_table_exists():
return []
try:
checksum_pairs = []
sql = "SELECT * FROM %s" % self.table_name
cursor = self.connector.connection.cursor()
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
entry = []
entry.append(str(row[0]))
entry.append(str(row[1]))
entry.append(str(row[2]))
checksum_pairs.append(entry)
return checksum_pairs
except Exception as err:
self.logger.log_generic_message(err)
return []
def get_abspath(self, filename):
"""
Returns the absolute path of filename
:param filename: filename of the file whose path we want to find
:returns: string -- Absolute path if succssful. None otherwise
"""
if os.path.exists(filename):
return os.path.abspath(filename)
else:
return None
def delete_checksum_table(self):
"""
Deletes the database table containing all recipient information.
:return: bool -- True if the table is deleted or does not exist.
False otherwise
"""
try:
cursor = self.connection.cursor()
sql = "DROP TABLE IF EXISTS %s" % self.table_name
cursor.execute(sql)
self.logger.log_generic_message("Table deleted: {}".format(
self.table_name))
return True
except Exception as err:
self.logger.log_generic_message(err)
return False
class Main():
SERIAL_PORT = "/dev/ttyUSB0"
SERIAL_SPEED = 57600
PACK_SYNC = 1
PACK_COMMAND = 2
PACK_ERROR = 3
def __init__(self):
self.fast_timeput = 0.1 #0.05
self.check_lan_error = False
# Connect to serial port
try:
self.serialPort = serial.Serial(self.SERIAL_PORT, self.SERIAL_SPEED, parity='O', timeout=self.fast_timeput)
except:
print("Ошибка подключения к '%s'" % self.SERIAL_PORT)
self.db = DBConnector()
self.db.load_controllers()
self.queue = []
# Run main loop
self.run()
def send_pack(self, dev_id, pack_type, pack_data, flush=True):
buf = json.dumps([dev_id, pack_type, pack_data]).encode("utf-8")
buf += bytearray([0x0])
c = 5
for err in range(c):
self.serialPort.write(buf)
if flush:
self.serialPort.flush()
#print(buf)
res = self.check_lan()
if self.check_lan_error == False:
break
if err < (c - 1):
self.check_lan_error = False
return res
def _store_variable_to_db(self, dev_id, pack_data):
for var in pack_data:
if len(var) > 1:
self.db.set_variable_value(var[0], var[1], dev_id)
def check_lan(self):
try:
buf = self.serialPort.readline()
if len(buf) > 0:
#print(buf)
resp = buf.decode("utf-8")
data = []
for pack in resp.split(chr(0x0)):
if len(pack) > 0:
d = json.loads(pack)
data += [d]
if d[1] == self.PACK_ERROR:
self.check_lan_error = True
for s in d[2]:
self._command_info("%s" % s)
return data
else:
return False
except Exception as e:
self.check_lan_error = True
self._command_info("EXCEPT {}".format(e.args))
return False
def _sync_variables(self):
# Зачитываем изменения в БД
var_data = self.db.variable_changes()
recv_valid = False
# Шлем посылку никому, чтобы контроллеры приготовились принимать
self.send_pack(0, self.PACK_SYNC, [])
time.sleep(0.02)
# Рассылаем изменения в БД и паралельно читаем обновления
for dev in self.db.controllers:
for rep in range(3): # 3 попытки отослать пакет
pack_data = []
lt = time.localtime()
t = time.mktime((2000, 1, 1, 0, 0, 0, 0, 0, lt.tm_isdst))
pack_data += [[-100, round(time.time() - t)]] #Передаем системное время в контроллеры
for var in var_data:
if var[2] != dev[0]:
pack_data += [[var[0], var[1]]]
date = datetime.datetime.now().strftime('%H:%M:%S')
print("[%s] SYNC. '%s': " % (date, dev[1]), end="")
cl = self.send_pack(dev[0], self.PACK_SYNC, pack_data)
if cl:
for res_pack in cl:
if res_pack[2] == "RESET":
print("RESET ", end="")
is_ok = self.send_pack(dev[0], self.PACK_SYNC, self._reset_pack()) != False
for r in range(30):
if self.check_lan():
is_ok = True
if is_ok:
print("OK\n")
recv_valid = True
else:
print("ERROR\n")
else:
self._store_variable_to_db(res_pack[0], res_pack[2])
print("OK")
print(" >> ", pack_data)
print(" << ", res_pack[2], "\n")
recv_valid = True
else:
print("ERROR\n")
time.sleep(0.02)
if recv_valid: # Обмен прошел успешно повторы не требуются
break
def _reset_pack(self):
return self.db.all_variables();
def _command_info(self, text, replace_text = None):
text = text.replace("'", "`")
text = text.replace('"', '\"')
s = self.db.get_property('RS485_COMMAND_INFO')
if replace_text == None:
text = text.replace("<", "<")
text = text.replace(">", ">")
print(text)
self.db.set_property('RS485_COMMAND_INFO', s + '<p>' + text + '</p>')
else:
self.db.set_property('RS485_COMMAND_INFO', s.replace(text, replace_text))
def _send_commands(self):
command = self.db.get_property('RS485_COMMAND')
if command == "":
return
self.db.set_property('RS485_COMMAND_INFO', '')
for dev in self.db.controllers:
error_text = "Контроллер '%s' не ответил." % dev[1]
if command == "SCAN_OW":
self._command_info("Запрос поиска OneWire устройств для контроллера '%s'..." % dev[1])
if self.send_pack(dev[0], self.PACK_COMMAND, ["SCAN_ONE_WIRE", ""]):
self._command_info("Пауза 3с...")
time.sleep(3)
self._command_info("Запрос списка найденых на шине OneWire устройств для контроллера '%s'" % dev[1])
is_ok = False
for res_pack in self.send_pack(dev[0], self.PACK_COMMAND, ["LOAD_ONE_WIRE_ROMS", ""]):
count = 0
allCount = len(res_pack[2][1])
is_ok = True
for rom in res_pack[2][1]:
rom_s = []
for r in rom:
ss = hex(r).upper()
if len(ss) == 3:
ss = ss.replace("0X", "0x0")
else:
ss = ss.replace("0X", "0x")
rom_s += [ss]
rom_s += [", "]
self._command_info("".join(rom_s[:-1]))
if self.db.append_scan_rom(dev[0], rom):
count += 1
self._command_info("Всего найдено устройств: %s. Новых: %s" % (allCount, count))
if is_ok == False:
self._command_info(error_text)
else:
self._command_info(error_text)
elif command == "CONFIG_UPDATE":
self.serialPort.timeout = 2
time.sleep(0.1)
try:
self._command_info("CONFIG FILE UPLOAD '%s'..." % dev[1])
#pack_data = self._str_to_hex(generate_config_file(self.db))
pack_data = generate_config_file(self.db)
self._command_info(str(len(pack_data)) + ' bytes.')
#bts = 512
#bts = 128
bts = 1024
cou = math.ceil(len(pack_data) / bts)
is_ok = False
c_pack = self.send_pack(dev[0], self.PACK_COMMAND, ["SET_CONFIG_FILE", cou, False], False)
if c_pack and self.check_lan_error == False:
prev_command = "Начало загрузки..."
self._command_info(prev_command)
for i in range(cou):
t = i * bts
s = pack_data[t:t + bts]
c_pack = self.send_pack(dev[0], self.PACK_COMMAND, ["SET_CONFIG_FILE", i + 1, s], i == cou - 1)
if c_pack != False and self.check_lan_error or (i == cou - 1):
is_ok = True
if i != cou - 1:
#Значит не долили файл
self._command_info("ВНИМАНИЕ: Загрузка прервана");
break
else:
new_command = self._gen_text_progress(i, cou)
self._command_info(prev_command, new_command)
prev_command = new_command
self._command_info(prev_command, self._gen_text_progress(cou, cou))
if is_ok:
self._command_info("OK")
else:
self._command_info(error_text)
except:
pass
self.serialPort.timeout = self.fast_timeput
self.check_lan_error = False
elif command == "REBOOT_CONTROLLERS":
self.serialPort.timeout = 1
self._command_info("Запрос перезагрузки контроллера '%s'..." % dev[1])
if self.send_pack(dev[0], self.PACK_COMMAND, ["REBOOT_CONTROLLER", ""]):
self._command_info("OK")
else:
self._command_info(error_text)
self.serialPort.timeout = self.fast_timeput
elif command == "GET_OW_VALUES":
pass
self.db.set_property('RS485_COMMAND', '')
self._command_info("Готово.")
time.sleep(2)
self._command_info("TERMINAL EXIT")
def _gen_text_progress(self, pos, max_pos):
i = round(pos * 100 / max_pos)
s = "<center>["
s += ("<span style=\"color:#ffffff;\">|</span>") * (i + 1)
s += ("<span style=\"color:#000000;\">|</span>") * (100 - i - 1)
s += "] " + str(i) + "% </center>"
return s
"""
def _str_to_hex(self, text):
res = []
for c in text:
s = hex(ord(c)).replace('0x', '')
if len(s) == 1:
s = '0' + s
res += [s]
return "".join(res)
"""
SYNC_STATE = ""
def run(self):
serialPort = self.serialPort
while True:
# Синхронизируем переменные между сервером и контроллерами
SYNC_STATE = self.db.get_property('SYNC_STATE')
stateChange = SYNC_STATE != self.SYNC_STATE
if SYNC_STATE == "RUN":
if stateChange:
print("Синхронизация запущена")
try:
self._sync_variables()
except:
pass
else:
if stateChange:
print("Синхронизация остановлена")
time.sleep(0.1)
self.SYNC_STATE = SYNC_STATE
# Рассылаем системные комманды, если требуется
self._send_commands()
class StackoverflowDumpToDBService:
def __init__(self):
self.db = DBConnector()
def import_posts_from_file(self, from_date, until_date, file_path,
table_name):
sql_query = "SELECT AcceptedAnswerId from android_posts where AcceptedAnswerId IS NOT NULL;"
cursor = self.db.get_cursor()
cursor.execute(sql_query)
acceptedAnswers = set(
[AcceptedAnswerId[0] for AcceptedAnswerId in cursor.fetchall()])
print(type(list(acceptedAnswers)[0]))
print("number of accepted answers:" + str(len(acceptedAnswers)))
print("number of accepted answers:" + str(len(set(acceptedAnswers))))
f = io.open(file_path, 'r', encoding="utf8")
f.readline()
f.readline()
row_nums = 0
scanned_row_nums = 0
counter = 0
while True:
current_line = f.readline()
scanned_row_nums += 1
if scanned_row_nums % 1000 == 0:
print("scanned_row_nums:" + str(scanned_row_nums))
if scanned_row_nums <= 6786000:
continue
if not current_line:
break
try:
root = ElementTree.fromstring(current_line.encode('utf-8'))
except Exception as e:
print(e)
print(
"----------------------------------------------------------------------"
)
# print("this line contains error")
# print(current_line)
# print("----------------------------------------------------------------------")
continue
attributes = root.attrib
post_id = long(attributes.get('Id'))
attributes['CreationDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
creation_date = datetime.datetime.strptime(
attributes['CreationDate'], '%Y-%m-%d %H:%M:%S')
if creation_date < from_date:
continue
if creation_date >= until_date:
break
if post_id not in acceptedAnswers:
continue
acceptedAnswers = acceptedAnswers - set([post_id])
attributes['LastEditDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
attributes['LastActivityDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
attributes['CommunityOwnedDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
attributes['AcceptedAnswerId'] = attributes.get('AcceptedAnswerId')
attributes['ViewCount'] = attributes.get('ViewCount', 0)
attributes['LastEditorDisplayName'] = attributes.get(
'LastEditorDisplayName')
attributes['Title'] = attributes.get('Title')
attributes['Tags'] = attributes.get('Tags')
attributes['OwnerUserId'] = attributes.get('OwnerUserId')
attributes['AnswerCount'] = attributes.get('AnswerCount', 0)
attributes['FavoriteCount'] = attributes.get('FavoriteCount', 0)
attributes['LastEditorUserId'] = attributes.get('LastEditorUserId')
attributes['Score'] = int(attributes.get('Score'))
attributes['ParentId'] = attributes.get('ParentId', None)
try:
query = '''
INSERT INTO ''' + table_name + ''' (id,PostTypeId,AcceptedAnswerId,CreationDate,Score,ViewCount,Body,OwnerUserId,
LastEditorUserId,LastEditorDisplayName,LastEditDate,LastActivityDate,Title,Tags,AnswerCount,CommentCount,FavoriteCount,CommunityOwnedDate,ParentId)
VALUES (%(Id)s,%(PostTypeId)s,%(AcceptedAnswerId)s,%(CreationDate)s,%(Score)s,%(ViewCount)s,%(Body)s,
%(OwnerUserId)s,%(LastEditorUserId)s,%(LastEditorDisplayName)s,
%(LastEditDate)s,%(LastActivityDate)s,%(Title)s,%(Tags)s,%(AnswerCount)s,%(CommentCount)s,
%(FavoriteCount)s,%(CommunityOwnedDate)s,%(ParentId)s)'''
self.db.get_cursor().execute(query, attributes)
except Exception as e:
print(attributes)
raise e
row_nums += 1
counter += 1
if row_nums == 1000:
self.db.get_connection().commit()
row_nums = 0
print("%s records has been inserted! last date was %s" %
(counter, creation_date))
self.db.get_connection().commit()
self.db.get_cursor().close()
#!/usr/bin/python3.4
#-*- coding: utf-8 -*-
from db_connector import DBConnector
import datetime
db = DBConnector()
print(datetime.datetime.now())
n = (12 * 30 * 24 * 3600) / 1545
q = db.query(" select 1, MIN(VALUE), MAX(VALUE) "
" from core_variable_changes "
" where VARIABLE_ID = 59 "
" group by ROUND(UNIX_TIMESTAMP(CHANGE_DATE) / %s) " % n, [])
row = q.fetchone()
c = 0
while row:
row = q.fetchone()
c += 1
q.close()
print(datetime.datetime.now())
print(c)
class PackageManagers(object):
"""Collect time stamped package manager data from various package managers
and store in a DB"""
def __init__(self):
self.db = DBConnector()
def update_package_manager_data(self, package_manager_urls):
"""Gets data given package manager urls and adds it to the DB
:param package_manager_urls: URL(s) to the package you want to obtain
download data from
:type package_manager_urls: Array of strings
:returns: Returns the data object that was added to the DB
:rtype: Data object
"""
num_total_csharp_downloads = None
num_nodejs_monthly_downloads = None
num_php_downloads = None
num_python_downloads = None
num_ruby_downloads = None
num_python_http_client_downloads = None
num_python_open_source_library_data_collector_downloads = None
num_ruby_http_client_downloads = None
num_csharp_http_client_downloads = None
num_php_http_client_downloads = None
num_node_http_client_downloads = None
for url in package_manager_urls:
if 'https://www.nuget.org/packages/SendGrid' == url:
num_total_csharp_downloads = self.csharp_downloads(url)
if 'https://www.nuget.org/packages/SendGrid.CSharp.HTTP.Client' == url:
num_csharp_http_client_downloads = self.csharp_downloads(url)
if 'https://www.npmjs.com/package/sendgrid' in url:
if 'https://www.npmjs.com/package/sendgrid-rest' != url:
num_nodejs_monthly_downloads = self.nodejs_downloads(url)
if 'https://www.npmjs.com/package/sendgrid-rest' in url:
num_node_http_client_downloads = self.nodejs_downloads(url)
if 'https://packagist.org/packages/sendgrid/sendgrid' == url:
num_php_downloads = self.php_downloads(url)
if 'https://packagist.org/packages/sendgrid/php-http-client' == url:
num_php_http_client_downloads = self.php_downloads(url)
if 'pypi' in url and 'sendgrid' in url:
num_python_downloads = self.python_downloads(url)
if 'pypi' in url and 'python_http_client' in url:
num_python_http_client_downloads = self.python_downloads(url)
if 'pypi' in url and 'open_source_library_data_collector' in url:
num_python_open_source_library_data_collector_downloads = self.python_downloads(url)
if 'rubygems' in url and 'sendgrid' in url:
num_ruby_downloads = self.ruby_downloads(url)
if 'rubygems' in url and 'http' in url:
num_ruby_http_client_downloads = self.ruby_downloads(url)
return self.update_db(num_total_csharp_downloads,
num_nodejs_monthly_downloads,
num_php_downloads,
num_python_downloads,
num_ruby_downloads,
num_python_http_client_downloads,
num_python_open_source_library_data_collector_downloads,
num_ruby_http_client_downloads,
num_csharp_http_client_downloads,
num_php_http_client_downloads,
num_node_http_client_downloads)
def csharp_downloads(self, url):
"""Gets library download data from nuget.org
:param url: the URL of the package
:type url: string
:returns: The number of total library downloads
:rtype: Integer
"""
r = requests.get(url)
soup = BeautifulSoup(r.text, "html.parser")
mydivs = soup.findAll("p", {"class": "stat-number"})
nodes = []
for node in mydivs:
nodes.append(''.join(node.findAll(text=True)))
num_total_csharp_downloads = nodes[0].replace(',', '')
return num_total_csharp_downloads
def nodejs_downloads(self, url):
"""Gets library download data from npmjs.org
:param url: the URL of the package
:type url: string
:returns: The number of library downloads in the last month
:rtype: Integer
"""
r = requests.get(url)
soup = BeautifulSoup(r.text, "html.parser")
mydivs = soup.findAll("strong",
{"class": "pretty-number monthly-downloads"})
nodes = []
for node in mydivs:
nodes.append(''.join(node.findAll(text=True)))
num_nodejs_monthly_downloads = nodes[0].replace(',', '')
return num_nodejs_monthly_downloads
def php_downloads(self, url):
"""Gets library download data from packagist.org
:param url: the URL of the package
:type url: string
:returns: The number of total library downloads
:rtype: Integer
"""
r = requests.get(url)
soup = BeautifulSoup(r.text, "html.parser")
mydivs = soup.findAll("div",
{"class": "facts col-xs-12 col-sm-6 col-md-12"})
nodes = []
for node in mydivs:
nodes.append(''.join(node.findAll(text=True)))
num_php_downloads = nodes[0][11:].replace(u('\u2009'), '').split('\n')
num_php_downloads = str(num_php_downloads[0])
return num_php_downloads
def python_downloads(self, url):
"""Gets library download data from pypi.python.org
:param url: the URL of the package
:type url: string
:returns: The number of library downloads in the last month
:rtype: Integer
"""
r = requests.get(url)
soup = BeautifulSoup(r.text, "html.parser")
mydivs = soup.findAll("ul", {"class": "nodot"})
nodes = []
for node in mydivs:
nodes.append(''.join(node.findAll(text=True)))
num_python_downloads = \
nodes[0].replace(u('\n'), '') \
.rpartition('week')[-1] \
.rpartition('downloads')[0][2:] \
.replace(u('\u2009'), '')
return num_python_downloads
def ruby_downloads(self, url):
"""Gets library download data from rubygems.org
:param url: the URL of the package
:type url: string
:returns: The number of total library downloads
:rtype: Integer
"""
r = requests.get(url)
soup = BeautifulSoup(r.text, "html.parser")
mydivs = soup.findAll("span", {"class": "gem__downloads"})
nodes = []
for node in mydivs:
nodes.append(''.join(node.findAll(text=True)))
num_ruby_downloads = nodes[0].replace(',', '')
return num_ruby_downloads
def update_db(
self,
num_total_csharp_downloads,
num_nodejs_monthly_downloads,
num_php_downloads,
num_python_downloads,
num_ruby_downloads,
num_python_http_client_downloads,
num_python_open_source_library_data_collector_downloads,
num_ruby_http_client_downloads,
num_csharp_http_client_downloads,
num_php_http_client_downloads,
num_node_http_client_downloads
):
"""Update the DB with the package manager data
:param num_total_csharp_downloads: # of total downloads
:param num_nodejs_monthly_downloads: # of downloads in the last month
:param num_php_downloads: # of total downloads
:param num_python_downloads: # of downloads in the last month
:param num_ruby_downloads: # of total downloads
:type num_total_csharp_downloads: Integer
:type num_nodejs_monthly_downloads: Integer
:type num_php_downloads: Integer
:type num_python_downloads: Integer
:type num_ruby_downloads: Integer
:returns: Returns the data object that was added to the DB
:rtype: Data object
"""
packagedata = PackageManagerData(
date_updated=datetime.datetime.now(),
csharp_downloads=num_total_csharp_downloads,
nodejs_downloads=num_nodejs_monthly_downloads,
php_downloads=num_php_downloads,
python_downloads=num_python_downloads,
ruby_downloads=num_ruby_downloads,
python_http_client_downloads=num_python_http_client_downloads,
csharp_http_client_downloads=num_csharp_http_client_downloads,
ruby_http_client_downloads=num_ruby_http_client_downloads,
php_http_client_downloads=num_php_http_client_downloads,
open_source_library_data_collector_downloads=num_python_open_source_library_data_collector_downloads,
node_http_client_downloads=num_node_http_client_downloads
)
return self.db.add_data(packagedata)
def test_valid_credentials(self):
connector = DBConnector(db_name=self.test_db_name)
response = connector.get_connection()
self.assertIsNotNone(response)
class StackoverflowDumpToDBService:
def __init__(self):
self.db = DBConnector()
def import_posts_from_file(self, from_date: datetime, until_date: datetime,
file_path: str, table_name: str):
f = open(file_path, 'r', encoding="utf8")
f.readline()
f.readline()
row_nums = 0
counter = 0
while True:
current_line = f.readline()
if not current_line:
break
try:
root = ElementTree.fromstring(current_line)
except:
print(
"----------------------------------------------------------------------"
)
print("this line contains error")
print(current_line)
print(
"----------------------------------------------------------------------"
)
continue
attributes = root.attrib
attributes['CreationDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
attributes['LastEditDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
attributes['LastActivityDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
attributes['CommunityOwnedDate'] = re.sub(
r'\.\d+', '', attributes['CreationDate'].replace('T', ' '))
attributes['AcceptedAnswerId'] = attributes.get('AcceptedAnswerId')
attributes['ViewCount'] = attributes.get('ViewCount', 0)
attributes['LastEditorDisplayName'] = attributes.get(
'LastEditorDisplayName')
attributes['Title'] = attributes.get('Title')
attributes['Tags'] = attributes.get('Tags')
attributes['OwnerUserId'] = attributes.get('OwnerUserId')
attributes['AnswerCount'] = attributes.get('AnswerCount', 0)
attributes['FavoriteCount'] = attributes.get('FavoriteCount', 0)
attributes['LastEditorUserId'] = attributes.get('LastEditorUserId')
attributes['Score'] = int(attributes.get('Score'))
attributes['ParentId'] = attributes.get('ParentId', None)
creation_date = datetime.datetime.strptime(
attributes['CreationDate'], '%Y-%m-%d %H:%M:%S')
if creation_date < from_date:
continue
if creation_date >= until_date:
break
if should_be_skipped(attributes):
continue
try:
query = '''
INSERT INTO ''' + table_name + ''' (id,PostTypeId,AcceptedAnswerId,CreationDate,Score,ViewCount,Body,OwnerUserId,
LastEditorUserId,LastEditorDisplayName,LastEditDate,LastActivityDate,Title,Tags,AnswerCount,CommentCount,FavoriteCount,CommunityOwnedDate,ParentId)
VALUES (%(Id)s,%(PostTypeId)s,%(AcceptedAnswerId)s,%(CreationDate)s,%(Score)s,%(ViewCount)s,%(Body)s,
%(OwnerUserId)s,%(LastEditorUserId)s,%(LastEditorDisplayName)s,
%(LastEditDate)s,%(LastActivityDate)s,%(Title)s,%(Tags)s,%(AnswerCount)s,%(CommentCount)s,
%(FavoriteCount)s,%(CommunityOwnedDate)s,%(ParentId)s)'''
self.db.get_cursor().execute(query, attributes)
except Exception as e:
print(attributes)
raise e
row_nums += 1
counter += 1
if row_nums == 1000:
self.db.get_connection().commit()
row_nums = 0
print("%s records has been inserted! last date was %s" %
(counter, creation_date))
self.db.get_connection().commit()
self.db.get_cursor().close()
from pyspark.ml.evaluation import RegressionEvaluator
from pyspark.ml.recommendation import ALS
from pyspark.sql import SparkSession
from pyspark import SparkContext
sc = SparkContext("spark://vm1:7077", "StreamProcessing")
sc.addPyFile('/home/tom/Spark-Recommendation-System/db_connector.py')
sc.setLogLevel("ERROR")
from db_connector import DBConnector
TRIGGER_INTERVAL = 30 # in seconds
TOPIC_NAME = 'spark_streaming'
KAFKA_PORT = 'vm1:2181'
db = DBConnector('streaming_db')
def fit_model(df):
als = ALS(maxIter=10,
regParam=0.01,
userCol="userId",
itemCol="movieId",
ratingCol="rating",
coldStartStrategy="drop")
model = als.fit(df)
return model
def to_numbers(pair):
# convert the input from kafka to a tuple of numbers
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
import json
from datetime import datetime
import flask
import flask_restful
from db_connector import DBConnector
app = flask.Flask(__name__)
api = flask_restful.Api(app)
db_connector = DBConnector()
class DatabasesResource(flask_restful.Resource):
"""API /databases endpoint."""
def post(self):
"""Create new database. Expected request json: {"database_name": <database_name>}."""
request_body = json.loads(flask.request.data.decode())
database_name = request_body["database_name"]
if not database_name:
flask_restful.abort(400, message="Missing database name")
db_connector.db_create(database_name)
return flask.Response("OK")
def categories():
con = DBConnector(abs_path('databases/offer_classification.db'))
return con.fetch_categories()
#!/usr/bin/python3.4
#-*- coding: utf-8 -*-
from db_connector import DBConnector
import datetime
db = DBConnector()
vals = []
i = 1
min_y = 9999999
max_y = -9999999
for rec in db.select("select UNIX_TIMESTAMP(CHANGE_DATE) - 1461412404, VALUE * 50 "
" from core_variable_changes "
" where VARIABLE_ID = 59 "
" order by 1 "):
vals += [str(rec[0] / 10000), ",", str(round(rec[1])), " "]
min_y = min(min_y, rec[1])
max_y = max(max_y, rec[1])
size = "%s %s %s %s" % (vals[0], round(min_y), vals[-4], round(max_y))
print(size)
s = ('<svg width="100%" height="100%" viewBox="' + size + '"><polyline stroke="#979797" fill="none" stroke-width="1" '
'points="' + "".join(vals[:-1]) + '"/>'
'</svg>')
f = open("data.html", "w")
f.write(s)
f.close()
def __init__(self):
self.db = DBConnector()
class Main():
def __init__(self):
self.db = DBConnector()
self.db.IUD("update core_scheduler set ACTION_DATETIME = NULL")
self.db.commit()
print("-- Предстоящие задачи --")
for row in self.db.select("select ID, COMM, ACTION, ACTION_DATETIME, INTERVAL_TIME_OF_DAY, INTERVAL_DAY_OF_TYPE, INTERVAL_TYPE from core_scheduler"):
next_time = self.parse_time(None, str(row[4], "utf-8"), str(row[5], "utf-8"), row[6])
print("[%s] %s" % (datetime.datetime.fromtimestamp(next_time), str(row[1], "utf-8")))
print("------------------------")
self.check_time()
self.run()
def check_time(self):
now = datetime.datetime.now().timestamp()
for row in self.db.select("select ID, COMM, ACTION, ACTION_DATETIME, INTERVAL_TIME_OF_DAY, INTERVAL_DAY_OF_TYPE, INTERVAL_TYPE from core_scheduler"):
next_time = None
if row[3] == None: # Это обнуленная дата - будет перещитана в холостую относительно текущей
next_time = self.parse_time(None, str(row[4], "utf-8"), str(row[5], "utf-8"), row[6])
elif row[3].timestamp() <= now: # Это дата, что пришла для выполнения. Выполняем и перещитываем.
next_time = self.parse_time(row[3].timestamp(), str(row[4], "utf-8"), str(row[5], "utf-8"), row[6])
self.execute(str(row[1], "utf-8"), str(row[2], "utf-8"))
if next_time != None:
#self.db.IUD("update core_scheduler set ACTION_DATETIME = FROM_UNIXTIME(%s) where ID = %s" % (next_time, row[0]))
d_s = datetime.datetime.fromtimestamp(next_time).strftime("%Y-%m-%d %H:%M:%S")
self.db.IUD("update core_scheduler set ACTION_DATETIME = '%s' where ID = %s" % (d_s, row[0]))
self.db.commit()
def calc_suntime(self, d, sun_type):
st = GetSunTime(d // (24 * 3600), 49.697287, 34.354388, 90.8333333333333, (-time.altzone // 3600) - 1, sun_type)
hour = math.trunc(st)
minutes = round((st - hour) * 60)
return (hour * 60 + minutes) * 60
def parse_time(self, action_datetime, time_of_day, day_of_type, int_type):
if action_datetime == None:
action_datetime = datetime.datetime.now().timestamp();
now = datetime.datetime.now()
now = datetime.datetime(now.year, now.month, now.day)
times = []
dates = []
time_type = ""
try:
time_of_day.upper().index("SUNRISE")
time_type = "Sunrise"
except:
pass
try:
time_of_day.upper().index("SUNSET")
time_type = "Sunset"
except:
pass
if time_type == "Sunrise" or time_type == "Sunset":
"""
Это особый случай блуждающего времени.
Сборка даты/времени выполняется здесь отдельно и дальше код
не пойдет.
"""
d1 = now.timestamp()
d2 = now.timestamp() + 24 * 3600
dt = [d1 + self.calc_suntime(d1, time_type),
d2 + self.calc_suntime(d2, time_type)]
dt.sort()
# Проверяем какая дата из расписания готова к выполнению
for d in dt:
if d > action_datetime:
return d
return None
else:
# Получаем список времени в секундах
for t in time_of_day.split(","):
m = t.split(":")
hour = int(m[0].strip()) * 60
minutes = 0
try:
minutes = int(m[1].strip())
except:
pass
sec = 0
try:
sec = int(m[2].strip())
except:
pass
s = hour + minutes
times += [s * 60 + sec]
if int_type == 0:
# Сегодняшняя дата и завтрашняя
dates += [now.timestamp(), now.timestamp() + 24 * 3600]
elif int_type == 1:
# Получаем дату понедельника этой недели в секундах
dw = now.timestamp() - now.weekday() * 24 * 3600
# Получаем дату понедельника следующей недели в секундах
dw_next = dw + 7 * 24 * 3600
w = ["пн", "вт", "ср", "чт", "пт", "сб", "вс"]
for d in day_of_type.split(","):
s = w.index(d.strip().lower())
dates += [dw + (s * 24 * 3600)]
dates += [dw_next + (s * 24 * 3600)]
elif int_type == 2:
# Получаем 1 число этого месяца в секундах
m = datetime.datetime(now.year, now.month, 1).timestamp()
# Получаем 1 число следующего месяца в секундах
if now.month < 12:
m_next = datetime.datetime(now.year, now.month, 1).timestamp()
else:
m_next = datetime.datetime(now.year + 1, 1, 1).timestamp()
for d in day_of_type.split(","):
s = int(d.strip())
dates += [m + (s * 24 * 3600)]
dates += [m_next + (s * 24 * 3600)]
elif int_type == 3:
for d in day_of_type.split(","):
m = d.split("-")
s = datetime.datetime(now.year, int(m[1].strip()), int(m[0].strip())).timestamp()
dates += [s]
s_next = datetime.datetime(now.year + 1, int(m[1].strip()), int(m[0].strip())).timestamp()
dates += [s_next]
dt = []
# Собираем дату и время расписания в одно
for tim in times:
if len(dates) > 0:
for dat in dates:
dt += [dat + tim]
dt.sort()
# Проверяем какая дата из расписания готова к выполнению
for d in dt:
if d > action_datetime:
return d
return None
def execute(self, comm, action):
self.db.IUD("insert into core_execute (COMMAND) values ('%s')" % action)
self.db.commit()
print("[%s] Произошло событие \"%s\"" % (time.strftime("%d-%m-%Y %H:%M"), comm))
print(" и запрошена команда %s" % (action))
def run(self):
while True:
self.check_time()
time.sleep(1)
def test_empty_db_name(self):
connector = DBConnector(db_name=self.test_empty_db)
self.assertIsNone(connector.connection)
connection = connector.get_connection()
self.assertIsNone(connection)
class Main():
ALARM_FILES = []
ALARM_LOOPS = 5
def __init__(self):
self.player = False
self.db = DBConnector()
self.timer_id = 101
self.timer_on = 102
self.timer_len = 0.5
self.get_start_time()
self.volume = 0
self.run()
def _curr_time(self, curr = True):
if curr:
return time.strftime("%d-%m-%Y %H:%M:%S", time.localtime())
else:
return time.strftime("%d-%m-%Y %H:%M:%S", time.localtime(self.time_start))
def get_start_time(self):
for row in self.db.select("select VALUE from core_variables where ID = %s" % (self.timer_id)):
lt = time.localtime()
h = math.floor(row[0])
m = round((row[0] - h) * 60)
self.time_start = time.mktime((lt.tm_year, lt.tm_mon, lt.tm_mday, h, m, 0, 0, 0, lt.tm_isdst))
if time.time() > self.time_start:
self.time_start += 24 * 3600
print("Время будильника установлено на: %s" % self._curr_time(False))
def check_time(self):
if time.time() >= self.time_start:
self.db.IUD("call CORE_SET_VARIABLE(%s, %s, null)" % (self.timer_on, 1))
self.db.commit()
self.get_start_time()
def run(self):
while True:
for row in self.db.variable_changes():
if row[1] == self.timer_id:
try:
self.time_start = row[2]
self.get_start_time()
except:
pass
elif row[1] == self.timer_on:
if row[2]:
print("%s Начали будить" % self._curr_time())
self.start()
else:
self.user_stop()
elif row[3] == 2: #Реакция на любой выключатель
self.user_stop()
if self.player:
self.volume += 1
if self.volume > 60:
self.volume = 60
self.send_cmd("volume %s 100" % round(40 + self.volume))
self.check_time()
time.sleep(1)
def user_stop(self):
if self.player:
print("%s Проснулись" % self._curr_time())
self.stop()
def start(self):
if self.player:
return
self.volume = 0
self.player = Popen(["mplayer", "-slave", "-quiet", "-loop", str(Main.ALARM_LOOPS), "-volume", "0"] + Main.ALARM_FILES,
stdin=PIPE, stdout=PIPE, stderr=PIPE)
def stop(self):
self.send_cmd("q")
self.player = False
def send_cmd(self, cmd):
if self.player:
try:
self.player.stdin.write((cmd + "\n").encode("utf-8"))
self.player.stdin.flush()
except:
self.player = False
print("%s Не разбудили" % self._curr_time())
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.db = DBConnector()
from db_connector import DBConnector, GitHubData, PackageManagerData
from config import Config
from github import GitHub
from package_managers import PackageManagers
from sendgrid_email import SendGrid
config = Config()
db = DBConnector()
github = GitHub()
pm = PackageManagers()
sg = SendGrid()
def update(send_email=True):
# Update the DB with the GitHub repo data
for repo in config.github_repos:
github.update_library_data(config.github_user, repo)
# Update the DB with Package Manager data
pm.update_package_manager_data(config.package_manager_urls)
# Export tables as CSV if config file indicates to do so
if config.export_github:
db.export_table_to_csv(GitHubData)
if config['export_tables']['PackageManagers']:
db.export_table_to_csv(PackageManagerData)
if not send_email:
return
# Send an email update
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.pm = PackageManagers()
self.db = DBConnector()
self.config = Config()
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.pm = PackageManagers()
self.db = DBConnector()
self.config = Config()
class Main():
SERIAL_PORT = "/dev/ttyUSB0"
SERIAL_SPEED = 57600
PACK_SYNC = 1
PACK_COMMAND = 2
PACK_ERROR = 3
def __init__(self):
self.fast_timeput = 0.05
self.check_lan_error = False
self.sync_error_counter = []
# Connect to serial port
try:
self.serialPort = serial.Serial(self.SERIAL_PORT, self.SERIAL_SPEED, parity='O', timeout=self.fast_timeput)
except:
print("Ошибка подключения к '%s'" % self.SERIAL_PORT)
self.db = DBConnector()
self.db.load_controllers()
self.queue = []
# Run main loop
self.run()
def send_pack(self, dev_id, pack_type, pack_data, flush=True):
buf = json.dumps([dev_id, pack_type, pack_data]).encode("utf-8")
buf += bytearray([0x0])
c = 5
for err in range(c):
self.serialPort.write(buf)
if flush:
self.serialPort.flush()
#print(buf)
res = self.check_lan()
if self.check_lan_error == False:
break
if err < (c - 1):
self.check_lan_error = False
return res
def _store_variable_to_db(self, dev_id, pack_data):
for var in pack_data:
if len(var) > 1:
self.db.set_variable_value(var[0], var[1], dev_id)
def check_lan(self):
try:
buf = self.serialPort.readline()
if len(buf) > 0:
#print(buf)
resp = buf.decode("utf-8")
data = []
for pack in resp.split(chr(0x0)):
if len(pack) > 0:
d = json.loads(pack)
data += [d]
if d[1] == self.PACK_ERROR:
self.check_lan_error = True
for s in d[2]:
self._command_info("%s" % s)
return data
else:
return False
except Exception as e:
self.check_lan_error = True
self._command_info("EXCEPT {}".format(e.args))
return False
def _sync_variables(self):
# Зачитываем изменения в БД
var_data = self.db.variable_changes()
# Рассылаем изменения в БД и паралельно читаем обновления
for dev in self.db.controllers:
se_ignore = False
for i in range(len(self.sync_error_counter)):
if self.sync_error_counter[i][0] == dev[0]:
self.sync_error_counter[i][1] -= 1
if self.sync_error_counter[i][1] == 0:
del self.sync_error_counter[i]
else:
se_ignore = True
if se_ignore == False:
pack_data = []
lt = time.localtime()
t = time.mktime((2000, 1, 1, 0, 0, 0, 0, 0, lt.tm_isdst))
pack_data += [[-100, round(time.time() - t)]] #Передаем системное время в контроллеры
for var in var_data:
if var[2] != dev[0]:
pack_data += [[var[0], var[1]]]
date = datetime.datetime.now().strftime('%H:%M:%S')
print("[%s] SYNC. '%s': " % (date, dev[1]), end="")
cl = self.send_pack(dev[0], self.PACK_SYNC, pack_data)
if cl:
for res_pack in cl:
if res_pack[2] == "RESET":
print("RESET ", end="")
is_ok = self.send_pack(dev[0], self.PACK_SYNC, self._reset_pack()) != False
for r in range(30):
if self.check_lan():
is_ok = True
break
if is_ok:
print("OK\n")
else:
print("ERROR\n")
self.sync_error_counter += [[dev[0], 30]]
else:
self._store_variable_to_db(res_pack[0], res_pack[2])
print("OK")
print(" >> ", pack_data)
print(" << ", res_pack[2], "\n")
else:
print("ERROR\n")
self.sync_error_counter += [[dev[0], 30]]
def _reset_pack(self):
return self.db.all_variables();
def _command_info(self, text, replace_text = None):
text = text.replace("'", "`")
text = text.replace('"', '\"')
s = self.db.get_property('RS485_COMMAND_INFO')
if replace_text == None:
text = text.replace("<", "<")
text = text.replace(">", ">")
print(text)
self.db.set_property('RS485_COMMAND_INFO', s + '<p>' + text + '</p>')
else:
self.db.set_property('RS485_COMMAND_INFO', s.replace(text, replace_text))
def _send_commands(self):
command = self.db.get_property('RS485_COMMAND')
if command == "":
return
self.db.set_property('RS485_COMMAND_INFO', '')
for dev in self.db.controllers:
error_text = "Контроллер '%s' не ответил." % dev[1]
if command == "SCAN_OW":
self._command_info("Запрос поиска OneWire устройств для контроллера '%s'..." % dev[1])
if self.send_pack(dev[0], self.PACK_COMMAND, ["SCAN_ONE_WIRE", ""]):
self._command_info("Пауза 3с...")
time.sleep(3)
self._command_info("Запрос списка найденых на шине OneWire устройств для контроллера '%s'" % dev[1])
is_ok = False
for res_pack in self.send_pack(dev[0], self.PACK_COMMAND, ["LOAD_ONE_WIRE_ROMS", ""]):
count = 0
allCount = len(res_pack[2][1])
is_ok = True
for rom in res_pack[2][1]:
rom_s = []
for r in rom:
ss = hex(r).upper()
if len(ss) == 3:
ss = ss.replace("0X", "0x0")
else:
ss = ss.replace("0X", "0x")
rom_s += [ss]
rom_s += [", "]
self._command_info("".join(rom_s[:-1]))
if self.db.append_scan_rom(dev[0], rom):
count += 1
self._command_info("Всего найдено устройств: %s. Новых: %s" % (allCount, count))
if is_ok == False:
self._command_info(error_text)
else:
self._command_info(error_text)
elif command == "CONFIG_UPDATE":
self.serialPort.timeout = 2
time.sleep(2)
try:
self._command_info("CONFIG FILE UPLOAD '%s'..." % dev[1])
#pack_data = self._str_to_hex(generate_config_file(self.db))
pack_data = generate_config_file(self.db)
self._command_info(str(len(pack_data)) + ' bytes.')
#bts = 512
bts = 1024
cou = math.ceil(len(pack_data) / bts)
is_ok = False
c_pack = self.send_pack(dev[0], self.PACK_COMMAND, ["SET_CONFIG_FILE", cou, False], False)
if c_pack and self.check_lan_error == False:
prev_command = "Начало загрузки..."
self._command_info(prev_command)
for i in range(cou):
t = i * bts
s = pack_data[t:t + bts]
c_pack = self.send_pack(dev[0], self.PACK_COMMAND, ["SET_CONFIG_FILE", i + 1, s], i == cou - 1)
if c_pack != False and self.check_lan_error or (i == cou - 1):
is_ok = True
if i != cou - 1:
#Значит не долили файл
self._command_info("ВНИМАНИЕ: Загрузка прервана");
break
else:
new_command = self._gen_text_progress(i, cou)
self._command_info(prev_command, new_command)
prev_command = new_command
self._command_info(prev_command, self._gen_text_progress(cou, cou))
if is_ok:
self._command_info("OK")
else:
self._command_info(error_text)
except:
pass
self.serialPort.timeout = self.fast_timeput
self.check_lan_error = False
elif command == "REBOOT_CONTROLLERS":
self.serialPort.timeout = 1
self._command_info("Запрос перезагрузки контроллера '%s'..." % dev[1])
if self.send_pack(dev[0], self.PACK_COMMAND, ["REBOOT_CONTROLLER", ""]):
self._command_info("OK")
else:
self._command_info(error_text)
self.serialPort.timeout = self.fast_timeput
elif command == "GET_OW_VALUES":
pass
self.db.set_property('RS485_COMMAND', '')
self._command_info("Готово.")
time.sleep(2)
self._command_info("TERMINAL EXIT")
def _gen_text_progress(self, pos, max_pos):
i = round(pos * 100 / max_pos)
s = "<center>["
s += ("<span style=\"color:#ffffff;\">|</span>") * (i + 1)
s += ("<span style=\"color:#000000;\">|</span>") * (100 - i - 1)
s += "] " + str(i) + "% </center>"
return s
"""
def _str_to_hex(self, text):
res = []
for c in text:
s = hex(ord(c)).replace('0x', '')
if len(s) == 1:
s = '0' + s
res += [s]
return "".join(res)
"""
SYNC_STATE = ""
def run(self):
serialPort = self.serialPort
while True:
# Синхронизируем переменные между сервером и контроллерами
SYNC_STATE = self.db.get_property('SYNC_STATE')
stateChange = SYNC_STATE != self.SYNC_STATE
if SYNC_STATE == "RUN":
if stateChange:
print("Синхронизация запущена")
try:
self._sync_variables()
except:
pass
else:
if stateChange:
print("Синхронизация остановлена")
self.SYNC_STATE = SYNC_STATE
# Рассылаем системные комманды, если требуется
self._send_commands()
time.sleep(0.1)
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.db = DBConnector()
class Main():
def __init__(self):
self.prev_val = 0
#self.play_file("samples/on.wav")
self.db = DBConnector()
self.run()
def play_file(self, file):
CHUNK = 2048
FORMAT = pyaudio.paInt16
CHANNELS = 2
RATE = 16000
p = pyaudio.PyAudio()
stream = p.open(format=FORMAT,
channels=CHANNELS,
rate=RATE,
output=True)
f = wave.open(file,'rb')
prev_filter_val = 0
while True:
data = f.readframes(CHUNK)
if len(data) > 0:
samples = numpy.fromstring(data, numpy.int16)
prev_filter_val = self.sound_processor(samples, prev_filter_val)
stream.write(numpy.fromstring(samples, numpy.int8))
else:
break;
stream.stop_stream()
stream.close()
f.close()
p.terminate()
def sound_processor(self, data, pv):
res = pv
for i in range(len(data)):
val = data[i]
"""
# Фильтруем высокие частоты
c = pv + (val - pv) // 2
val = c
"""
# Корректируем громкость
val = val * 2
if val > 32000:
val = 32000
elif val < -32000:
val = -32000
data[i] = val
res = pv
return res
def run(self):
while True:
for row in self.db.variable_changes():
if row[3] == 1:
try:
self.play_file("samples/part_%s.wav" % (row[4]))
except:
pass
if row[2]:
self.play_file("samples/on.wav")
else:
self.play_file("samples/off.wav")
time.sleep(0.1)
def setUp(self):
if os.environ.get('TRAVIS') == None:
self.github = GitHub()
self.db = DBConnector()
self.config = Config()
from db_connector import DBConnector
from datetime import datetime
db = DBConnector()
date_from = datetime.now().timestamp() // 1 - 3600 * 24 * 15
all_count = 0
for rec in db.select("select COUNT(*) "
" from core_variable_changes c, core_variables v "
" where c.VARIABLE_ID = v.ID "
" and v.APP_CONTROL in (4, 13, 14, 11)"
" and UNIX_TIMESTAMP(c.CHANGE_DATE) > %s" %
(date_from)):
all_count = rec[0]
print("ВСЕГО ЗАПИСЕЙ: %s" % (all_count))
prev_var_id = -1
prev_var_time = None
prev_var_value = None
exit_check = False
values = []
def check_values(values, new_value):
if len(values) > 1:
row = values[-2]
if row[1] == new_value[1]:
return True
from db_connector import DBConnector
db = DBConnector()
all_count = 0
for rec in db.select("select COUNT(*) from core_variable_changes"):
all_count = rec[0]
print("ВСЕГО ЗАПИСЕЙ: %s" % (all_count))
db.query("update core_variable_changes"
" set value = ROUND(value * 10) / 10"
" where VARIABLE_ID = 153 ")
db.commit()
prev_var_id = -1
prev_var_value = None
i = 0
curr_count = 0
for row in db.select("select ID, VALUE, VARIABLE_ID "
" from core_variable_changes "
" where VARIABLE_ID = 153 "
" order by VARIABLE_ID, CHANGE_DATE "):
if row[2] == prev_var_id:
if row[1] == prev_var_value:
db.query("delete from core_variable_changes where ID = %s" %
(row[0]))
db.commit()
else:
prev_var_value = row[1]
#!/usr/bin/python3
#-*- coding: utf-8 -*-
import mysql.connector
from db_connector import DBConnector
import time
db = DBConnector()
for rec in db.select("select ID, VALUE from core_variable_changes "
" where VARIABLE_ID = 124 "
" and VALUE > 100"):
b = int(rec[1] * 16)
if (b & (1<<15)):
m = b
b = 0
for i in range(16):
if m & (1<<i) == 0:
b |= (1<<i)
b = -b
b += 1
print(b / 16)
db.IUD("update core_variable_changes set VALUE = %s where ID = %s" % ((b / 16), rec[0]))
db.commit()
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(
os.getenv("HOST"),
username=os.getenv("HOST_USER"),
password=os.getenv("HOST_PASSWORD")
)
cmd = "python /opt/stats_collector.py"
stdin, stdout, stderr = ssh.exec_command(cmd)
stats = json.loads(stdout.read())
ssh.close()
if resource is not None \
and resource in stats.keys():
stats = stats[resource]
return stats
if __name__ == "__main__":
conn = DBConnector()
stats = collect_stats()
for resource in stats.keys():
conn.submit_stats(resource, stats[resource])
conn.close()
