import argparse
import datetime as dt
import json
import time
from configparser import ConfigParser
from functools import wraps

import numpy as np
import requests
import xlsxwriter
from sklearn.linear_model import LinearRegression
from matplotlib import pyplot as plt
from mysql import connector
from pandas import date_range


parser = argparse.ArgumentParser()
parser.add_argument("-f", "--frequency", help="frekvence", type=str, choices=["hodina", "den", "tyden"], required=True)
parser.add_argument("-o", "--output", help="výstupní čtení", type=bool, default=True)
parser.add_argument("-c", "--compensation", help="kompenzace", type=float, default=1.0)
args_ = parser.parse_args()

np.set_printoptions(linewidth=100)

config = ConfigParser()
config.read("config.ini")

DatabaseAliasLive = "db-cme-knihy-cme-live"

hodina_arg = "hodina"
den_arg = "den"
tyden_arg = "tyden"

DNU_DOPREDU_MAXIMUM = 19

dnu_simulace = DNU_DOPREDU_MAXIMUM - dt.date.today().weekday()
den2datum = {k: str(dt.date.today() + dt.timedelta(days=k)) for k in range(DNU_DOPREDU_MAXIMUM)}
den2datum = {k: v[-2:] + "." + v[-5:-3] + "." for k, v in den2datum.items()}
day_dict = {0: "po", 1: "út", 2: "st", 3: "čt", 4: "pá", 5: "so", 6: "ne"}

prumerna_cena_kusu_na_minimalni_sklad = 178
smooth_coef = 0.6
legacy_prisun_naskladneni = True
pouzivame_plan = 1.5


def count_calls(func):
	@wraps(func)
	def wrapper(*args, **kwargs):
		wrapper.calls += 1
		return func(*args, **kwargs)

	wrapper.calls = 0
	return wrapper


@count_calls
def query_execute(cursor, query, args=None):
	cursor.execute(query, args)


def query_db(query_, databasealias, args=None):
	Con, Cur = get_db_cursor(databasealias)
	query_execute(Cur, query_, args)
	result = Cur.fetchall()
	Con.close()
	return result


def get_db_alias_exp(exp_name_):
	return f"db-cme-{exp_name_}-live"


def get_db_cursor(db_alias):
	Con = connector.connect(
		user=config[db_alias]["user"],
		password=config[db_alias]["password"],
		host=config[db_alias]["host"],
		database=config[db_alias]["database"]
	)
	Cur = Con.cursor()
	return Con, Cur


def get_active_exps():
	query_exps = """
		SELECT ex.expedice_id, expedice_databaze
		FROM
		(
			SELECT DISTINCT expedice_id, COUNT(*) pocet
			FROM objednavka
			WHERE objednavka_datetime > NOW() - INTERVAL 1 DAY
			GROUP BY expedice_id
		) aa
		JOIN expedice ex USING (expedice_id)
		WHERE pocet > 100
	"""
	result = query_db(query_exps, DatabaseAliasLive)
	expeditions = {x[0]: x[1] for x in result}
	return expeditions


def get_naskladnovaci_rozdeleni_3(exp_name, exp_id, vykryti):
	query_zjk = """
		SELECT COUNT(DISTINCT(objednavka_id)) AS pocet_zobecnenych
		FROM sklad_objednani_ so
		JOIN `knihy-cme`.polozka p USING (polozka_id)
		WHERE sklad_objednani_pozadavek >= CURDATE() - INTERVAL 14 DAY"""
	query_typ_1 = """
		SELECT SUM(sklad_objednani_mnozstvi) AS pocet_typ_1
		FROM sklad_objednani_ so
		WHERE sklad_objednani_typ_id IN (1, 10)
		AND sklad_objednani_pozadavek >= CURDATE() - INTERVAL 14 DAY"""
	query_jk = """
		SELECT COUNT(DISTINCT p.objednavka_id) AS pocet_jednokusu
		FROM sklad_objednani_ so
		JOIN `knihy-cme`.polozka p ON so.polozka_id = p.polozka_id
		JOIN (
			SELECT objednavka_id, SUM(polozka_mnozstvi) AS celkem_kusu
			FROM `knihy-cme`.polozka p
			JOIN `knihy-cme`.zbozi z ON p.zbozi_id = z.zbozi_id
			WHERE polozka_datetime > CURDATE() - INTERVAL 14 DAY
			AND z.zbozi_cena > 2
			GROUP BY objednavka_id
		) objednavky
		ON objednavky.objednavka_id = p.objednavka_id
		WHERE so.sklad_objednani_typ_id IN (1, 10)
		AND so.sklad_objednani_pozadavek >= CURDATE() - INTERVAL 14 DAY
		AND objednavky.celkem_kusu = 1"""
	query_write = """
		INSERT INTO expedice_prehled_data
			(expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, %s, %s, DATE(NOW()))
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
		"""

	query_fetch = f"""
		SELECT hodnota
		FROM
		(
			SELECT expedice_prehled_data_nazev_id,
			hodnota,
			RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
			FROM expedice_prehled_data
			WHERE expedice_id = %s
			AND expedice_prehled_data_nazev_id IN (3, 4, 5, 6)
		) aa
		WHERE poradi = 1
		ORDER BY expedice_prehled_data_nazev_id ASC
		"""

	if args_.frequency == tyden_arg:
		result_zobecnene = float(query_db(query_zjk, get_db_alias_exp(exp_name))[0][0])
		result_typ_1 = float(query_db(query_typ_1, get_db_alias_exp(exp_name))[0][0])
		result_jednokusu = float(query_db(query_jk, get_db_alias_exp(exp_name))[0][0])
		jednokusovky = result_jednokusu
		zobecnene = result_zobecnene - result_jednokusu
		result_typ_1 = result_typ_1 - result_zobecnene
		polozkove_vykryti = vykryti[exp_id][1]
		# share of jednokusovky, zobecnene, typ_1; the shares have to add up to 1-vykryti
		if jednokusovky + zobecnene + result_typ_1 == 0:
			print("No data for expedition", exp_name, exp_id)
			return [0, 0, 0]
		share_jednokusovky = jednokusovky / (jednokusovky + zobecnene + result_typ_1)
		share_zobecnene = zobecnene / (jednokusovky + zobecnene + result_typ_1)
		share_typ_1 = result_typ_1 / (jednokusovky + zobecnene + result_typ_1)
		share_jednokusovky, share_zobecnene, share_typ_1 = [x * (1 - polozkove_vykryti) for x in
															[share_jednokusovky, share_zobecnene, share_typ_1]]
		result = [share_typ_1, share_jednokusovky, share_zobecnene, polozkove_vykryti]
		Con, Cur = get_db_cursor(DatabaseAliasLive)
		query_execute(Cur, query_write, (3, exp_id, result[0]))
		query_execute(Cur, query_write, (4, exp_id, result[1]))
		query_execute(Cur, query_write, (5, exp_id, result[2]))
		query_execute(Cur, query_write, (6, exp_id, result[3]))
		Con.commit()
		Con.close()
	else:
		result = query_db(query_fetch, DatabaseAliasLive, (exp_id,))
		if result:
			result = [float(x[0]) for x in result]
		else:
			result = [0, 0, 0, 0]
	return result




def get_naskladnovaci_rozdeleni_2(exp_name, exp_id):
	query = """
			SELECT
				so.sklad_objednani_id,
				so.polozka_id,
				so.sklad_objednani_mnozstvi,
				so.sklad_objednani_typ_id,
				so.sklad_objednani_doruceno,
				so.sklad_objednani_pozadavek,
				p.polozka_mnozstvi,
				p.objednavka_id,
				p.zbozi_id,
				o.expedice_id,
				z.zbozi_cena
			FROM
				sklad_objednani_ so
			LEFT JOIN `knihy-cme`.polozka p ON so.polozka_id = p.polozka_id
			LEFT JOIN `knihy-cme`.objednavka o ON p.objednavka_id = o.objednavka_id
			LEFT JOIN `knihy-cme`.zbozi z ON p.zbozi_id = z.zbozi_id
			WHERE
				so.sklad_objednani_pozadavek >= CURDATE() - INTERVAL 14 DAY
				AND so.sklad_objednani_doruceno IS NOT NULL
			"""
	query_write = """
			INSERT INTO expedice_prehled_data
				(expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
				VALUES (%s, %s, %s, DATE(NOW()))
				ON DUPLICATE KEY UPDATE
				expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
				expedice_id = VALUES(expedice_id),
				hodnota = VALUES(hodnota),
				aktualizovano = VALUES(aktualizovano)
		"""

	query_fetch = f"""
			SELECT hodnota
			FROM
			(
				SELECT expedice_prehled_data_nazev_id,
				hodnota,
				RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_id = %s
				AND expedice_prehled_data_nazev_id IN (3, 4, 5, 6)
			) aa
			WHERE poradi = 1
			ORDER BY expedice_prehled_data_nazev_id ASC
		"""
	if args_.frequency == tyden_arg:
		result_ = query_db(query, get_db_alias_exp(exp_name))

		# Columns mapping (explicit)
		columns = [
			'sklad_objednani_id', 'polozka_id', 'sklad_objednani_mnozstvi',
			'sklad_objednani_typ_id', 'sklad_objednani_doruceno',
			'sklad_objednani_pozadavek', 'polozka_mnozstvi', 'objednavka_id',
			'zbozi_id', 'expedice_id', 'zbozi_cena'
		]

		# Transform DB rows to dicts
		data = [dict(zip(columns, row)) for row in result_]

		# Prepare lists for different types
		volny_sklad = []
		realne = []

		for row in data:
			typ_id = row['sklad_objednani_typ_id']
			exp_match = row['expedice_id'] == exp_id
			cena_ok = row['zbozi_cena'] is not None and row['zbozi_cena'] > 2
			polozka_id_null = row['polozka_id'] is None

			if typ_id in [3, 5] and polozka_id_null:
				volny_sklad.append(row)
			elif typ_id not in [3, 5] and exp_match and cena_ok:
				realne.append(row)

		# Count unique polozka_id per objednavka_id
		pocet_polozek = {}
		for row in realne:
			oid = row['objednavka_id']
			pocet_polozek.setdefault(oid, set()).add(row['polozka_id'])

		# Assign types
		for row in realne:
			oid = row['objednavka_id']
			polozky = pocet_polozek[oid]
			if len(polozky) == 1 and row['polozka_mnozstvi'] == row['sklad_objednani_mnozstvi']:
				row['typ'] = 'jedina_polozka'
			else:
				row['typ'] = 'ostatni'

		# Determine 'posledni_chybejici'
		ostatni_rows = [r for r in realne if r['typ'] == 'ostatni']

		# Find max pozadavek per objednavka_id
		max_pozadavek = {}
		for row in ostatni_rows:
			oid = row['objednavka_id']
			pozadavek = row['sklad_objednani_pozadavek']
			if oid not in max_pozadavek or pozadavek > max_pozadavek[oid]['sklad_objednani_pozadavek']:
				max_pozadavek[oid] = row

		# Update type to 'posledni_chybejici'
		for row in max_pozadavek.values():
			row['typ'] = 'posledni_chybejici'

		# Combine all rows into single list for counting
		typ_counts = {
			'ostatni': 0,
			'jedina_polozka': 0,
			'posledni_chybejici': 0,
			'volny_sklad': 0
		}

		# Sum amounts
		for row in volny_sklad:
			typ_counts['volny_sklad'] += row['sklad_objednani_mnozstvi']

		for row in realne:
			typ = row['typ']
			typ_counts[typ] += row['sklad_objednani_mnozstvi']

		# Calculate proportions
		total = sum(typ_counts.values())
		if total > 0:
			pomery = {k: round(v / total, 4) for k, v in typ_counts.items()}
		else:
			pomery = {k: 0 for k in typ_counts}

		result_ = [
			pomery.get('ostatni', 0),
			pomery.get('jedina_polozka', 0),
			pomery.get('posledni_chybejici', 0),
			pomery.get('volny_sklad', 0),
		]

		Con, Cur = get_db_cursor(DatabaseAliasLive)
		query_execute(Cur, query_write, (3, exp_id, result_[0]))
		query_execute(Cur, query_write, (4, exp_id, result_[1]))
		query_execute(Cur, query_write, (5, exp_id, result_[2]))
		query_execute(Cur, query_write, (6, exp_id, result_[3]))
		Con.commit()
		Con.close()
	else:
		result_ = query_db(query_fetch, DatabaseAliasLive, (exp_id,))
		if result_:
			result_ = [float(x[0]) for x in result_]
		else:
			result_ = [0, 0, 0, 0]
	return result_


def get_naskladnovaci_rozdeleni(exp_name, exp_id):
	# WRITE EPD3 , WRITE EPD4 , WRITE EPD5 , WRITE EPD6
	# READ EPD3 , READ EPD4 , READ EPD5 , READ EPD6
	query_get = """
	SELECT
  SUM(pocet) / SUM(SUM(pocet)) OVER () AS podil
FROM (

  -- Typy 1, 10 (A, B, C)
  SELECT
	CASE
	  WHEN velikost_objednavky = 1 AND sklad_objednani_mnozstvi = 1 THEN 4 -- A
	  WHEN cum_pocet_objednanych = pocet_objednanych THEN 5 -- B
	  ELSE 3 -- C
	END AS typ_polozky,
	CASE
	  WHEN cum_pocet_objednanych = pocet_objednanych THEN LEAST(sklad_objednani_mnozstvi, 1)
	  ELSE sklad_objednani_mnozstvi
	END AS pocet
  FROM (
	SELECT
	  sklad_objednani_id,
	  objednavka_id,
	  sklad_objednani_mnozstvi,
	  sklad_objednani_doruceno,
	  velikost_objednavky,
	  SUM(sklad_objednani_mnozstvi) OVER (
		PARTITION BY objednavka_id
		ORDER BY sklad_objednani_doruceno, sklad_objednani_id
	  ) AS cum_pocet_objednanych,
	  SUM(sklad_objednani_mnozstvi) OVER (PARTITION BY objednavka_id) AS pocet_objednanych
	FROM sklad_objednani_
	JOIN `knihy-cme`.polozka p USING (polozka_id)
	JOIN `knihy-cme`.objednavka USING (objednavka_id)
	JOIN (
	  SELECT objednavka_id, SUM(polozka_mnozstvi) AS velikost_objednavky
	  FROM `knihy-cme`.polozka
	  JOIN `knihy-cme`.objednavka USING (objednavka_id)
	  JOIN `knihy-cme`.zbozi USING (zbozi_id)
	  WHERE zbozi_cena > 2
		AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
		AND zbozi_nazev NOT LIKE '%Zběrateľská záložka%'
		AND objednavka_datetime BETWEEN CURRENT_DATE() - INTERVAL 28 DAY AND CURRENT_DATE()
	  GROUP BY objednavka_id
	) cc USING (objednavka_id)
	WHERE sklad_objednani_typ_id IN (1, 10)
	  AND sklad_objednani_doruceno IS NOT NULL
	  AND objednavka_datetime BETWEEN CURRENT_DATE() - INTERVAL 28 DAY AND CURRENT_DATE()
  ) detail_ABC

  UNION ALL

  -- Typy 3 a 5 (D)
  SELECT
	6 AS typ_polozky,
	sklad_objednani_mnozstvi AS pocet
  FROM sklad_objednani_
  WHERE sklad_objednani_typ_id IN (3, 5)
	AND sklad_objednani_pozadavek BETWEEN CURRENT_DATE() - INTERVAL 28 DAY AND CURRENT_DATE()

) komplet
GROUP BY typ_polozky
ORDER BY typ_polozky;

	"""

	query_write = """
		INSERT INTO expedice_prehled_data
			(expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, %s, %s, DATE(NOW()))
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
	"""

	query_fetch = f"""
		SELECT hodnota
		FROM
		(
			SELECT expedice_prehled_data_nazev_id,
			hodnota,
			RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
			FROM expedice_prehled_data
			WHERE expedice_id = %s
			AND expedice_prehled_data_nazev_id IN (3, 4, 5, 6)
		) aa
		WHERE poradi = 1
		ORDER BY expedice_prehled_data_nazev_id ASC
	"""

	if args_.frequency == tyden_arg:
		databaseAlias = get_db_alias_exp(exp_name)
		result = [x[0] for x in query_db(query_get, databaseAlias)]
		Con, Cur = get_db_cursor(DatabaseAliasLive)
		query_execute(Cur, query_write, (3, exp_id, result[0]))
		query_execute(Cur, query_write, (4, exp_id, result[1]))
		query_execute(Cur, query_write, (5, exp_id, result[2]))
		query_execute(Cur, query_write, (6, exp_id, result[3]))
		Con.commit()
		Con.close()
	else:
		result = query_db(query_fetch, DatabaseAliasLive, (exp_id,))
		if result:
			result = [float(x[0]) for x in result]
		else:
			result = [0, 0, 0, 0]
	return result


def get_fronta_naskladneni(expedice_id, time_=None):
	"""fix"""
	fronta_naskladneni_palety = {1: 8, 3: 4, 6: 17, 8: 8, 9: 2}
	fronta_naskladneni_pocet_kusu_na_palete = {1: 450, 3: 400, 6: 400, 8: 350, 9: 250}
	fronta_naskladneni = {exp_id: fronta_naskladneni_palety[exp_id] * fronta_naskladneni_pocet_kusu_na_palete[exp_id] for exp_id in fronta_naskladneni_palety}
	return fronta_naskladneni[expedice_id]
	"""fix"""
	url = "https://stock-queue-intake.math.90.cz/fronta_naskladneni/v1/fronta"
	with open('data/config.json') as f:
		config_json = json.load(f)
		config_json = [config_json['ldap_user'], config_json['ldap_password']]
	response = requests.post(url, auth=(config_json[0], config_json[1]), json={"expedice_id": expedice_id})
	str_data = ""
	try:
		str_data = str(response.json())
	except requests.exceptions.JSONDecodeError:
		with open("json_fronty.txt", "r") as f:
			str_data = f.read()

	data = eval(str_data)
	if not data:
		return 111
	return data.get("fronta", 111)


def get_fronta_rozhozeni(expedice_nazev):
	query = """
		SELECT COUNT(naskladnovani_vozik_polozky_id)
		FROM naskladnovani_vozik
		JOIN naskladnovani_vozik_polozky USING (naskladnovani_vozik_id)
		WHERE naskladnovani_vozik_vytvoren > CURRENT_DATE() - INTERVAL 14 DAY
		AND naskladnovani_vozik_nacten IS NULL
		AND naskladnovani_vozik_dokoncen IS NULL
	"""
	DatabaseAlias = get_db_alias_exp(expedice_nazev)
	return query_db(query, DatabaseAlias)[0][0]


def get_fronta_vyhledani_objednavek(expedice_nazev, time_=None):
	if not time_:
		query = """
			SELECT pocet_k_zabaleni - pocet_dohledano
			FROM statistika_sumarizace_sklad_baliku
			ORDER BY den DESC
			LIMIT 1
		"""
	else:
		query = """
			SELECT pocet_k_zabaleni - pocet_dohledano
			FROM statistika_sumarizace_sklad_baliku
			WHERE den <= %s
			ORDER BY den DESC
			LIMIT 1
		"""
	DatabaseAlias = get_db_alias_exp(expedice_nazev)
	return query_db(query, DatabaseAlias)[0][0]


def get_fronta_baleni_objednavek(expedice_nazev):
	query = """
		SELECT pocet_dohledano
		FROM statistika_sumarizace_sklad_baliku
		ORDER BY den DESC
		LIMIT 1
	"""
	DatabaseAlias = get_db_alias_exp(expedice_nazev)
	fronta_b = query_db(query, DatabaseAlias)[0][0]
	return fronta_b

def get_kusy_kompletni_objednavky(expedice_id):
	query = f"""
			SELECT COUNT(*)
			FROM polozka
			JOIN objednavka USING (objednavka_id)
			JOIN zbozi USING (zbozi_id)
			WHERE objednavka_datetime > CURRENT_DATE() - INTERVAL 28 DAY
			AND obj_stav_id = 12
			AND expedice_id = {expedice_id}
			AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
			AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
			AND zbozi_cena > 2
		"""
	kusy = query_db(query, DatabaseAliasLive)[0][0]
	return kusy

def get_polozky_kompletni_objednavky(expedice_id):
	query = f"""
		SELECT SUM(polozka_mnozstvi)
		FROM polozka
		JOIN objednavka USING (objednavka_id)
		JOIN zbozi USING (zbozi_id)
		WHERE objednavka_datetime > CURRENT_DATE() - INTERVAL 28 DAY
		AND obj_stav_id = 12
		AND expedice_id = {expedice_id}
		AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
		AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
		AND zbozi_cena > 2
	"""
	polozky = query_db(query, DatabaseAliasLive)[0][0]
	return polozky


def get_koeficienty_naskladneni_z_tabulky():
	# READ EPD1 , READ EPD2
	query = f"""
		SELECT expedice_prehled_data_nazev_id, expedice_id, hodnota
		FROM (
			SELECT *, RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
			FROM expedice_prehled_data
		) aa
		WHERE poradi = 1
		AND expedice_prehled_data_nazev_id IN (1, 2)
	"""
	result = query_db(query, DatabaseAliasLive)
	result = {r[1]: r[2] for r in result if r[0] == 1}, {r[1]: r[2] for r in result if r[0] == 2}
	return {k: (result[0][k], result[1][k]) for k in result[0]}


def write_fronta(fronta, nazev_id):
	db, cursor = get_db_cursor(DatabaseAliasLive)
	for exp, hodnota in fronta.items():
		insert_update_query = """
			INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, %s, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
		"""
		query_execute(cursor, insert_update_query, (nazev_id, exp, hodnota))
		db.commit()
	db.close()
	return


def get_fronty_kusu(exp2name_):
	# WRITE EP1 , WRITE EP2 , WRITE EP3 , WRITE EP4
	if args_.frequency == hodina_arg:
		fronta_naskladneni = {
			exp_id: get_fronta_naskladneni(exp_id) for exp_id in exp2name_}
		if args_.output:
			print("Odhad fronty naskladněni", fronta_naskladneni)

		fronta_rozhozeni = {exp_id: get_fronta_rozhozeni(exp_name) for exp_id, exp_name in exp2name_.items()}
		fronta_vyhledani_objednavek = {exp_id: get_fronta_vyhledani_objednavek(exp_name) for exp_id, exp_name in
									   exp2name_.items()}
		fronta_baleni_objednavek = {exp_id: get_fronta_baleni_objednavek(exp_name) for exp_id, exp_name in
									exp2name_.items()}
		kusy_kompletni = {exp_id: get_kusy_kompletni_objednavky(exp_id) for exp_id in exp2name_}
		polozky_kompletni = {exp_id: get_polozky_kompletni_objednavky(exp_id) for exp_id in exp2name_}
		fronta_vyhledani = {exp_id: kusy_kompletni[exp_id] * (fronta_vyhledani_objednavek[exp_id] / (
				fronta_vyhledani_objednavek[exp_id] + fronta_baleni_objednavek[exp_id])) for exp_id in exp2name_}
		fronta_baleni = {exp_id: kusy_kompletni[exp_id] * (fronta_baleni_objednavek[exp_id] / (
				fronta_vyhledani_objednavek[exp_id] + fronta_baleni_objednavek[exp_id])) for exp_id in exp2name_}
		fronta_vyhledani_polozek = {exp_id: polozky_kompletni[exp_id] * (fronta_vyhledani_objednavek[exp_id] / (
				fronta_vyhledani_objednavek[exp_id] + fronta_baleni_objednavek[exp_id])) for exp_id in exp2name_}
		fronta_baleni_polozek = {exp_id: polozky_kompletni[exp_id] * (fronta_baleni_objednavek[exp_id] / (
				fronta_vyhledani_objednavek[exp_id] + fronta_baleni_objednavek[exp_id])) for exp_id in exp2name_}
		write_fronta(fronta_naskladneni, 1)
		write_fronta(fronta_rozhozeni, 2)
		write_fronta(fronta_vyhledani, 3)
		write_fronta(fronta_baleni, 4)
		return fronta_naskladneni, fronta_rozhozeni, fronta_vyhledani, fronta_vyhledani_objednavek, fronta_vyhledani_polozek, fronta_baleni, fronta_baleni_objednavek, fronta_baleni_polozek
	return None


def get_nepracovni_dny_tuple():
	return "2025-12-22", "2025-12-23", "2025-12-29", "2025-12-30", "2025-12-31"


def get_rychlosti_pozic(exp_id, exp_nazev, dny):
	# WRITE EPD19 , WRITE EPD20 , WRITE EPD21 , WRITE EPD22
	# WRITE EPD166, WRITE EPD167, WRITE EPD168, WRITE EPD169
	# READ EPD19 , READ EPD20 , READ EPD21 , READ EPD22
	# READ EPD166, READ EPD167, READ EPD168, READ EPD169

	rychlosti_pozic_ = [0, 0, 0, 0]
	nepracovni_dny = get_nepracovni_dny_tuple()
	if args_.frequency == den_arg:
		DatabaseAlias = get_db_alias_exp(exp_nazev)
		query = f"""
					SELECT IFNULL(sum(naskladnovani_s) / sum(naskladnovani_ks), 0) cas_naskladneni,
					IFNULL(sum(rozhazovani_s) / sum(rozhazovani_ks), 0) cas_rozhozeni,
					IFNULL(sum(vyhledavani_s) / sum(vyhledavani_ks), 0) cas_vyhledani,
					IFNULL(sum(baleni_s) / sum(baleni_ks), 0) cas_baleni
					FROM statistika_sumarizace_vykon
					LEFT JOIN (SELECT uzivatel_id, den, SUM(odpracovany_cas_orezany) naskladnovani_s
								FROM merena_prace_den
								JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id)
								WHERE merena_prace_typ_id = 1
								GROUP BY uzivatel_id, den) m1 USING (uzivatel_id, den)
					LEFT JOIN (SELECT uzivatel_id, den, SUM(odpracovany_cas_orezany) rozhazovani_s
								FROM merena_prace_den
								JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id)
								WHERE merena_prace_typ_id = 2
								AND uzivatel_typ_id != 2
								GROUP BY uzivatel_id, den) m2 USING (uzivatel_id, den)
					LEFT JOIN (SELECT uzivatel_id, den, SUM(odpracovany_cas_orezany) vyhledavani_s
								FROM merena_prace_den
								JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id)
								WHERE merena_prace_typ_id = 3
								AND uzivatel_typ_id != 2
								GROUP BY uzivatel_id, den) m3 USING (uzivatel_id, den)
					LEFT JOIN (SELECT uzivatel_id, den, SUM(odpracovany_cas_orezany) baleni_s
								FROM merena_prace_den
								JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id)
								WHERE merena_prace_typ_id = 4
								AND uzivatel_typ_id != 2
								GROUP BY uzivatel_id, den) m4 USING (uzivatel_id, den)
					LEFT JOIN (SELECT uzivatel_id, den, SUM(naskladnoval_knih) naskladnovani_ks FROM statistika_sumarizace_vykon JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id) GROUP BY uzivatel_id, den) sk1 USING (uzivatel_id, den)
					LEFT JOIN (SELECT uzivatel_id, den, SUM(naskladnoval_vozik_knih) rozhazovani_ks FROM statistika_sumarizace_vykon JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id) WHERE uzivatel_typ_id != 2 GROUP BY uzivatel_id, den) sk2 USING (uzivatel_id, den)
					LEFT JOIN (SELECT uzivatel_id, den, SUM(vyhledavaci_vozik_knih) vyhledavani_ks FROM statistika_sumarizace_vykon JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id) WHERE uzivatel_typ_id != 2 GROUP BY uzivatel_id, den) sk3 USING (uzivatel_id, den)
					LEFT JOIN (SELECT uzivatel_id, den, SUM(baleni_kusu) baleni_ks FROM statistika_sumarizace_vykon JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id) WHERE uzivatel_typ_id != 2 GROUP BY uzivatel_id, den) sk4 USING (uzivatel_id, den)
					WHERE den BETWEEN CURRENT_DATE() - INTERVAL %s DAY AND CURRENT_DATE()
					AND den NOT IN {nepracovni_dny}
				"""
		result = query_db(query, DatabaseAlias, (dny,))
		rychlosti_pozic_ = [float(x) for x in result[0]]
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
				VALUES (%s, %s, %s, NOW())
				ON DUPLICATE KEY UPDATE
				expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
				expedice_id = VALUES(expedice_id),
				hodnota = VALUES(hodnota),
				aktualizovano = VALUES(aktualizovano)
		"""
		if dny == 14:
			query_execute(cursor, insert_update_query, (19, exp_id, rychlosti_pozic_[0]))
			query_execute(cursor, insert_update_query, (20, exp_id, rychlosti_pozic_[1]))
			query_execute(cursor, insert_update_query, (21, exp_id, rychlosti_pozic_[2]))
			query_execute(cursor, insert_update_query, (22, exp_id, rychlosti_pozic_[3]))
		if dny == 1:
			query_execute(cursor, insert_update_query, (166, exp_id, rychlosti_pozic_[0]))
			query_execute(cursor, insert_update_query, (167, exp_id, rychlosti_pozic_[1]))
			query_execute(cursor, insert_update_query, (168, exp_id, rychlosti_pozic_[2]))
			query_execute(cursor, insert_update_query, (169, exp_id, rychlosti_pozic_[3]))
		db.commit()
		db.close()

	if args_.frequency == hodina_arg:
		DatabaseAlias = DatabaseAliasLive
		query = """
			SELECT hodnota
			FROM
			(
				SELECT expedice_prehled_data_nazev_id,
					hodnota,
					RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_id = %s
				AND expedice_prehled_data_nazev_id IN (%s, %s, %s, %s)
			) aa
			WHERE poradi = 1
			ORDER BY expedice_prehled_data_nazev_id ASC
		"""
		result = []
		if dny == 14:
			result = query_db(query, DatabaseAlias, (exp_id, 19, 20, 21, 22))
		if dny == 1:
			result = query_db(query, DatabaseAlias, (exp_id, 166, 167, 168, 169))
		rychlosti_pozic_ = [float(x[0]) for x in result]
	return rychlosti_pozic_


def get_rychlost_baleni_regrese(exp_id, exp_name):
	if args_.frequency == tyden_arg:
		DatabaseAlias = get_db_alias_exp(exp_name)
		query = """
				SELECT MAX(uzivatel_baleni_pocetks), COUNT(*) pocet_polozek, MAX(uzivatel_baleni_realny_cas)
				FROM uzivatel_baleni
				JOIN `knihy-cme`.polozka USING (objednavka_id)
				WHERE uzivatel_baleni_cas > NOW() - INTERVAL 28 DAY
				GROUP BY objednavka_id
				"""
		result = query_db(query, DatabaseAlias)
		# Zpracování dat bez pandas
		X, y = [], []

		for pocetks, pocet_polozek, realny_cas in result:
			# Převod dat na správné typy
			pocetks = int(pocetks)
			pocet_polozek = int(pocet_polozek)
			realny_cas = float(realny_cas)

			X.append([pocetks, pocet_polozek])
			y.append(realny_cas)

		# Trénování modelu
		model = LinearRegression()
		model.fit(X, y)
		slope_1 = float(model.coef_[0])
		slope_2 = float(model.coef_[1])
		intercept = float(model.intercept_)

		query_insert = """
		INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (%s, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""

		db, cursor = get_db_cursor(DatabaseAliasLive)
		query_execute(cursor, query_insert, (184, exp_id, intercept))
		query_execute(cursor, query_insert, (185, exp_id, slope_1))
		query_execute(cursor, query_insert, (186, exp_id, slope_2))
		db.commit()
		db.close()
		return intercept, slope_1, slope_2
	if args_.frequency == den_arg:
		return [0, 0, 0]
	if args_.frequency == hodina_arg:
		query = """
				SELECT expedice_prehled_data_nazev_id, hodnota
				FROM (
					SELECT expedice_prehled_data_nazev_id,
					hodnota,
					RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id ORDER BY aktualizovano DESC) AS poradi
					FROM expedice_prehled_data
					WHERE expedice_prehled_data_nazev_id IN (184, 185, 186)
					AND expedice_id = %s
				) aa
				WHERE poradi = 1
				"""
		result = query_db(query, DatabaseAliasLive, (exp_id,))
		result = {x[0]: x[1] for x in result}
		return float(result[184]), float(result[185]), float(result[186])
	return None


def get_velikost_kompletni(exp_id, exp_name):
	# WRITE EP7
	# WRITE EPD 180
	# READ EP7
	if args_.frequency == den_arg:
		DatabaseAlias = get_db_alias_exp(exp_name)
		query = """
				SELECT a / b
				FROM
				(
					SELECT SUM(polozka_mnozstvi) a, COUNT(DISTINCT(objednavka_id)) b
					FROM `knihy-cme`.objednavka o
					JOIN `knihy-cme`.polozka p USING (objednavka_id)
					JOIN `knihy-cme`.zbozi z USING (zbozi_id)
					LEFT JOIN sklad_objednani_ s
						ON s.polozka_id = p.polozka_id
					WHERE objednavka_datetime > NOW() - INTERVAL 7 DAY
					AND s.polozka_id IS NULL
					AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
					AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
					AND z.zbozi_cena > 0.2
					AND o.expedice_id = %s
				) aa"""
		result = query_db(query, DatabaseAlias, (exp_id,))
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query_1 = """
		INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (7, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		insert_update_query_2 = """
		INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (180, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		query_execute(cursor, insert_update_query_1, (exp_id, result[0][0]))
		query_execute(cursor, insert_update_query_2, (exp_id, result[0][0]))
		db.commit()
		db.close()
		return float(result[0][0])

	if args_.frequency == hodina_arg:
		query = """
				SELECT hodnota
				FROM expedice_prehled
				WHERE expedice_prehled_nazev_id = 7
				AND expedice_id = %s
				"""
		result = query_db(query, DatabaseAliasLive, (exp_id,))
		if not result[0][0]:
			return 1
		return float(result[0][0])
	return None


def get_polozek_kompletni(exp_id, exp_name):
	# WRITE EPD181
	# READ EPD181
	result = [[1]]
	if args_.frequency == den_arg:
		DatabaseAlias = get_db_alias_exp(exp_name)
		query = """
					SELECT a / b
					FROM
					(
						SELECT COUNT(*) a, COUNT(DISTINCT(objednavka_id)) b
						FROM `knihy-cme`.objednavka o
						JOIN `knihy-cme`.polozka p USING (objednavka_id)
						JOIN `knihy-cme`.zbozi z USING (zbozi_id)
						LEFT JOIN sklad_objednani_ s
							ON s.polozka_id = p.polozka_id
						WHERE objednavka_datetime > NOW() - INTERVAL 7 DAY
						AND s.polozka_id IS NULL
						AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
						AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
						AND z.zbozi_cena > 0.2
						AND o.expedice_id = %s
					) aa"""
		DatabaseAlias = get_db_alias_exp(exp_name)
		result = query_db(query, DatabaseAlias, (exp_id,))
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query = """
						INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
						VALUES (181, %s, %s, NOW())
						ON DUPLICATE KEY UPDATE
						expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
						expedice_id = VALUES(expedice_id),
						hodnota = VALUES(hodnota),
						aktualizovano = VALUES(aktualizovano)
						"""
		query_execute(cursor, insert_update_query, (exp_id, result[0][0]))
		db.commit()
		db.close()
	if args_.frequency == hodina_arg:
		query = """
				SELECT hodnota
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = 181
				AND expedice_id = %s
				"""
		result = query_db(query, DatabaseAliasLive, (exp_id,))
		if not result[0][0]:
			return 1
	return result[0][0]


def get_velikost_nekomplet(exp_id, exp_name):
	# WRITE EP8
	# WRITE EPD182
	# READ EP8
	if args_.frequency == den_arg:
		DatabaseAlias = get_db_alias_exp(exp_name)
		query_kusy = """ SELECT a / b
					FROM
					(
						SELECT SUM(p.polozka_mnozstvi) a, COUNT(DISTINCT(objednavka_id)) b
						FROM
						(
							SELECT DISTINCT(objednavka_id)
							FROM `knihy-cme`.polozka p
							JOIN `knihy-cme`.objednavka o USING (objednavka_id)
							JOIN sklad_objednani_ USING (polozka_id)
							WHERE objednavka_datetime > NOW() - INTERVAL 7 DAY
							AND o.expedice_id = %s
						) aa
						JOIN `knihy-cme`.polozka p USING (objednavka_id)
						JOIN `knihy-cme`.zbozi z USING (zbozi_id)
						WHERE zbozi_nazev NOT LIKE '%Sběratelská záložka%'
						AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
						AND z.zbozi_cena > 0.2
					) ab
				"""

		result = query_db(query_kusy, DatabaseAlias, (exp_id,))
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query_1 = """
				INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
				VALUES (8, %s, %s, NOW())
				ON DUPLICATE KEY UPDATE
				expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
				expedice_id = VALUES(expedice_id),
				hodnota = VALUES(hodnota),
				aktualizovano = VALUES(aktualizovano)
				"""
		query_execute(cursor, insert_update_query_1, (exp_id, result[0][0]))
		insert_update_query_2 = """
				INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
				VALUES (182, %s, %s, NOW())
				ON DUPLICATE KEY UPDATE
				expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
				expedice_id = VALUES(expedice_id),
				hodnota = VALUES(hodnota),
				aktualizovano = VALUES(aktualizovano)
				"""
		query_execute(cursor, insert_update_query_2, (exp_id, result[0][0]))
		db.commit()
		db.close()
		if not result[0][0]:
			return 1
		return float(result[0][0])
	if args_.frequency == hodina_arg:
		query = """
				SELECT hodnota
				FROM expedice_prehled
				WHERE expedice_prehled_nazev_id = 8
				AND expedice_id = %s
				"""
		result = query_db(query, DatabaseAliasLive, (exp_id,))
		if not result[0][0]:
			return 1
		return float(result[0][0])
	return None

def get_polozek_nekomplet(exp_id, exp_name):
	# WRITE EPD183
	# READ EPD183
	result = [[1]]
	if args_.frequency == den_arg:
		query_polozky = """ SELECT a / b
					FROM
					(
						SELECT COUNT(*) a, COUNT(DISTINCT(objednavka_id)) b
						FROM
						(
							SELECT DISTINCT(objednavka_id)
							FROM `knihy-cme`.polozka p
							JOIN `knihy-cme`.objednavka o USING (objednavka_id)
							JOIN sklad_objednani_ USING (polozka_id)
							WHERE objednavka_datetime > NOW() - INTERVAL 7 DAY
							AND o.expedice_id = %s
						) aa
						JOIN `knihy-cme`.polozka p USING (objednavka_id)
						JOIN `knihy-cme`.zbozi z USING (zbozi_id)
						WHERE zbozi_nazev NOT LIKE '%Sběratelská záložka%'
						AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
						AND z.zbozi_cena > 0.2
					) ab
				"""
		DatabaseAlias = get_db_alias_exp(exp_name)
		result = query_db(query_polozky, DatabaseAlias, (exp_id,))
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query = """
						INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
						VALUES (183, %s, %s, NOW())
						ON DUPLICATE KEY UPDATE
						expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
						expedice_id = VALUES(expedice_id),
						hodnota = VALUES(hodnota),
						aktualizovano = VALUES(aktualizovano)
						"""
		query_execute(cursor, insert_update_query, (exp_id, result[0][0]))
		db.commit()
		db.close()
	if args_.frequency == hodina_arg:
		query = """
				SELECT hodnota
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = 183
				AND expedice_id = %s
				"""
		result = query_db(query, DatabaseAliasLive, (exp_id,))
		if not result[0][0]:
			return 1
	return result[0][0]


def vypocti_frontu_naskladneni_cas(exp2name_, fronta_naskladneni_kusy, rychlost_pozic, rozdeleni_naskladneni,
								   velikost_nekompletni_objednavky_):
	if args_.frequency in [hodina_arg, den_arg]:
		mnozstvi_prace = {exp_id: [0, 0, 0, 0.] for exp_id in exp2name_}
		for e in exp2name_:
			# Práce z fronty naskladnění

			# Naskladnění - všechny
			mnozstvi_prace[e][0] = fronta_naskladneni_kusy[e] * rychlost_pozic[e][0]

			# Rozhození - všechny kromě jednokusovek
			mnozstvi_prace[e][1] = fronta_naskladneni_kusy[e] * rychlost_pozic[e][1]
			mnozstvi_prace[e][1] -= fronta_naskladneni_kusy[e] * \
									rozdeleni_naskladneni[e][1] * rychlost_pozic[e][1]

			# Vyhledání - zobecněné vynásobené počtem kusů v objednávce
			mnozstvi_prace[e][2] = fronta_naskladneni_kusy[e] * \
								   rozdeleni_naskladneni[e][2] * rychlost_pozic[e][2] * \
								   velikost_nekompletni_objednavky_[e]

			# Balení - vyhledání plus jednokusovky
			mnozstvi_prace[e][3] = fronta_naskladneni_kusy[e] * \
								   rozdeleni_naskladneni[e][2] * rychlost_pozic[e][3] * \
								   velikost_nekompletni_objednavky_[e]
			mnozstvi_prace[e][3] += fronta_naskladneni_kusy[e] * \
									rozdeleni_naskladneni[e][1] * rychlost_pozic[e][3]

			mnozstvi_prace[e] = [v / 3600 for v in mnozstvi_prace[e]]
		return mnozstvi_prace
	return None


def vypocti_frontu_naskladneni_cas_2(exp2name_, fronta_naskladneni_kusy, rychlost_pozic, rozdeleni_naskladneni, rozdeleni_nko):
	# Rozdělení NKO: Volný sklad, k doobjednání
	fronta_naskladneni_kusy_zaklad = {exp_id: fronta_naskladneni_kusy[exp_id] * rozdeleni_naskladneni[exp_id][0] for exp_id in exp2name_}
	fronta_naskladneni_kusy_jednokusovky = {exp_id: fronta_naskladneni_kusy[exp_id] * rozdeleni_naskladneni[exp_id][1] for exp_id in exp2name_}
	fronta_naskladneni_kusy_zobecnene = {exp_id: fronta_naskladneni_kusy[exp_id] * rozdeleni_naskladneni[exp_id][2] for exp_id in exp2name_}
	fronta_naskladneni_kusy_volny = {exp_id: fronta_naskladneni_kusy[exp_id] * rozdeleni_naskladneni[exp_id][3] for exp_id in exp2name_}

	if args_.frequency in [hodina_arg, den_arg]:
		mnozstvi_prace = {exp_id: [0, 0, 0, 0.] for exp_id in exp2name_}
		for e in exp2name_:
			# Práce z fronty naskladnění

			# Naskladnění - všechny
			mnozstvi_prace[e][0] = (fronta_naskladneni_kusy_zaklad[e] + fronta_naskladneni_kusy_jednokusovky[e] + fronta_naskladneni_kusy_zobecnene[e] + fronta_naskladneni_kusy_volny[e]) * rychlost_pozic[e][0]

			# Rozhození - všechny kromě jednokusovek
			mnozstvi_prace[e][1] = (fronta_naskladneni_kusy_zaklad[e] + fronta_naskladneni_kusy_zobecnene[e] + fronta_naskladneni_kusy_volny[e])* rychlost_pozic[e][1]

			# Vyhledání - zobecněné vynásobené počtem kusů v objednávce
			mnozstvi_prace[e][2] = (fronta_naskladneni_kusy_zaklad[e] + fronta_naskladneni_kusy_zobecnene[e] * (1 + rozdeleni_nko[e][0])) * rychlost_pozic[e][2]

		return mnozstvi_prace
	return None


def vypocti_frontu_rozhozeni_cas(exp2name_, fronta_rozhozeni_, rychlost_pozic, rozdeleni_naskladneni,
								 velikosti_nekomplet):
	rozdeleni_rozhozeni = {
		exp_id: [x[0] / max(sum([x[0], x[2], x[3]]), 1), x[2] / max(sum([x[0], x[2], x[3]]), 1),
				 x[3] / max(sum([x[0], x[2], x[3]]), 1)] for
		exp_id, x in rozdeleni_naskladneni.items()}
	mnozstvi_prace = {exp_id: [0, 0, 0, 0.] for exp_id in exp2name_}
	# Množství práce z fronty rozhození

	for e in exp2name_:
		# Práce na naskladnění(žádná)
		mnozstvi_prace[e][0] = 0

		# Práce z fronty rozhození
		mnozstvi_prace[e][1] = fronta_rozhozeni_[e] * rychlost_pozic[e][1]

		# Práce na vyhledávání
		mnozstvi_prace[e][2] = fronta_rozhozeni_[e] * rychlost_pozic[e][2] * \
							   rozdeleni_rozhozeni[e][1] * velikosti_nekomplet[e]

		# Práce na balení
		mnozstvi_prace[e][3] = fronta_rozhozeni_[e] * rychlost_pozic[e][3] * \
							   rozdeleni_rozhozeni[e][1] * velikosti_nekomplet[e]

		mnozstvi_prace[e] = [v / 3600 for v in mnozstvi_prace[e]]

	return mnozstvi_prace


def vypocti_frontu_vyhledani_baleni_cas(exp2name_, fronta_vyhledani_kusy, fronta_vyhledani_objednavky_, fronta_vyhledani_polozky_,
										fronta_baleni_kusy, fronta_baleni_objednavky_, fronta_baleni_polozky_, rychlost_pozic, intercept_,
										slope_1, slope_2):
	mnozstvi_prace = {exp_id: [0, 0, 0, 0.] for exp_id in exp2name_}
	for e in exp2name_:
		mnozstvi_prace[e][0] = 0
		mnozstvi_prace[e][1] = 0
		mnozstvi_prace[e][2] = fronta_vyhledani_kusy[e] * rychlost_pozic[e][2]

		mnozstvi_prace[e][3] = (fronta_vyhledani_objednavky_[e] + fronta_baleni_objednavky_[e]) * intercept_[e] + \
							   (fronta_vyhledani_kusy[e] + fronta_baleni_kusy[e]) * slope_1[e] + \
							   (fronta_vyhledani_polozky_[e] + fronta_baleni_polozky_[e]) * slope_2[e]

		mnozstvi_prace[e] = [v / 3600 for v in mnozstvi_prace[e]]

	return mnozstvi_prace


# noinspection PyTypeChecker
def vypocti_aktualni_mnozstvi_prace(exp2name_, fronty_kusu_, rychlosti_pozic_, naskladnovaci_rozdeleni_,
									velikost_nekomplet_, intercept_, slope_1, slope_2):
	# WRITE EP5
	if args_.frequency == hodina_arg:
		a = vypocti_frontu_naskladneni_cas(exp2name_, fronty_kusu_[0], rychlosti_pozic_, naskladnovaci_rozdeleni_,
										   velikost_nekomplet_)
		b = vypocti_frontu_rozhozeni_cas(exp2name_, fronty_kusu_[1], rychlosti_pozic_, naskladnovaci_rozdeleni_,
										 velikost_nekomplet_)
		c = vypocti_frontu_vyhledani_baleni_cas(exp2name_, *fronty_kusu_[2:8], rychlosti_pozic_, intercept_, slope_1, slope_2)
		mnozstvi_prace_return = {}
		for exp_id in exp2name_:
			mnozstvi_prace_return[exp_id] = sum([z[exp_id][h] for h in range(4) for z in [a, b, c]])
		query_write = """
		INSERT INTO expedice_prehled
		(expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (5, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id in exp2name_:
			query_execute(cursor, query_write, (exp_id, mnozstvi_prace_return[exp_id]))
		db.commit()
		db.close()
		if args_.output:
			print("Aktuální množství měřené práce (hodin):", {k: int(v) for k, v in mnozstvi_prace_return.items()})
		return mnozstvi_prace_return
	return None


def get_objednavky_pocet(dny: int, exps: list):
	# READ EP15 , READ EP17
	if args_.frequency == den_arg:
		query = """
				SELECT expedice_id, SUM(vaha) AS pocet_objednavek
				FROM (
					SELECT o.expedice_id expedice_id, %s - DATEDIFF(CURRENT_DATE, objednavka_datetime) + 1 AS vaha
					FROM objednavka o
					JOIN objednavka_expedice_presun oep USING (objednavka_id)
					WHERE objednavka_datetime BETWEEN CURRENT_DATE - INTERVAL %s DAY AND CURRENT_DATE
					AND NOT EXISTS (
						SELECT 1 FROM objednavka_expedice_presun
						WHERE objednavka_id = o.objednavka_id
						AND objednavka_expedice_presun_duvod_id = 4
					)
				) zz
				GROUP BY expedice_id;
				"""
		result = query_db(query, DatabaseAliasLive, (dny, dny))
		obj_dict = {r[0]: r[1] for r in result}
		return {x: obj_dict.get(x, 0) for x in exps}

	if args_.frequency == hodina_arg:
		query = """
				SELECT expedice_id, hodnota
				FROM expedice_prehled
				WHERE expedice_prehled_nazev_id = %s
				"""
		result = []
		if dny == 1:
			result = query_db(query, DatabaseAliasLive, (17,))
		if dny == 14:
			result = query_db(query, DatabaseAliasLive, (15,))
		return {r[0]: r[1] for r in result}
	return None


def write_objednavky(obj_1, obj_14, exps):
	# WRITE EPD17 , WRITE EPD11 , WRITE EP15 , WRITE EP17
	if args_.frequency == den_arg:
		try:
			db, cursor = get_db_cursor(DatabaseAliasLive)
			for exp_id in exps:
				insert_update_query_data = """
				INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
					VALUES (17, %s, %s, DATE(NOW()))
					ON DUPLICATE KEY UPDATE
					expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
					expedice_id = VALUES(expedice_id),
					hodnota = VALUES(hodnota),
					aktualizovano = VALUES(aktualizovano)
				"""
				rozdeleni_query_data = """
				INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
					VALUES (11, %s, %s, DATE(NOW()))
					ON DUPLICATE KEY UPDATE
					expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
					expedice_id = VALUES(expedice_id),
					hodnota = VALUES(hodnota),
					aktualizovano = VALUES(aktualizovano)
				"""
				rozdeleni_query = """
				INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
					VALUES (%s, %s, %s, NOW())
					ON DUPLICATE KEY UPDATE
					expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
					expedice_id = VALUES(expedice_id),
					hodnota = VALUES(hodnota),
					aktualizovano = VALUES(aktualizovano)
				"""
				query_execute(cursor, insert_update_query_data, (exp_id, obj_1.get(exp_id, 0)))
				query_execute(cursor, rozdeleni_query_data,
							  (exp_id, round(obj_14.get(exp_id, 0) / max(sum(obj_14.values()), 1), 3)))

				query_execute(cursor, rozdeleni_query,
							  (15, exp_id, round(obj_14.get(exp_id, 0) / max(sum(obj_14.values()), 1), 3)))
				query_execute(cursor, rozdeleni_query,
							  (17, exp_id, round(obj_1.get(exp_id, 0) / max(sum(obj_1.values()), 1), 3)))
				db.commit()
			db.close()
		except connector.Error as err:
			print(err)
	return


def get_rozdeleni_objednavek_mezi_expedice(exp2name_):
	if args_.frequency in [hodina_arg, den_arg]:
		objednavky_1 = get_objednavky_pocet(1, list(exp2name_.keys()))
		objednavky_14 = get_objednavky_pocet(14, list(exp2name_.keys()))
		write_objednavky(objednavky_1, objednavky_14, exp2name_)
		rozdeleni = {exp_id: float(objednavky_14[exp_id] / max(sum(objednavky_14.values()), 1)) for exp_id in
					 exp2name_.keys()}
		if args_.output:
			print()
			print("Rozdělení objednávek mezi expedice:")
			print([(exp2name_[x], round(rozdeleni[x], 2)) for x in [1, 3, 6, 8, 9]])
			print()
		return rozdeleni
	return None


def get_predikce_poctu_objednavek(rozdeleni_mezi_exp, exp2name_):
	if args_.frequency in (hodina_arg, den_arg):
		query_xgb = """
		SELECT p.predikce_hodnota_celkova
		FROM statistika_objednavka_predikce_xgb p
		JOIN (
			SELECT predikce_pro_den, MAX(predikce_vytvoreno_den) AS max_vytvoreno
			FROM statistika_objednavka_predikce_xgb
			WHERE predikce_pro_den >= CURDATE()
			GROUP BY predikce_pro_den
		) latest
		ON p.predikce_pro_den = latest.predikce_pro_den
		AND p.predikce_vytvoreno_den = latest.max_vytvoreno;
		"""
		query_arima = """
		SELECT predikce_values
		FROM statistika_objednavka_predikce_new
		WHERE predikce_date = CURDATE()"""

		result_arima = query_db(query_arima, DatabaseAliasLive)
		result_xgb = query_db(query_xgb, DatabaseAliasLive)
		if result_arima:
			result_arima = eval(result_arima[0][0])
			result_xgb_start = [int(x[0]) for x in result_xgb][:4]
			result_xgb_zbytek = [int(x[0]) for x in result_xgb][4:dnu_simulace]
			result_full = [int(((3-e) * x + e*y) / 3) for e, x, y in zip(range(5), result_arima, result_xgb_start)] + \
							result_xgb_zbytek
		else:
			result_full = [int(x[0]) for x in result_xgb][:dnu_simulace]
		result_full = [int(x * args_.compensation) for x in result_full]
		if args_.output:
			print("Predikce	počtu objednávek:", result_full)
		return_result = {exp_id: [int(x * rozdeleni_mezi_exp[exp_id]) for x in result_full] for exp_id in exp2name_}
		return return_result
	return None


def get_rozdeleni_objednavek_mezi_hodiny():
	# WRITE EPD23 , WRITE EPD24 , WRITE EPD25 , WRITE EPD26
	# WRITE EPD27 , WRITE EPD28 , WRITE EPD29 , WRITE EPD30
	# WRITE EPD31 , WRITE EPD32 , WRITE EPD33 , WRITE EPD34
	# WRITE EPD35 , WRITE EPD36 , WRITE EPD37 , WRITE EPD38
	# WRITE EPD39 , WRITE EPD40 , WRITE EPD41 , WRITE EPD42
	# WRITE EPD43 , WRITE EPD44 , WRITE EPD45 , WRITE EPD46
	# READ EPD23 , READ EPD24 , READ EPD25 , READ EPD26
	# READ EPD27 , READ EPD28 , READ EPD29 , READ EPD30
	# READ EPD31 , READ EPD32 , READ EPD33 , READ EPD34
	# READ EPD35 , READ EPD36 , READ EPD37 , READ EPD38
	# READ EPD39 , READ EPD40 , READ EPD41 , READ EPD42
	# READ EPD43 , READ EPD44 , READ EPD45 , READ EPD46
	if args_.frequency == tyden_arg:
		query = f"""
			SELECT hodina, h / celkem FROM
			(
			SELECT HOUR(objednavka_datetime) hodina, COUNT(*) h
			FROM `objednavka`
			where objednavka_datetime > NOW() - INTERVAL 7 DAY
			GROUP BY hodina
			) aa
			JOIN
			(
			SELECT COUNT(*) celkem
			FROM objednavka
			WHERE objednavka_datetime > NOW() - INTERVAL 7 DAY
			) bb
		"""
		result = query_db(query, DatabaseAliasLive)
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, 0, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
		"""
		for hodina, hodnota in result:
			query_execute(cursor, insert_update_query, (23 + hodina, hodnota))
		db.commit()
		db.close()
		return {hodina: hodnota for hodina, hodnota in result}
	if args_.frequency == hodina_arg:
		query = """
			SELECT expedice_prehled_data_nazev_id - 23, hodnota
			FROM (
				SELECT expedice_prehled_data_nazev_id,
					hodnota,
					RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id BETWEEN 23 AND 46
			) aa
			WHERE poradi = 1
		"""
		result = query_db(query, DatabaseAliasLive)
		return {hodina: hodnota for hodina, hodnota in result}
	return None


def get_objednavkove_vykryti(exp_name, exp_id):
	# WRITE EP9 , WRITE EP11
	# WRITE EPD9
	# READ EPD9
	if args_.frequency == den_arg:
		DatabaseAlias = get_db_alias_exp(exp_name)
		query_nevykryto_14 = f"""
			WITH vahy AS (
			  SELECT * FROM (
				SELECT 15 - DATEDIFF(CURRENT_DATE, objednavka_datetime) vaha, COUNT(DISTINCT objednavka_id) nevykryto
				FROM `knihy-cme`.polozka p
				JOIN `knihy-cme`.objednavka o USING (objednavka_id)
				JOIN sklad_objednani_ USING (polozka_id)
				WHERE objednavka_datetime BETWEEN CURRENT_DATE - INTERVAL 14 DAY AND CURRENT_DATE()
				AND expedice_id = %s
				GROUP BY vaha
			  ) v1
			  JOIN (
				SELECT 15 - DATEDIFF(CURRENT_DATE, objednavka_datetime) vaha, COUNT(*) celkem
				FROM `knihy-cme`.objednavka
				WHERE objednavka_datetime BETWEEN CURRENT_DATE() - INTERVAL 14 DAY AND CURRENT_DATE()
				AND expedice_id = %s
				GROUP BY vaha
			  ) v2
			  USING (vaha)
			)

			SELECT SUM(vazene_nevykryto) / SUM(vazene_celkem)
			FROM (
			  SELECT vaha * nevykryto vazene_nevykryto, vaha * celkem vazene_celkem
			  FROM vahy
			  ) v3
			"""
		query_nevykryto_vcera = f"""
					SELECT COUNT(distinct objednavka_id) nevykryto
					FROM `knihy-cme`.polozka p
					JOIN `knihy-cme`.objednavka o USING (objednavka_id)
					JOIN sklad_objednani_ USING (polozka_id)
					WHERE objednavka_datetime BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE()
					AND expedice_id = %s
				"""
		data_nevykryto_14 = query_db(query_nevykryto_14, DatabaseAlias, (exp_id, exp_id))[0][0]
		data_nevykryto_vcera = query_db(query_nevykryto_vcera, DatabaseAlias, (exp_id,))[0][0]
		if data_nevykryto_14 is None:
			return 0
		query_celkem_vcera = f"""
					SELECT count(*) celkem
					FROM `knihy-cme`.objednavka
					WHERE objednavka_datetime BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE()
					AND expedice_id = %s
				"""
		vykryti_ = 1 - data_nevykryto_14
		data_celkem_vcera = query_db(query_celkem_vcera, DatabaseAlias, (exp_id,))[0][0]
		vykryti_vcera_ = 1 - (data_nevykryto_vcera / data_celkem_vcera)
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query = """
			INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
				VALUES (%s, %s, %s, NOW())
				ON DUPLICATE KEY UPDATE
				expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
				expedice_id = VALUES(expedice_id),
				hodnota = VALUES(hodnota),
				aktualizovano = VALUES(aktualizovano)
		"""
		insert_update_data_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
				VALUES (9, %s, %s, NOW())
				ON DUPLICATE KEY UPDATE
				expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
				expedice_id = VALUES(expedice_id),
				hodnota = VALUES(hodnota),
				aktualizovano = VALUES(aktualizovano)
		"""
		query_execute(cursor, insert_update_query, (9, exp_id, vykryti_))
		query_execute(cursor, insert_update_query, (11, exp_id, vykryti_vcera_))
		query_execute(cursor, insert_update_data_query, (exp_id, vykryti_))
		db.commit()
		db.close()
		return vykryti_
	else:
		query = """
			SELECT hodnota
			FROM expedice_prehled_data
			WHERE expedice_prehled_data_nazev_id = 9
			AND expedice_id = %s
			ORDER BY aktualizovano DESC
			LIMIT 1
		"""
		result = query_db(query, DatabaseAliasLive, (exp_id,))
		return float(result[0][0])


def get_polozkove_vykryti(exp_name, exp_id):
	# WRITE EP12, WRITE EP14
	# WRITE EPD10
	# READ EP12
	if args_.frequency == den_arg:
		query_nevykryto_14 = f"""
			WITH vahy AS (
				SELECT
					15 - DATEDIFF(CURRENT_DATE(), objednavka_datetime) AS vaha,
					SUM(IF(sklad_objednani_.polozka_id IS NULL, 0, sklad_objednani_mnozstvi)) AS nevykryto,
					SUM(p.polozka_mnozstvi) AS celkem
				FROM `knihy-cme`.polozka p
				JOIN `knihy-cme`.objednavka o USING (objednavka_id)
				JOIN `knihy-cme`.zbozi z USING (zbozi_id)
				LEFT JOIN sklad_objednani_ ON p.polozka_id = sklad_objednani_.polozka_id
											AND sklad_objednani_aktivni = 1
				WHERE objednavka_datetime BETWEEN CURRENT_DATE() - INTERVAL 14 DAY AND CURRENT_DATE()
				AND expedice_id = %s
				AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
				AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
				AND z.zbozi_cena > 2
				GROUP BY vaha
			)
			SELECT SUM(vaha * nevykryto) / SUM(vaha * celkem) AS podil_vazeny
			FROM vahy;

			"""
		query_nevykryto_vcera = f"""
					SELECT SUM(IF(sklad_objednani_.polozka_id IS NULL, 0, 1)) as nevykryto_polozek
					FROM `knihy-cme`.polozka p
					JOIN `knihy-cme`.objednavka o USING (objednavka_id)
					JOIN `knihy-cme`.zbozi z USING (zbozi_id)
					LEFT JOIN sklad_objednani_ ON (p.polozka_id = sklad_objednani_.polozka_id AND sklad_objednani_aktivni = 1)
					WHERE polozka_datetime BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE()
					AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
					AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
					AND z.zbozi_cena > 2
					AND expedice_id = %s
				"""
		DatabaseAlias = get_db_alias_exp(exp_name)
		data_nevykryto = query_db(query_nevykryto_14, DatabaseAlias, (exp_id,))[0][0]
		data_nevykryto_vcera = query_db(query_nevykryto_vcera, DatabaseAlias, (exp_id,))[0][0]
		if data_nevykryto is None:
			return 0

		query_celkem_vcera = f"""
					SELECT COUNT(*) celkem
					FROM `knihy-cme`.polozka p
					JOIN `knihy-cme`.objednavka o USING (objednavka_id)
					JOIN `knihy-cme`.zbozi z USING (zbozi_id)
					WHERE polozka_datetime BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE()
					AND zbozi_nazev NOT LIKE '%Sběratelská záložka%'
					AND zbozi_nazev NOT LIKE '%Zberateľská záložka%'
					AND z.zbozi_cena > 2
					AND expedice_id = %s
				"""
		data_celkem_vcera = query_db(query_celkem_vcera, DatabaseAliasLive, (exp_id,))[0][0]
		vykryti_ = 1 - data_nevykryto
		vykryti_vcera = 1 - (data_nevykryto_vcera / max(data_celkem_vcera, 1))
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query = """
			INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
				VALUES (%s, %s, %s, NOW())
				ON DUPLICATE KEY UPDATE
				expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
				expedice_id = VALUES(expedice_id),
				hodnota = VALUES(hodnota),
				aktualizovano = VALUES(aktualizovano)
		"""
		insert_update_data_query = """
					INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
						VALUES (10, %s, %s, NOW())
						ON DUPLICATE KEY UPDATE
						expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
						expedice_id = VALUES(expedice_id),
						hodnota = VALUES(hodnota),
						aktualizovano = VALUES(aktualizovano)
				"""
		query_execute(cursor, insert_update_query, (12, exp_id, vykryti_))
		query_execute(cursor, insert_update_query, (14, exp_id, vykryti_vcera))
		query_execute(cursor, insert_update_data_query, (exp_id, vykryti_))
		db.commit()
		db.close()
		return vykryti_
	else:
		query = """
			SELECT hodnota
			FROM expedice_prehled
			WHERE expedice_prehled_nazev_id = 12
			AND expedice_id = %s
			ORDER BY aktualizovano DESC
			LIMIT 1
		"""
		result = query_db(query, DatabaseAliasLive, (exp_id,))
		return float(result[0][0])


def get_rozdeleni_doby_vykryti():
	# READ EPD67 , READ EPD68 , READ EPD69 , READ EPD70 , READ EPD71 , READ EPD72 , READ EPD73
	# READ EPD74 , READ EPD75 , READ EPD76 , READ EPD77 , READ EPD78 , READ EPD79 , READ EPD80
	# READ EPD81 , READ EPD82 , READ EPD83 , READ EPD84 , READ EPD85 , READ EPD86 , READ EPD87
	# READ EPD88 , READ EPD89 , READ EPD90 , READ EPD91 , READ EPD92 , READ EPD93 , READ EPD94
	# READ EPD95 , READ EPD96 , READ EPD97 , READ EPD98 , READ EPD99 , READ EPD100 , READ EPD101
	# READ EPD102 , READ EPD103 , READ EPD104 , READ EPD105 , READ EPD106 , READ EPD107 , READ EPD108
	# READ EPD109 , READ EPD110 , READ EPD111 , READ EPD112 , READ EPD113 , READ EPD114 , READ EPD115
	# READ EPD116 , READ EPD117 , READ EPD118 , READ EPD119 , READ EPD120 , READ EPD121 , READ EPD122
	# READ EPD123 , READ EPD124 , READ EPD125 , READ EPD126 , READ EPD127 , READ EPD128 , READ EPD129
	# READ EPD130 , READ EPD131 , READ EPD132 , READ EPD133 , READ EPD134 , READ EPD135 , READ EPD136
	# READ EPD137 , READ EPD138 , READ EPD139 , READ EPD140 , READ EPD141 , READ EPD142 , READ EPD143
	# READ EPD144 , READ EPD145 , READ EPD146 , READ EPD147 , READ EPD148 , READ EPD149 , READ EPD150
	# READ EPD151 , READ EPD152 , READ EPD153 , READ EPD154 , READ EPD155 , READ EPD156 , READ EPD157
	# READ EPD158 , READ EPD159 , READ EPD160 , READ EPD161 , READ EPD162 , READ EPD163 , READ EPD164
	# WRITE EPD67 , WRITE EPD68 , WRITE EPD69 , WRITE EPD70 , WRITE EPD71 , WRITE EPD72 , WRITE EPD73
	# WRITE EPD74 , WRITE EPD75 , WRITE EPD76 , WRITE EPD77 , WRITE EPD78 , WRITE EPD79 , WRITE EPD80
	# WRITE EPD81 , WRITE EPD82 , WRITE EPD83 , WRITE EPD84 , WRITE EPD85 , WRITE EPD86 , WRITE EPD87
	# WRITE EPD88 , WRITE EPD89 , WRITE EPD90 , WRITE EPD91 , WRITE EPD92 , WRITE EPD93 , WRITE EPD94
	# WRITE EPD95 , WRITE EPD96 , WRITE EPD97 , WRITE EPD98 , WRITE EPD99 , WRITE EPD100 , WRITE EPD101
	# WRITE EPD102 , WRITE EPD103 , WRITE EPD104 , WRITE EPD105 , WRITE EPD106 , WRITE EPD107 , WRITE EPD108
	# WRITE EPD109 , WRITE EPD110 , WRITE EPD111 , WRITE EPD112 , WRITE EPD113 , WRITE EPD114 , WRITE EPD115
	# WRITE EPD116 , WRITE EPD117 , WRITE EPD118 , WRITE EPD119 , WRITE EPD120 , WRITE EPD121 , WRITE EPD122
	# WRITE EPD123 , WRITE EPD124 , WRITE EPD125 , WRITE EPD126 , WRITE EPD127 , WRITE EPD128 , WRITE EPD129
	# WRITE EPD130 , WRITE EPD131 , WRITE EPD132 , WRITE EPD133 , WRITE EPD134 , WRITE EPD135 , WRITE EPD136
	# WRITE EPD137 , WRITE EPD138 , WRITE EPD139 , WRITE EPD140 , WRITE EPD141 , WRITE EPD142 , WRITE EPD143
	# WRITE EPD144 , WRITE EPD145 , WRITE EPD146 , WRITE EPD147 , WRITE EPD148 , WRITE EPD149 , WRITE EPD150
	# WRITE EPD151 , WRITE EPD152 , WRITE EPD153 , WRITE EPD154 , WRITE EPD155 , WRITE EPD156 , WRITE EPD157
	# WRITE EPD158 , WRITE EPD159 , WRITE EPD160 , WRITE EPD161 , WRITE EPD162 , WRITE EPD163 , WRITE EPD164
	if args_.frequency == tyden_arg:
		query = f"""
			SELECT expedice_id, den_v_tydnu, pocet_dni, COUNT(*) / celkem cetnost  FROM
			(
				SELECT DAYOFWEEK(objednavka_datetime) den_v_tydnu, expedice_id,
					DAYOFWEEK(doba_zabaleni_zkompletneno) -
					DAYOFWEEK(objednavka_datetime) +
					IFNULL(7 * (WEEK(doba_zabaleni_zkompletneno) - WEEK(objednavka_datetime)), 0) pocet_dni
				FROM doba_zabaleni
				JOIN objednavka using (objednavka_id)
				WHERE objednavka_datetime
					BETWEEN NOW() - INTERVAL 70 DAY
					AND NOW() - INTERVAL 14 DAY
			) zz
			JOIN
			(
				SELECT DAYOFWEEK(objednavka_datetime) den_v_tydnu, expedice_id, COUNT(*) celkem
				FROM doba_zabaleni
				JOIN objednavka USING (objednavka_id)
				WHERE objednavka_datetime BETWEEN NOW() - INTERVAL 70 DAY AND NOW() - INTERVAL 14 DAY
				AND DATE(objednavka_datetime) != DATE(doba_zabaleni_zkompletneno)
				AND doba_zabaleni_zkompletneno > NOW() - INTERVAL 1 YEAR
				GROUP BY den_v_tydnu, expedice_id
			) aa
			USING (den_v_tydnu, expedice_id)
			WHERE pocet_dni BETWEEN 1 AND 14
			GROUP BY expedice_id, den_v_tydnu, pocet_dni
			ORDER BY expedice_id, den_v_tydnu, pocet_dni
		"""
		result = query_db(query, DatabaseAliasLive)
		db, cursor = get_db_cursor(DatabaseAliasLive)
		insert_update_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, %s, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
		"""
		for exp_id, den_v_tydnu, pocet_dni, hodnota in result:
			query_execute(cursor, insert_update_query, (52 + 14 * den_v_tydnu + pocet_dni, exp_id, hodnota))
		db.commit()
		db.close()
		result2 = {}
		for exp_id, den_v_tydnu, pocet_dni, hodnota in result:
			result2.setdefault(exp_id, {}).setdefault(den_v_tydnu, {})[pocet_dni] = hodnota
		return result2
	if args_.frequency == hodina_arg:
		query = """
			SELECT expedice_prehled_data_nazev_id - 52, expedice_id, hodnota
			FROM (
				SELECT expedice_prehled_data_nazev_id,
					expedice_id,
					hodnota,
					RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id BETWEEN 67 AND 164
			) aa
			WHERE poradi = 1
		"""
		result = query_db(query, DatabaseAliasLive)
		result = [((x - 1) // 14, (x - 1) % 14 + 1, y, z) for x, y, z in result]
		result2 = {}
		for den_v_tydnu, pocet_dni, exp_id, hodnota in result:
			result2.setdefault(exp_id, {}).setdefault(den_v_tydnu, {})[pocet_dni] = hodnota
		for eid in result2:
			for d in range(1, 8):
				result2[eid][d][0] = 1 - sum(result2[eid][d].values())
		return result2
	return None


def get_minulost_nekomplet_den(exp_id, exp_name):
	# WRITE EPD165
	# READ EPD165
	if args_.frequency == den_arg:
		databaseAlias = get_db_alias_exp(exp_name)
		query = f"""SELECT COUNT(distinct objednavka_id) nevykryto
					FROM `knihy-cme`.polozka p
					JOIN `knihy-cme`.objednavka o USING (objednavka_id)
					JOIN sklad_objednani_ USING (polozka_id)
					WHERE objednavka_datetime > CURRENT_DATE() - INTERVAL 14 DAY
					AND expedice_id = %s
					GROUP BY DATE(objednavka_datetime)
					ORDER BY DATE(objednavka_datetime) ASC"""
		data = query_db(query, databaseAlias, (exp_id,))
		insert_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (165, %s, %s, DATE(NOW() - INTERVAL %s DAY))
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
			"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for e, d in enumerate(data[:-1]):
			query_execute(cursor, insert_query, (exp_id, d[0], 14 - e))
		db.commit()
		db.close()
		return [d[0] for d in data]
	if args_.frequency == hodina_arg:
		query_get_minulost = """
			SELECT COUNT(*)
			FROM objednavka o
			WHERE objednavka_datetime BETWEEN CURRENT_DATE() - INTERVAL 14 DAY AND CURRENT_DATE()
			AND expedice_id = %s
			AND obj_stav_id = 1
			GROUP BY DATE(objednavka_datetime)
			ORDER BY DATE(objednavka_datetime) ASC
		"""
		query_get_today = f"""SELECT COUNT(distinct objednavka_id) nevykryto
			FROM `knihy-cme`.polozka p
			JOIN `knihy-cme`.objednavka o USING (objednavka_id)
			JOIN sklad_objednani_ USING (polozka_id)
			WHERE objednavka_datetime > CURRENT_DATE()
			AND expedice_id = %s
			GROUP BY DATE(objednavka_datetime)
			ORDER BY DATE(objednavka_datetime) ASC"""
		result_minulost = [d[0] for d in query_db(query_get_minulost, DatabaseAliasLive, (exp_id,))]
		databaseAlias = get_db_alias_exp(exp_name)
		result_today = query_db(query_get_today, databaseAlias, (exp_id,))
		if result_today:
			return result_minulost + [result_today[0][0]]
		else:
			return [0] * 15
	return None


def get_nevykryte_objednavky(exp_id, exp_name):
	databaseAlias = get_db_alias_exp(exp_name)
	query = """SELECT COUNT(DISTINCT(objednavka_id)) nevykryto
				FROM `knihy-cme`.polozka p
				JOIN `knihy-cme`.objednavka o USING (objednavka_id)
				JOIN sklad_objednani_ USING (polozka_id)
				WHERE objednavka_datetime > NOW() - INTERVAL 14 DAY
				AND expedice_id = %s"""
	result = query_db(query, databaseAlias, (exp_id,))
	nevykryte_objednavky_ = float(result[0][0])
	return nevykryte_objednavky_


def get_nevykryte_polozky(exp_id, exp_name):
	databaseAlias = get_db_alias_exp(exp_name)
	query = """SELECT SUM(sklad_objednani_mnozstvi) nevykryto
				FROM `knihy-cme`.polozka p
				JOIN `knihy-cme`.objednavka o USING (objednavka_id)
				JOIN sklad_objednani_ USING (polozka_id)
				WHERE objednavka_datetime > NOW() - INTERVAL 14 DAY
				AND expedice_id = %s"""
	result = query_db(query, databaseAlias, (exp_id,))
	nevykryte_polozky_ = float(result[0][0])
	return nevykryte_polozky_


def get_nevykryte_kusy_na_objednavku(exp_id, exp_name):
	databaseAlias = get_db_alias_exp(exp_name)
	query = """
			SELECT SUM(pocet_objednano) / COUNT(*)
			FROM (
				SELECT
					SUM(p.polozka_mnozstvi - IFNULL(so.objednano_mnozstvi, 0)) AS pocet_skladem,
					SUM(IFNULL(so.objednano_mnozstvi, 0)) AS pocet_objednano,
					SUM(p.polozka_mnozstvi) AS pocet_celkem
				FROM (
					SELECT p.polozka_id, p.objednavka_id, p.polozka_mnozstvi
					FROM `knihy-cme`.polozka p
					JOIN `knihy-cme`.objednavka o USING (objednavka_id)
					WHERE p.polozka_datetime > NOW() - INTERVAL 14 DAY
					  AND o.expedice_id = %s
				) p
				LEFT JOIN (
					SELECT polozka_id, SUM(sklad_objednani_mnozstvi) AS objednano_mnozstvi
					FROM sklad_objednani_
					WHERE sklad_objednani_pozadavek > NOW() - INTERVAL 14 DAY
					GROUP BY polozka_id
				) so ON p.polozka_id = so.polozka_id
				GROUP BY p.objednavka_id
				HAVING SUM(IFNULL(so.objednano_mnozstvi, 0)) > 0 -- pouze objednávky s alespoň 1 objednaným kusem
			) axaxax"""
	result = query_db(query, databaseAlias, (exp_id,))
	nevykryto_na_nekomplet_obj = float(result[0][0])
	return nevykryto_na_nekomplet_obj


def get_nevykryto_na_objednavku(exp2name_):
	# WRITE EPD18
	# READ EPD18
	if args_.frequency == den_arg:
		nevykryto_na_objednavku_ = {
			exp_id: get_nevykryte_kusy_na_objednavku(exp_id, exp_name) for
			exp_id, exp_name in exp2name_.items()}
		insert_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (18, %s, %s, DATE(NOW()))
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id in exp2name_:
			query_execute(cursor, insert_query, (exp_id, nevykryto_na_objednavku_[exp_id]))
		db.commit()
		db.close()
		return nevykryto_na_objednavku_
	if args_.frequency == hodina_arg:
		nevykryto_na_objednavku_ = {}
		query = """
			SELECT hodnota
			FROM expedice_prehled_data
			WHERE expedice_prehled_data_nazev_id = 18
			AND expedice_id = %s
			ORDER BY aktualizovano DESC
			LIMIT 1
		"""
		for exp_id in exp2name_.keys():
			result = query_db(query, DatabaseAliasLive, (exp_id,))
			nevykryto_na_objednavku_[exp_id] = float(result[0][0])
		return nevykryto_na_objednavku_
	return None


def get_prisun_objednavek(predikce_rozdeleni_objednavek_, vykryti_, hodiny_, exps):
	if args_.frequency == hodina_arg:
		prisun = {}
		for exp in exps:
			prisun[exp] = [(x * vykryti_[exp][0], x * (1 - vykryti_[exp][0])) for x in
						   predikce_rozdeleni_objednavek_[exp]]
			prisun[exp] = [(prisun[exp][j // 24][0] * hodiny_[j % 24],
							prisun[exp][j // 24][1] * hodiny_[j % 24]) for j in
						   range(len(prisun[exp]) * 24)]
		return prisun
	return None


def prehodnot_rozdeleni(rozdeleni_exp):
	nova_pravdepodobnost = {}
	suma = sum(rozdeleni_exp.values())
	dny_sorted = range(15)
	for den in dny_sorted:
		nova_pravdepodobnost[den] = rozdeleni_exp[den] / suma
		suma -= rozdeleni_exp[den]
	return nova_pravdepodobnost


def vypocti_zkompletneni(dny_v_tydnu, rozdeleni, full_nekomplet):
	vysledek = np.zeros(len(full_nekomplet))
	rozdeleni_minulost = {den: prehodnot_rozdeleni(rozdeleni[den]) for den in rozdeleni}
	minulost = [int(x) for x in full_nekomplet[:14]]
	budoucnost = [int(x) for x in full_nekomplet[14:]]
	for den_objednavky, pocet_objednavek in enumerate(minulost):
		den_v_tydnu = dny_v_tydnu[den_objednavky % len(dny_v_tydnu)]
		distribuce = rozdeleni_minulost.get(den_v_tydnu, {})
		for zpozdeni, pravdepodobnost in distribuce.items():
			den_pripravy = den_objednavky + zpozdeni
			if 13 < den_pripravy < len(full_nekomplet):
				vysledek[den_pripravy] += pocet_objednavek * pravdepodobnost
				pocet_objednavek *= (1 - pravdepodobnost)
	for den_objednavky, pocet_objednavek in enumerate(budoucnost):
		den_v_tydnu = dny_v_tydnu[(den_objednavky + 14) % len(dny_v_tydnu)]
		distribuce = rozdeleni.get(den_v_tydnu, {})

		for zpozdeni, pravdepodobnost in distribuce.items():
			den_pripravy = den_objednavky + 14 + zpozdeni
			if den_pripravy < len(full_nekomplet):
				vysledek[den_pripravy] += pocet_objednavek * pravdepodobnost
	return vysledek[14:]


def vypocti_preliti_naskladneni(rozdeleni, minulost_nekomplet, nevykryto_na_objednavku, exps, prisun_):
	if args_.frequency == hodina_arg:
		dny_v_tydnu = {h: (dt.date.today().weekday() + h + 1) % 7 + 1 for h in range(7)}
		preliti_ = {}
		prisun_nekomplet = {exp_id: [x[1] for x in prisun_[exp_id]] for exp_id in exps}
		for exp in exps:
			prisun = [sum(prisun_nekomplet[exp][max(j, 0):j + 24]) for j in
					  range(0, len(prisun_nekomplet[exp]), 24)]
			full_nekomplet_zbyle = minulost_nekomplet[exp][:-1] + [minulost_nekomplet[exp][-1] + prisun[0]] + prisun[1:]
			# full_nekomplet = [0 if e != 18 else x for e, x in enumerate(full_nekomplet)]
			zkompletneno = [sum([full_nekomplet_zbyle[h] * rozdeleni[exp][dny_v_tydnu[h % 7]].get(14 + j - h, 0) *
								 nevykryto_na_objednavku[exp] for h in range(14 + dnu_simulace)]) for j in
							range(dnu_simulace)]
			zkompletneno = vypocti_zkompletneni(dny_v_tydnu, rozdeleni[exp], full_nekomplet_zbyle)
			preliti_[exp] = [x * nevykryto_na_objednavku[exp] for x in zkompletneno]
		return preliti_
	return None


def get_prisun_total_cas(prisun_naskladneni_cas_, prisun_vyhledani_cas_, exps):
	if args_.frequency == hodina_arg:
		prisun_total_cas_ = {exp: [prisun_naskladneni_cas_[exp][h] + prisun_vyhledani_cas_[exp][h] for h in
								   range(len(prisun_naskladneni_cas_[exp]))] for exp in exps}
		return prisun_total_cas_
	return None


def get_odpracovane_hodiny(hpp=False):
	ConCME, CurCME = get_db_cursor(DatabaseAliasLive)
	nepracovni_dny = get_nepracovni_dny_tuple()
	if not hpp:
		uzivatel_typ = " AND uzivatel_typ_id != 2 "
	else:
		uzivatel_typ = " AND uzivatel_typ_id = 2 "
	query = f"""
			SELECT expedice_id, SUM(smena_do) - SUM(smena_od) - SUM(IFNULL(smena_pauza, 0))
			FROM smena
			JOIN uzivatel_mzda_nastaveni USING (uzivatel_id)
			WHERE smena_do_datetime BETWEEN CURRENT_DATE() - INTERVAL 14 DAY AND CURRENT_DATE()
			AND smena_od > 100000
			AND smena_do > 100000
			AND smena_smazano = 0
			AND expedice_id IS NOT NULL
			{uzivatel_typ}
			AND DATE(smena_od_datetime) NOT IN {nepracovni_dny}
			GROUP BY expedice_id
			"""

	query_execute(CurCME, query)
	data = CurCME.fetchall()
	odpracovane_hodiny_ = {x[0]: float(x[1] / 3600) for x in data}
	return odpracovane_hodiny_


def get_odpracovane_hodiny_merene(exp2name_, hpp=False):
	"""
	Získáme počty odpracovaných hodin měřené práce v expedici za posledních 28 dní
	"""
	nepracovni_dny = get_nepracovni_dny_tuple()
	odpracovane_hodiny_ = {}
	if not hpp:
		uzivatel_typ = " AND uzivatel_typ_id != 2 "
	else:
		uzivatel_typ = " AND uzivatel_typ_id = 2 "
	query = f"""
				SELECT SUM(odpracovany_cas_orezany) / 3600
				FROM merena_prace_den
				JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id)
				WHERE den BETWEEN CURRENT_DATE() - INTERVAL 14 DAY AND CURRENT_DATE() - INTERVAL 1 DAY
				AND merena_prace_typ_id < 5
				AND den NOT IN {nepracovni_dny}
				{uzivatel_typ}
				"""
	for exp_id, exp_name in exp2name_.items():
		databaseAlias = get_db_alias_exp(exp_name)
		result = query_db(query, databaseAlias)
		odpracovane_hodiny_[exp_id] = float(result[0][0])
	return odpracovane_hodiny_


def get_podil_merene(exps_nazev_):
	# WRITE EPD7
	# READ EPD7
	if args_.frequency == den_arg:
		odpracovana = get_odpracovane_hodiny()
		odpracovana_merena = get_odpracovane_hodiny_merene(exps_nazev_)
		podil = {exp: odpracovana_merena.get(exp, 0) / max(odpracovana.get(exp, 0), 1) for exp in odpracovana.keys()}
		insert_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (7, %s, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
			"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id in odpracovana.keys():
			query_execute(cursor, insert_query, (exp_id, podil[exp_id]))
		db.commit()
		db.close()
		return podil
	if args_.frequency == hodina_arg:
		query = """
			SELECT expedice_id, hodnota
			FROM (
				SELECT expedice_id,
					hodnota,
					RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = 7
			) aa
			WHERE poradi = 1
		"""
		result = query_db(query, DatabaseAliasLive)
		return {exp_id: float(hodnota) for exp_id, hodnota in result}
	return None


def get_podil_merene_hpp(exps_nazev_):
	# WRITE EPD47
	# READ EPD47
	if args_.frequency == den_arg:
		odpracovana = get_odpracovane_hodiny(hpp=True)
		odpracovana_merena = get_odpracovane_hodiny_merene(exps_nazev_, hpp=True)
		podil = {exp: odpracovana_merena.get(exp, 0) / max(odpracovana.get(exp, 0), 1) for exp in odpracovana.keys()}
		insert_query = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (47, %s, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
			"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id in odpracovana.keys():
			query_execute(cursor, insert_query, (exp_id, podil[exp_id]))
		db.commit()
		db.close()
		return podil
	if args_.frequency == hodina_arg:
		query = """
			SELECT expedice_id, hodnota
			FROM (
				SELECT expedice_id,
					hodnota,
					RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = 47
			) aa
			WHERE poradi = 1
		"""
		result = query_db(query, DatabaseAliasLive)
		return {exp_id: float(hodnota) for exp_id, hodnota in result}
	return None


def get_prace_in(prisun_prace_merena, merena_podil):
	"""
	Zkompletování časové řady příchozí práce
	"""
	prisun_prace = {exp: [x / max(merena_podil[exp], 0.1) for x in prisun_prace_merena.get(exp, [])] for exp in
					merena_podil.keys()}
	"""fix
	prenasobeni_podil_vanoce = {1: 10, 3: 15, 6: 7, 8: 5, 9: 13}
	prisun_prace = {
		exp: [d * prenasobeni_podil_vanoce.get(exp, 1) for d in v] for exp, v in
					prisun_prace.items()
	}
	fix"""
	return prisun_prace


def get_psh(rychlosti, merena_podil, dni):
	# WRITE EP21, WRITE EP23
	# WRITE EPD170, WRITE EPD13
	"""
	Výpočet PS/h pro každou expedici
	Jeden kus musí projít všemi pozicemi, proto se sčítá rychlost všech pozic
	Vydělíme podílem měřené práce
	"""
	if args_.frequency == hodina_arg:
		pruchod_skladem_merena = {exp_id: sum(rychlosti.get(exp_id, [0])) for exp_id in merena_podil.keys()}
		pruchod_skladem = {exp_id: pruchod_skladem_merena[exp_id] / max(merena_podil[exp_id], 0.1) for exp_id in
						   merena_podil.keys()}
		psh_ = {exp: 3600 / max(pruchod_skladem[exp], 1) for exp in merena_podil.keys()}

		insert_query = """
			INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, %s, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
			"""

		insert_query_data = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, %s, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
			"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id in merena_podil.keys():
			if dni == 1:
				query_execute(cursor, insert_query, (23, exp_id, psh_[exp_id]))
				query_execute(cursor, insert_query_data, (170, exp_id, psh_[exp_id]))
			if dni == 14:
				query_execute(cursor, insert_query, (21, exp_id, psh_[exp_id]))
				query_execute(cursor, insert_query_data, (13, exp_id, psh_[exp_id]))
		db.commit()
		db.close()
		print(f"PS/h pro {dni} dní: {psh_}")

		return


def filter_dates(json_argument, date_start, days_pred):
	"""
	Úprava datové struktury, aby na sebe seděly správné dny
	"""
	if isinstance(json_argument, int):
		return json_argument
	else:
		return [x for x in json_argument if date_start <= dt.datetime.strptime(x["datum"], "%Y-%m-%d").date()
				< date_start + dt.timedelta(days=days_pred)]


def get_hodiny(date_start, days_pred, exp2name_, oddelene=False, soucet=False) -> np.ndarray:
	if args_.frequency == hodina_arg:
		"""
		Zavolá endpoint na získání naplánovaných hodin, buď všechny dohromady, nebo rozdělené na ranní a odpolední směnu
		"""
		url = "https://knihy.90.cz/skripty/sklad_frontend/matematiciAPI.php?naplanovane_hodiny"
		with open('data/config.json') as f:
			config_json = json.load(f)
			config_json = [config_json['ldap_user'], config_json['ldap_password']]
		response = requests.post(url, auth=(config_json[0], config_json[1]))
		str_data = ""
		try:
			str_data = str(response.json())
		except requests.exceptions.JSONDecodeError:
			with open("json_hodiny.txt", "r") as f:
				str_data = f.read()
		data = eval(str_data.replace("[]", "{'aktivni': '0', 'naplanovano_hodin': '0.0000', 'zamek': '0'}"))
		data = [{k: filter_dates(v, date_start, days_pred) for k, v in x.items()} for x in data]
		razeni = {}
		prerazeni = {}
		for e, exp_id in enumerate(sorted(exp2name_.keys())):
			for h, x in enumerate(data):
				if int(x["expedice_id"]) == exp_id:
					razeni.update({h: exp_id})
					prerazeni.update({h: e})

		for e, exp_id in enumerate(sorted(exp2name_.keys())):
			if exp_id not in razeni.values():
				razeni.update({-len(razeni.keys()): exp_id})
				prerazeni.update({-len(prerazeni.keys()): e})
		if soucet:
			hours = {exp: 0 for exp in sorted(exp2name_.keys())}
			for h, exp_id in razeni.items():
				for j in range(days_pred):
					if h >= 0:
						hours[razeni[h]] += int(float(data[h]["dny"][j]["smeny"]["ranni"]["naplanovano_hodin"]) + 1) * \
											int(data[h]["dny"][j]["smeny"]["ranni"]["aktivni"]) + \
											int(float(data[h]["dny"][j]["smeny"]["odpoledni"]["naplanovano_hodin"])) * \
											int(data[h]["dny"][j]["smeny"]["odpoledni"]["aktivni"])
			return hours
		if oddelene:
			hours = np.zeros((len(razeni), days_pred, 2))
			for h, exp_id in razeni.items():
				for j in range(days_pred):
					if h >= 0:
						if j < len(data[h]["dny"]):
							hours[prerazeni[h], j] = (int(float(data[h]["dny"][j]["smeny"]["ranni"]["naplanovano_hodin"]) + 1) *
													  int(data[h]["dny"][j]["smeny"]["ranni"]["aktivni"]),
													  int(float(
														  data[h]["dny"][j]["smeny"]["odpoledni"]["naplanovano_hodin"])) *
													  int(data[h]["dny"][j]["smeny"]["odpoledni"]["aktivni"]),)
						else:
							hours[prerazeni[h], j] = (0, 0)
		else:
			hours = np.zeros((len(razeni), days_pred))
			for h, exp_id in razeni.items():
				for j in range(days_pred):
					if h >= 0:
						if j < len(data[h]["dny"]):
							hours[prerazeni[h], j] = int(float(data[h]["dny"][j]["smeny"]["ranni"]["naplanovano_hodin"]) + 1) * \
													 int(data[h]["dny"][j]["smeny"]["ranni"]["aktivni"]) + \
													 int(float(
														 data[h]["dny"][j]["smeny"]["odpoledni"]["naplanovano_hodin"])) * \
													 int(data[h]["dny"][j]["smeny"]["odpoledni"]["aktivni"])
						else:
							hours[prerazeni[h], j] = 0
		return hours
	return None


def get_aktivni_smeny(date_start, days_pred, exp2name_):
	if args_.frequency == hodina_arg:
		"""
		Zavolá endpoint, získá informace o směnách
		Vrátí pro každou směnu 0/1 jako neaktivní/aktivní
		"""
		url = "https://knihy.90.cz/skripty/sklad_frontend/matematiciAPI.php?naplanovane_hodiny"
		with open('data/config.json') as f:
			config_json = json.load(f)
			config_json = [config_json['ldap_user'], config_json['ldap_password']]
		response = requests.post(url, auth=(config_json[0], config_json[1]))
		str_data = ""
		try:
			str_data = str(response.json())
		except requests.exceptions.JSONDecodeError:
			with open("json_hodiny.txt", "r") as f:
				str_data = f.read()
		data = eval(str_data.replace("[]", "{'aktivni': '0', 'naplanovano_hodin': '0.0000', 'zamek': '0'}"))
		data = [{k: filter_dates(v, date_start, days_pred) for k, v in x.items()} for x in data]
		razeni = {}
		for exp_id in sorted(exp2name_.keys()):
			for h, x in enumerate(data):
				if int(x["expedice_id"]) == exp_id:
					razeni.update({h: exp_id})
		for exp_id in sorted(exp2name_.keys()):
			if exp_id not in razeni.values():
				razeni.update({-len(razeni.keys()): exp_id})

		aktivni = {exp: [] for exp in exp2name_}
		for h, exp_id in razeni.items():
			for j in range(days_pred):
				if h >= 0:
					if j < len(data[h]["dny"]):
						aktivni[exp_id].append((int(data[h]["dny"][j]["smeny"]["ranni"]["aktivni"]),
												int(data[h]["dny"][j]["smeny"]["odpoledni"]["aktivni"]),
												int(data[h]["dny"][j]["smeny"].get("nocni", {}).get("aktivni", 0))))
					else:
						aktivni[exp_id].append((0, 0, 0))
				else:
					aktivni[exp_id].append((0, 0, 0))
		return aktivni
	return None


def get_smeny(date_start, days_pred, exp2name_, soucet=False) -> dict[any, int] | dict[any, dict]:
	if args_.frequency == hodina_arg:
		"""
		Pro každou expedici vrací počty reálně odpracovaných hodin za posledních 14 dní
		"""
		ConCME, CurCME = get_db_cursor(DatabaseAliasLive)
		if soucet:
			smeny = {exp: 0 for exp in exp2name_}
			for exp_id in sorted(exp2name_.keys()):
				for datum in date_range(date_start, date_start + dt.timedelta(days=days_pred - 1)):
					datum = datum.strftime("%Y-%m-%d")
					query = f"""SELECT IFNULL(SUM(smena_do - smena_od - IFNULL(smena_pauza,0)) / 3600, 0)
								FROM smena
								JOIN uzivatel_mzda_nastaveni
								USING (uzivatel_id)
								WHERE smena_od_datetime < %s + INTERVAL 1 DAY
								AND smena_od_datetime >= %s
								AND smena_do > 0
								AND smena_od > 0
								AND smena_smazano = 0
								AND uzivatel_typ_id != 2
								AND expedice_id = %s"""
					query_execute(CurCME, query, (datum, datum, exp_id))
					data = CurCME.fetchall()
					if data[0][0] is not None:
						smeny[exp_id] += float(data[0][0])
			ConCME.close()
			return smeny

		smeny = {exp: {} for exp in exp2name_}
		for exp_id in sorted(exp2name_.keys()):
			for datum in date_range(date_start, date_start + dt.timedelta(days=days_pred - 1)):
				smeny[exp_id][datum] = {h: 0 for h in [1, 2]}
				for typ in [1, 2]:
					query = f"""SELECT IFNULL(SUM(smena_do - smena_od - IFNULL(smena_pauza,0)) / 3600, 0)
								FROM planovac_brigadnik plan
								JOIN planovac_smena USING (planovac_smena_id)
								JOIN planovac_den USING (planovac_den_id)
								JOIN planovac_obdobi o USING (planovac_obdobi_id)
								JOIN uzivatel_mzda_nastaveni USING (uzivatel_id)
								LEFT JOIN smena
								ON smena.uzivatel_id = plan.uzivatel_id
								WHERE smena_od_datetime < %s + INTERVAL 1 DAY
								AND smena_od_datetime >= %s
								AND planovac_den_datum = %s
								AND planovac_smena_typ = %s
								AND smena_do > 0
								AND smena_od > 0
								AND smena_smazano = 0
								AND planovac_brigadnik_smazano = 0
								AND uzivatel_typ_id != 2
								AND o.expedice_id = %s"""
					query_execute(CurCME, query, (datum, datum, datum, typ, exp_id))
					data = CurCME.fetchall()
					smeny[exp_id][datum][typ] = float(data[0][0])
		ConCME.close()
		return smeny
	return {}


def get_rozdeleni_prace_hodiny(exp2name_):
	# WRITE EPD55 , WRITE EPD56 , WRITE EPD57 , WRITE EPD58 , WRITE EPD59 , WRITE EPD60
	# WRITE EPD61 , WRITE EPD62 , WRITE EPD63 , WRITE EPD64 , WRITE EPD65 , WRITE EPD66
	# READ EPD55 , READ EPD56 , READ EPD57 , READ EPD58 , READ EPD59 , READ EPD60
	# READ EPD61 , READ EPD62 , READ EPD63 , READ EPD64 , READ EPD65 , READ EPD66
	if args_.frequency == tyden_arg:
		"""
		Spočítá běžné rozdělení množství odpracovaných hodin mezi jednotlivé denní hodiny
		"""
		query = f"""
				SELECT HOUR(smena_od_datetime), HOUR(smena_do_datetime)
				FROM smena
				WHERE smena_od_datetime > NOW() - INTERVAL 14 DAY
				AND smena_do_datetime > NOW() - INTERVAL 14 DAY
				AND smena_smazano = 0
				AND expedice_id = %s
				"""
		query_insert = """
			INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
			VALUES (%s, %s, %s, NOW())
			ON DUPLICATE KEY UPDATE
			expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
			expedice_id = VALUES(expedice_id),
			hodnota = VALUES(hodnota),
			aktualizovano = VALUES(aktualizovano)
			"""
		rozdeleni_hodin = {}
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id in exp2name_:
			query_execute(cursor, query, (exp_id,))
			data = cursor.fetchall()
			hodiny_list = [0 for _ in range(24)]
			for x in data:
				for h in range(x[0], x[1]):
					hodiny_list[h + 1] += 1
			hodiny_list = [0] * 7 + hodiny_list[7:18] + [0] * 5
			hodiny_list = [x / max(sum(hodiny_list), 1) for x in hodiny_list]
			rozdeleni_hodin[exp_id] = hodiny_list
		db.close()
		db2, cursor2 = get_db_cursor(DatabaseAliasLive)
		for h in range(12):
			for exp_id in exp2name_:
				query_execute(cursor2, query_insert, (h + 55, exp_id, rozdeleni_hodin[exp_id][h + 7]))
		db2.commit()
		db2.close()

		return rozdeleni_hodin
	if args_.frequency == hodina_arg:
		query = """
			SELECT hodina, hodnota
			FROM
			(   SELECT expedice_prehled_data_nazev_id AS hodina,
					hodnota,
					RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id BETWEEN 55 AND 66
				AND expedice_id = %s
				ORDER BY expedice_prehled_data_nazev_id ASC
			) aa
			WHERE poradi = 1
		"""
		rozdeleni_prace_hodiny_ = {}
		for exp_id in exp2name_:
			result = query_db(query, DatabaseAliasLive, (exp_id,))
			rozdeleni_prace_hodiny_[exp_id] = [0] * 7 + [float(x[1]) for x in result] + [0] * 5
		return rozdeleni_prace_hodiny_
	return None


def oriznout_ranni(rozdeleni_prace_hodiny_):
	if args_.frequency == hodina_arg:
		"""
		Upravuje rozdělení odpracovaných hodin, pokud se jedná pouze o ranní směnu bez odpolední
		"""
		result = [0] * 7
		result += rozdeleni_prace_hodiny_[7:15]
		result += [0] * 9
		result = [x / max(float(sum(result)), 0.1) for x in result]
		return result
	return None


def get_prace_out(hodiny_odpracovane_, rozdeleni_prace_, rozdeleni_prace_ranni, aktivni, exp2name_):
	if args_.frequency == hodina_arg:
		"""
		Na vstupu bere typy otevřených směn, obyvklé rozdělení práce mezi hodiny a aktuální počty zapsaných hodin na celé období
		Vrací odhad odpracovaných člověkohodin každou hodinu
		"""
		prace = {}
		for exp_id in sorted(exp2name_.keys()):
			ho = hodiny_odpracovane_[exp_id]
			rp = rozdeleni_prace_[exp_id]
			rpr = rozdeleni_prace_ranni[exp_id]
			akt = aktivni[exp_id]
			prace_exp = [ho[h // 24] * rp[h % 24] if akt[h // 24][1] == 1 else ho[h // 24] * rpr[h % 24] for h in
						 range(len(ho) * 24)]
			prace[exp_id] = prace_exp[dt.datetime.now().hour:]
		return prace
	return None


def get_odpracovano_dnes(hodiny_odpracovane_dnes_zapsano, rozdeleni_prace_, rozdeleni_prace_ranni, aktivni, exp2name_):
	if args_.frequency == hodina_arg:
		"""
		Na vstupu bere typy otevřených směn, obyvklé rozdělení práce mezi hodiny a dnešní počty zapsaných hodin
		Vrací odhad dnes odpracovaných hodin
		"""
		hodina_ted = dt.datetime.now().hour
		odpracovano_dnes_ = {}
		for exp_id in sorted(exp2name_.keys()):
			rp = rozdeleni_prace_[exp_id]
			rpr = rozdeleni_prace_ranni[exp_id]
			akt = aktivni[exp_id]
			odpracovano_dnes_exp = hodiny_odpracovane_dnes_zapsano[exp_id] * sum(
				[rp[h] if akt[1][1] == 1 else rpr[h] for h in range(hodina_ted)])
			odpracovano_dnes_[exp_id] = odpracovano_dnes_exp
		return odpracovano_dnes_
	return None


def get_prace_out_optimal_dny(prace_out_optimal_, exp2name_):
	if args_.frequency == hodina_arg:
		"""
		Na vstupu bere optimální počty odpacovaných člověkohodin po hodinách
		Výstupem jsou optimální počty po dnech
		"""
		prace_out_optimal_dny_ = {}
		for exp_id in exp2name_:
			prace_out_optimal_dny_[exp_id] = [sum(prace_out_optimal_[exp_id][max(j, 0):j + 24]) for j in
											  range(0, len(prace_out_optimal_[exp_id]), 24)]
		return prace_out_optimal_dny_
	return None


def adjust_values_to_zero(lst, zaps, maximum_work):
	adjusted = lst[:]
	for _ in range(10):
		positive_indices = [i for i, x in enumerate(adjusted) if x > 0]
		negative_indices = [i for i, x in enumerate(adjusted) if x < 0]
		maximum_indices = [i for i, x in enumerate(adjusted) if x + zaps[i] > maximum_work]
		for mi in maximum_indices:
			excess = adjusted[mi] + zaps[mi] - maximum_work
			adjusted[mi] = maximum_work - zaps[mi]
			transfer = excess / (len(positive_indices) + len(negative_indices))
			for pi in positive_indices:
				adjusted[pi] += transfer
			for ni in negative_indices:
				adjusted[ni] += transfer
		if not negative_indices:
			break
		for ni in negative_indices:
			smaller_positive = [i for i in positive_indices if i < ni]
			if not smaller_positive:
				continue
			smaller_positive += [ni]
			transfer = adjusted[ni] / len(smaller_positive)
			for pi in smaller_positive:
				adjusted[pi] += transfer
				adjusted[ni] = 0
	return adjusted

def automaticky_plan_exp_2(zapsane_hodiny, optimalni_hodiny, smoothing_coefficient, max_limit):
	plan = []
	fronty = []
	fronta = optimalni_hodiny[0] - zapsane_hodiny[0]
	for e, (z, o) in enumerate(zip(zapsane_hodiny, optimalni_hodiny)):
		if e == 0:
			plan.append(z)
			fronty.append(fronta)
			continue
		new_z = zapsane_hodiny[e]
		new_o = optimalni_hodiny[e]
		dohnat_dnes = fronta * smoothing_coefficient
		fronta += new_o - new_z
		if z == 0:
			plan.append(0)
			continue
		fronta -= dohnat_dnes
		fronta += new_z - new_o
		fronty.append(int(fronta))
		plan.append(int(new_o + dohnat_dnes))
	chybejici = [p - z for p, z in zip(plan, zapsane_hodiny)]
	chybejici_nove = adjust_values_to_zero(chybejici, zapsane_hodiny, max_limit)
	plan = [z + int(c) for z, c in zip(zapsane_hodiny, chybejici_nove)]
	return plan

def automaticky_plan_exp(zapsane_hodiny, optimalni_hodiny, smoothing_coefficient, max_daily_work):
	# Step 1: Remove the first day's values
	prvni_den_zapsane = zapsane_hodiny[0]
	prvni_den_optimalni = optimalni_hodiny[0]
	pracovnich_dnu = len([x for x in zapsane_hodiny if x > 0])
	max_hodiny = [max_daily_work if z else 0 for z in zapsane_hodiny]
	min_rezerva = 0
	# Initialize variables
	n = len(optimalni_hodiny)
	planned_work_rest = [0] * n
	current_work = prvni_den_optimalni - prvni_den_zapsane  # Tracks leftover work carried over to future days

	import pulp
	# Optimalizační model
	model = pulp.LpProblem("Optimalizace_smen", pulp.LpMinimize)

	workers = [pulp.LpVariable(f"worker_{den}", lowBound=0, upBound=max_hodiny[den], cat="Integer") for den in range(n)]

	model += workers[0] == zapsane_hodiny[0]

	# proměnné pro zachycení absolutních hodnot změn
	rozdil_plus = [pulp.LpVariable(f"rozdil_plus_{den}", lowBound=0) for den in range(n)]
	rozdil_minus = [pulp.LpVariable(f"rozdil_minus_{den}", lowBound=0) for den in range(n)]

	# definice absolutních hodnot
	for den in range(n):
		model += workers[den] - zapsane_hodiny[den] == rozdil_plus[den] - rozdil_minus[den]

	zmeny_smen = pulp.lpSum(rozdil_plus + rozdil_minus)

	# Výpočet fronty
	fronta = [current_work]
	for den in range(n):
		odpracovano = workers[den]
		prichod_nove_prace = optimalni_hodiny[den]
		fronta.append(fronta[-1] + prichod_nove_prace - odpracovano)

	# Penalizace za nízkou rezervu práce
	penalty_vars = [pulp.LpVariable(f"penalty_rezerva_{den}", lowBound=0) for den in range(1, n + 1)]
	for den in range(1, n + 1):
		model += fronta[den] >= min_rezerva - penalty_vars[den - 1]


	# Použij stejný trik i pro odchylka_od_optimalu:
	optimal_plus = [pulp.LpVariable(f"optimal_plus_{den}", lowBound=0) for den in range(n)]
	optimal_minus = [pulp.LpVariable(f"optimal_minus_{den}", lowBound=0) for den in range(n)]
	for den in range(n):
		model += workers[den] - optimalni_hodiny[den] == optimal_plus[den] - optimal_minus[den]

	odchylka_od_optimalu = pulp.lpSum([optimal_plus[den] + optimal_minus[den] for den in range(n)])

	# Co minimalizujeme:
	final_queue = fronta[-1]
	model += (
			(10 * pulp.lpSum(fronta))
			+ (5 * zmeny_smen)
			+ (3 * odchylka_od_optimalu)
			+ (50 * pulp.lpSum(penalty_vars))
	)

	model.solve(pulp.PULP_CBC_CMD(msg=False))
	planned_work = [x.varValue for x in workers]

	# Step 5: Ensure non-negative planned work
	planned_work = [
		max(work, 0) for work in planned_work
	]
	zmeny = [x - y for x, y in zip(planned_work, zapsane_hodiny)]
	zapsane_hodiny_np = np.array(zapsane_hodiny)
	alpha = smoothing_coefficient
	vyhlazene_zmeny = np.zeros(n)
	for i in range(n):
		if zapsane_hodiny_np[i] == 0:
			vyhlazene_zmeny[i] = 0  # Zachovat nulovou hodnotu beze změny
		else:
			if i == 0 or zapsane_hodiny_np[i - 1] == 0:
				# Pokud je první hodnota nebo předchozí je nula, vezmi aktuální změnu přímo
				vyhlazene_zmeny[i] = zmeny[i]
			else:
				# Jinak vyhlaď vůči předchozí vyhlazené hodnotě
				vyhlazene_zmeny[i] = alpha * zmeny[i] + (1 - alpha) * vyhlazene_zmeny[i - 1]

	# Přičtení vyhlazených změn k původně zapsaným směnám
	workers_final = zapsane_hodiny_np + vyhlazene_zmeny

	# Zachování nulových dní beze změny
	workers_final[zapsane_hodiny_np == 0] = 0

	# Normalizace plánovaných na optimální
	workers_final *= sum(optimalni_hodiny) / max(sum(workers_final), 1)

	# Zaokrouhlení na celé pracovníky
	workers_final = np.round(workers_final).astype(int)
	workers_final[0] = zapsane_hodiny[0]  # První den musí být stejný jako zapsané hodiny

	return workers_final


def automaticky_plan(zapsano_, optimum_, limity_):
	"""
	Na vstupu vezme naplánované a optimální hodnoty
	Na základě limitů expedic určí, co je třeba dělat pro dosažení požadovaných front
	"""
	plan = np.empty(optimum_.shape)
	if args_.frequency == hodina_arg:
		# for exp in range(len(zapsano_)):
		for exp in range(len(zapsano_)):
			zapsano = [sum(y) for y in zapsano_[exp]]
			optimum = optimum_[exp]
			limit = sum(limity_[exp][0])
			plan[exp] = automaticky_plan_exp(zapsano, optimum, smooth_coef, limit)
		return plan
	return None


def automaticky_plan_2(zapsano_, optimum_, limity_):
	"""
	Na vstupu vezme naplánované a optimální hodnoty
	Na základě limitů expedic určí, co je třeba dělat pro dosažení požadovaných front
	"""
	plan = np.empty(optimum_.shape)
	if args_.frequency == hodina_arg:
		# for exp in range(len(zapsano_)):
		for exp in range(len(zapsano_)):
			zapsano = [sum(y) for y in zapsano_[exp]]
			optimum = optimum_[exp]
			limit = sum(limity_[exp][0])
			plan[exp] = automaticky_plan_exp_2(zapsano, optimum, smooth_coef, limit)
		return plan
	return None


def get_limity_smeny(exp2name_):
	# READ EPD14, READ EPD15, READ EPD16
	result = {exp_id: [1, 1] for exp_id in exp2name_.keys()}, {exp_id: 1 for exp_id in exp2name_.keys()}
	if args_.frequency == hodina_arg:
		query = """
		SELECT expedice_id, ranni_limit, odpoledni_limit, technicke_minimum
		FROM
		(   SELECT expedice_id, ranni_limit
			FROM
			(   SELECT expedice_id, hodnota ranni_limit, RANK() OVER (PARTITION BY expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = 14
			) AS ranked1_0
			WHERE poradi = 1
		) AS ranked1
		JOIN
		(   SELECT expedice_id, odpoledni_limit
			FROM
			(   SELECT expedice_id, hodnota odpoledni_limit, RANK() OVER (PARTITION BY expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = 15
			) AS ranked2_0
			WHERE poradi = 1
		) AS ranked2
		USING (expedice_id)
		JOIN
		(   SELECT expedice_id, technicke_minimum
			FROM
			(   SELECT expedice_id, hodnota technicke_minimum, RANK() OVER (PARTITION BY expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = 16
			) AS ranked3_0
			WHERE poradi = 1
		) AS ranked3
		USING (expedice_id)
		"""
		data = query_db(query, DatabaseAliasLive)
		limity = {exp_id: [1, 1] for exp_id in exp2name_.keys()}
		minima = {exp_id: 1 for exp_id in exp2name_.keys()}
		limity.update({x[0]: [int(x[1]), int(x[2])] for x in data})
		minima.update({x[0]: int(x[3]) for x in data})
		return limity, minima
	return result


def insert_zmeny():
	# WRITE EP10 , WRITE EP13 , WRITE EP16 , WRITE EP19 , WRITE EP22 , WRITE EP25
	# READ EPD9 , READ EPD10 , READ EPD11 , READ EPD12 , READ EPD13 , READ EPD172
	if args_.frequency == den_arg:
		query_get = """
		SELECT expedice_id, hodnota_ted - hodnota_minule
		FROM
		(   SELECT expedice_id, hodnota_ted
			FROM
			(   SELECT expedice_id, hodnota hodnota_ted, RANK() OVER (PARTITION BY expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = %s
			) AS ranked1
			WHERE poradi = 1
		) AS ranked1_2
		JOIN
		(   SELECT expedice_id, hodnota_minule
			FROM
			(   SELECT expedice_id, hodnota hodnota_minule, RANK() OVER (PARTITION BY expedice_id ORDER BY aktualizovano DESC) AS poradi
				FROM expedice_prehled_data
				WHERE expedice_prehled_data_nazev_id = %s
				AND aktualizovano < NOW() - INTERVAL 7 DAY
			) AS ranked2
			WHERE poradi = 1
		) AS ranked2_2
		USING (expedice_id)
		"""
		query_insert = """
		INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (%s, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for read_id, write_id in [(9, 10), (10, 13), (11, 16), (12, 19), (13, 22), (172, 25)]:
			query_execute(cursor, query_get, (read_id, read_id))
			data = cursor.fetchall()
			for x in data:
				query_execute(cursor, query_insert, (write_id, x[0], x[1]))
		db.commit()
		db.close()


def vypocti_prisun_nekomplet_kusu_naskladneni(preliti_naskladneni_, exp2name_):
	if args_.frequency == hodina_arg:
		return {exp_id: [0 if j % 24 < 7 or j % 24 > 12 else preliti_naskladneni_[exp_id][j // 24] / 6 for j in
						 range(len(preliti_naskladneni_[exp_id]) * 24)] for exp_id in exp2name_.keys()}
	return None


def vypocti_prisun_ms_kusu_naskladneni(exp2name_):
	if args_.frequency == hodina_arg:
		DatabaseAliasMathLive = 'db-cme-knihy-math-live'
		query = f"""
			SELECT warehouse_id, SUM(cost) / %s
				FROM
				(   SELECT warehouse_id, cost, RANK() OVER (PARTITION BY warehouse_id, local ORDER BY calculation_id DESC) AS poradi
					FROM so_calculation_limits
				) AS ranked
			WHERE poradi = 1
			GROUP BY warehouse_id
			"""
		data = query_db(query, DatabaseAliasMathLive, (prumerna_cena_kusu_na_minimalni_sklad,))
		prisun_ms_kusu = {x[0]: x[1] for x in data}
		prisun_ms_kusu = {exp_id: [0 if j % 24 < 7 or j % 24 > 12 else prisun_ms_kusu[exp_id] / 6
								   for j in range(24 * dnu_simulace)] for exp_id in exp2name_.keys()}
		return prisun_ms_kusu
	return None


def vypocti_prisun_kusu_naskladneni(prisun_nekomplet_kusu_naskladneni_, prisun_ms_kusu_naskladneni_, exp2name_, ):
	if args_.frequency == hodina_arg:
		return {
			exp_id: [x + y for x, y in
					 zip(prisun_nekomplet_kusu_naskladneni_[exp_id], prisun_ms_kusu_naskladneni_[exp_id])]
			for exp_id in exp2name_.keys()}
	return None


def vypocti_prisun_kusu_naskladneni_legacy(prisun_nekomplet_kusu_naskladneni_, naskladnovaci_rozdeleni_, exp2name_):
	if args_.frequency == hodina_arg:
		return {
			exp_id: [x / (1 - naskladnovaci_rozdeleni_[exp_id][3]) for x in
					 prisun_nekomplet_kusu_naskladneni_[exp_id]]
			for exp_id in exp2name_.keys()}
	return None


def vypocti_prisun_naskladneni_cas(preliti_naskladneni_, exp2name_, rychlosti_pozic_, naskladnovaci_rozdeleni_,
								   velikost_nekompletni_objednavky_):
	if args_.frequency == hodina_arg:
		prisun_nekomplet_kusu_naskladneni = vypocti_prisun_nekomplet_kusu_naskladneni(preliti_naskladneni_, exp2name_)
		prisun_ms_kusu_naskladneni = vypocti_prisun_ms_kusu_naskladneni(exp2name_)
		prisun_kusu_naskladneni = vypocti_prisun_kusu_naskladneni(prisun_nekomplet_kusu_naskladneni,
																  prisun_ms_kusu_naskladneni,
																  exp2name_)
		prisun_kusu_naskladneni_legacy = vypocti_prisun_kusu_naskladneni_legacy(prisun_nekomplet_kusu_naskladneni,
																				naskladnovaci_rozdeleni_, exp2name_)
		if legacy_prisun_naskladneni:
			prisun_kusu_naskladneni = prisun_kusu_naskladneni_legacy
		prisun_naskladneni_cas_ = {exp_id: [] for exp_id in exp2name_.keys()}
		for h in range(len(prisun_kusu_naskladneni[list(exp2name_.keys())[0]])):
			pnk = vypocti_frontu_naskladneni_cas(exp2name_,
												 {exp_id: prisun_kusu_naskladneni[exp_id][h] for exp_id in
												  exp2name_.keys()},
												 rychlosti_pozic_, naskladnovaci_rozdeleni_,
												 velikost_nekompletni_objednavky_)
			for exp_id in exp2name_.keys():
				prisun_naskladneni_cas_[exp_id].append(pnk[exp_id])
		prisun_naskladneni_cas_ = {
			exp_id: [sum(prisun_naskladneni_cas_[exp_id][h]) for h in range(len(prisun_naskladneni_cas_[exp_id]))] for
			exp_id
			in exp2name_.keys()}
		return prisun_naskladneni_cas_
	return None


def vypocti_prisun_vyhledani_cas(prisun_objednavek_, exp2name_, rychlosti_pozic_, velikost_kompletni_objednavky_, pocet_polozek_kompletni_objednavky_,
								 intercept_, slope_1_, slope_2_):
	if args_.frequency == hodina_arg:
		prisun_komplet_objednavek = {exp_id: [x[0] for x in prisun_objednavek_[exp_id]] for exp_id in exp2name_.keys()}
		prisun_komplet_kusu_vyhledani = {
			exp_id: [x * velikost_kompletni_objednavky_[exp_id] for x in prisun_komplet_objednavek[exp_id]] for exp_id
			in exp2name_.keys()}
		prisun_komplet_polozek_vyhledani = {
			exp_id: [x * pocet_polozek_kompletni_objednavky_[exp_id] for x in prisun_komplet_objednavek[exp_id]] for exp_id
			in exp2name_.keys()}

		prisun_vyhledani_cas_ = {exp: [] for exp in exp2name_.keys()}
		for i in range(len(prisun_komplet_kusu_vyhledani[list(exp2name_.keys())[0]])):
			pvk = vypocti_frontu_vyhledani_baleni_cas(exp2name_,
													  {exp_id: prisun_komplet_kusu_vyhledani[exp_id][i] for exp_id in
														exp2name_.keys()},
													  {exp_id: prisun_komplet_objednavek[exp_id][i] for exp_id in
														exp2name_.keys()},
													  {exp_id: prisun_komplet_polozek_vyhledani[exp_id][i] for exp_id in
														exp2name_.keys()},
													  {exp_id: 0 for exp_id in exp2name_.keys()},
													  {exp_id: 0 for exp_id in exp2name_.keys()},
													  {exp_id: 0 for exp_id in exp2name_.keys()},
													  rychlosti_pozic_, intercept_, slope_1_, slope_2_)
			for exp_id in exp2name_.keys():
				prisun_vyhledani_cas_[exp_id].append(pvk[exp_id])

		prisun_vyhledani_cas_ = {
			exp_id: [sum(prisun_vyhledani_cas_[exp_id][i]) for i in range(len(prisun_vyhledani_cas_[exp_id]))] for
			exp_id in exp2name_.keys()}
		return prisun_vyhledani_cas_
	return None


def plot_total(fronta, dnu_simu, show=True, typ=0):
	"""
	Vytvoří obrázky pro .xlsx soubor
	"""
	x = date_range(dt.datetime.now(), periods=24 * dnu_simu - 1, freq='h')
	y = fronta
	original_width, original_height = plt.rcParams['figure.figsize']
	plt.figure(figsize=(original_width * 1.5, original_height))
	plt.plot(x, y)
	plt.xlabel("den")
	plt.title(f"Celková fronta práce [h]")
	plt.ylim((min(min(fronta) * 1.1, 0), max(fronta) * 1.1))
	# label x as days of week [po, ut, st, ct, pa, so, ne], only one for each day
	tick_positions = [x[i] for i in range(0, len(x), 24)]
	tick_labels = [day_dict[(dt.date.today().weekday() + i) % 7] for i in range(len(tick_positions))]

	plt.xticks(tick_positions, tick_labels)
	if show:
		plt.show()
	# save plot as png
	plt.savefig(f"data/nova_{typ}.png")
	plt.close()
	return


def write_excel(length, planned: np.ndarray, optimal: np.ndarray, suggested: np.ndarray):
	"""
	Zapíše výsledek do .xlsx souboru
	"""
	exps_pocet = planned.shape[0]

	exp_dict_excel = {0: "liberec", 1: "lípa", 2: "jablonec", 3: "parfémy", 4: "ostrava", 5: "celkem"}
	workbook = xlsxwriter.Workbook("hodiny_nova_8.xlsx")
	worksheet = workbook.add_worksheet()
	day_index = dt.datetime.today().weekday()

	# Výpočty
	celkem_zapsano = planned.sum(axis=0)
	celkem_optimum = optimal.sum(axis=0)
	celkem_navrh = suggested.sum(axis=0)

	# Formát
	cell_format = workbook.add_format()
	cell_format.set_align("center")
	cell_format.set_align("vcenter")
	cell_format.set_font_name("Arial")

	# Merge
	worksheet.merge_range(0, 1, 0, length, "ZAPSÁNO TEĎ", cell_format)
	worksheet.merge_range(4 + exps_pocet, 1, 4 + exps_pocet, length, "OPTIMÁLNÍ", cell_format)
	worksheet.merge_range(7 + 2 * exps_pocet, 1, 7 + 2 * exps_pocet, length, "NAVRHOVANÉ", cell_format)
	worksheet.merge_range(10 + 3 * exps_pocet, 1, 10 + 3 * exps_pocet, length, "CHYBÍ", cell_format)
	worksheet.merge_range(16, length + 1, 17, length + 4, "Hodinový dluh:", cell_format)
	worksheet.merge_range(16, length + 5, 17, length + 6, str(int(celkem_optimum[0] - celkem_zapsano[0])), cell_format)

	# Texty
	for i in range(length):
		worksheet.write(1, i + 1, (dt.datetime.today().date() + dt.timedelta(days=i)).strftime("%d.%m."), cell_format)
		worksheet.write(2, i + 1, day_dict[(day_index + i) % 7], cell_format)
		worksheet.write(5 + exps_pocet, i + 1, day_dict[(day_index + i) % 7], cell_format)
		worksheet.write(8 + 2 * exps_pocet, i + 1, day_dict[(day_index + i) % 7], cell_format)
		worksheet.write(11 + 3 * exps_pocet, i + 1, day_dict[(day_index + i) % 7], cell_format)
	for j in range(exps_pocet + 1):
		for i in range(4):
			worksheet.write(3 + j + (exps_pocet + 3) * i, 0, exp_dict_excel[j], cell_format)
		if j != exps_pocet:
			worksheet.write(16, length + 7 + j, exp_dict_excel[j], cell_format)

	# Hodnoty
	for j in range(exps_pocet):
		for i in range(length):
			worksheet.write(3 + j, i + 1, planned[j, i], cell_format)
			worksheet.write(6 + exps_pocet + j, i + 1, optimal[j, i], cell_format)
			worksheet.write(9 + 2 * exps_pocet + j, i + 1, suggested[j, i], cell_format)
		worksheet.write(17, length + 7 + j, str(int(optimal[j, 0] - planned[j, 0])), cell_format)

	for letter in "BCDEFGHIJKLMNOPQRST"[:length]:
		for row in range(13 + 3 * exps_pocet, 13 + 4 * exps_pocet):
			worksheet.write_formula(f"{letter}{row}", f"={letter}{row - 8}-{letter}{row - 24}", cell_format)

	# Součty
	for i in range(length):
		worksheet.write(3 + exps_pocet, i + 1, celkem_zapsano[i], cell_format)
		worksheet.write(6 + 2 * exps_pocet, i + 1, celkem_optimum[i], cell_format)
		worksheet.write(9 + 3 * exps_pocet, i + 1, celkem_navrh[i], cell_format)

	# Obrázky
	worksheet.insert_image(0, length + 1, "data/nova_0.png", {"x_scale": 1, "y_scale": 0.65})
	worksheet.insert_image(20, length + 1, "data/nova_1.png", {"x_scale": 1, "y_scale": 0.7})

	workbook.close()


def get_podil_storno(exp2name_):
	# WRITE EP24 , WRITE EP26
	# WRITE EPD171
	# READ EPD171
	if args_.frequency == den_arg:
		query = """
		SELECT expedice_id, storno / celkem
		FROM
		(
		  SELECT expedice_id, count(*) storno
		  FROM objednavka
		  WHERE obj_stav_id = 10
		  AND objednavka_datetime BETWEEN CURRENT_DATE - INTERVAL %s DAY AND CURRENT_DATE - INTERVAL %s DAY
		  GROUP BY expedice_id
		) AS storna
		JOIN
		(
		  SELECT expedice_id, count(*) celkem
		  FROM objednavka
		  WHERE objednavka_datetime BETWEEN CURRENT_DATE - INTERVAL %s DAY AND CURRENT_DATE - INTERVAL %s DAY
		  GROUP BY expedice_id
		) AS celka
		USING (expedice_id)
		"""
		data = query_db(query, DatabaseAliasLive, (1, 0, 1, 0))
		insert_query = """
		INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (%s, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		insert_query_data = """
		INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (%s, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for x in data:
			query_execute(cursor, insert_query, (26, x[0], x[1]))
			query_execute(cursor, insert_query_data, (172, x[0], x[1]))
		data = query_db(query, DatabaseAliasLive, (35, 28, 35, 28))
		for x in data:
			query_execute(cursor, insert_query, (24, x[0], x[1]))
			query_execute(cursor, insert_query_data, (171, x[0], x[1]))
		db.commit()
		db.close()
	if args_.frequency == hodina_arg:
		query = """
		SELECT expedice_id, hodnota FROM
		(
			SELECT expedice_id, hodnota, RANK() OVER (PARTITION BY expedice_id ORDER BY aktualizovano DESC) AS poradi
			FROM expedice_prehled_data
			WHERE expedice_prehled_data_nazev_id = %s
		) AS ranked
		WHERE poradi = 1
		"""
		data = query_db(query, DatabaseAliasLive, (171,))
		storno = {exp_id: 0 for exp_id in exp2name_.keys()}
		storno.update({x[0]: x[1] for x in data})
		return storno
	return None


def get_zapsane_hodiny(dni_zpet: int, exp2name_: dict):
	if args_.frequency == hodina_arg:
		ConCME, CurCME = get_db_cursor(DatabaseAliasLive)
		smeny = {exp: 0 for exp in exp2name_}
		for exp_id in sorted(exp2name_.keys()):
			query = f"""SELECT IFNULL(SUM((planovac_brigadnik_do - planovac_brigadnik_od) DIV 10000), 0)
								FROM planovac_brigadnik plan
								JOIN planovac_smena USING (planovac_smena_id)
								JOIN planovac_den USING (planovac_den_id)
								JOIN uzivatel_mzda_nastaveni USING (uzivatel_id)
								JOIN planovac_obdobi USING (planovac_obdobi_id)
								WHERE planovac_den_datum BETWEEN CURRENT_DATE - INTERVAL %s DAY
								AND CURRENT_DATE - INTERVAL 1 DAY
								AND planovac_brigadnik_smazano = 0
								AND uzivatel_typ_id != 2
								AND expedice_id = %s"""
			query_execute(CurCME, query, (dni_zpet, exp_id))
			data = CurCME.fetchall()
			smeny[exp_id] += float(data[0][0])
		ConCME.close()
		return smeny
	return None


def get_hpp_hodiny_merene(exp2name_):
	# WRITE EPD48 , WRITE EPD49 , WRITE EPD50 , WRITE EPD51 , WRITE EPD52 , WRITE EPD53 , WRITE EPD54
	# READ EPD48 , READ EPD49 , READ EPD50 , READ EPD51 , READ EPD52 , READ EPD53 , READ EPD54
	if args_.frequency == tyden_arg:
		query = """
		SELECT DAYOFWEEK(den), AVG(merene_prace)
		FROM
		(
		  SELECT den, sum(odpracovano) as merene_prace
		  FROM
		  (
			SELECT uzivatel_id, SUM(odpracovany_cas_orezany) / 3600 odpracovano, den
			FROM merena_prace_den
			JOIN `knihy-cme`.uzivatel_mzda_nastaveni USING (uzivatel_id)
			WHERE den BETWEEN CURRENT_DATE - INTERVAL 28 DAY AND CURRENT_DATE
			AND merena_prace_typ_id < 5
			AND uzivatel_typ_id = 2
			GROUP BY uzivatel_id, den
		  ) bb
		  GROUP BY den
		) cc
		GROUP BY DAYOFWEEK(den)
		"""
		data = {}
		for exp_id, exp_name in exp2name_.items():
			databaseAlias = get_db_alias_exp(exp_name)
			result = query_db(query, databaseAlias)
			data.update({exp_id: {x[0]: x[1] for x in result}})
		insert_query = """
		INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (%s, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id, data_ in data.items():
			for day, hpp in data_.items():
				query_execute(cursor, insert_query, (47 + day, exp_id, hpp))
		db.commit()
		db.close()
		return data
	if args_.frequency == hodina_arg:
		data = {exp: {k: 0 for k in range(1, 8)} for exp in exp2name_.keys()}
		query = """
		SELECT expedice_prehled_data_nazev_id - 47, expedice_id, hodnota FROM
		(
			SELECT expedice_prehled_data_nazev_id, expedice_id, hodnota, RANK() OVER (PARTITION BY expedice_prehled_data_nazev_id, expedice_id ORDER BY aktualizovano DESC) AS poradi
			FROM expedice_prehled_data
			WHERE expedice_prehled_data_nazev_id BETWEEN 48 AND 54
		) AS ranked
		WHERE poradi = 1
		"""
		data_hpp = query_db(query, DatabaseAliasLive)
		for den, exp, hpp in data_hpp:
			if exp in data:
				data[exp][den] = hpp
		return data
	return None


def odecti_praci_hpp(prace, hpp, aktivni_smeny_):
	if args_.frequency == hodina_arg:
		day = dt.datetime.today().weekday()
		prace_new = {exp_id: [x for x in data] for exp_id, data in prace.items()}
		for exp_id in prace.keys():
			for h in range(len(prace[exp_id])):
				nasobek = 2 if 7 <= h % 24 < 19 else 0

				prace_new[exp_id][h] -= hpp[exp_id][(day + h // 24 + 1) % 7 + 1] / 24 * nasobek * \
										(aktivni_smeny_[exp_id][h // 24][0] * 2 / 3 +
										 aktivni_smeny_[exp_id][h // 24][1] * 1 / 3)
		return prace_new
	return None


def get_prace_optimalni_dny(prace_in_real_odhad_, odpracovano_real_dnes):
	for exp_id in prace_in_real_odhad_.keys():
		prace_in_real_odhad_[exp_id][0] += odpracovano_real_dnes[exp_id]
	return prace_in_real_odhad_


def get_graph_data_exp(prace_aktualni, prace_in_, prace_out_den, odpoledni_aktivni, ranni_rozdeleni,
					   odpoledni_rozdeleni, spolehlivost_hist_, realne=True):
	result = []
	if not realne:
		prace_out_den = [x * spolehlivost_hist_ for x in prace_out_den]
	for i in range(len(odpoledni_aktivni)):
		delka = 24
		rozdeleni = ranni_rozdeleni if odpoledni_aktivni[i][1] == 0 else odpoledni_rozdeleni
		rozdeleni = rozdeleni[-delka:]
		prace_in_dnes = prace_in_[max(24 * i - 1, 0):24 * (i + 1) - 1]
		prace_out_dnes = [prace_out_den[i] * x for x in rozdeleni][-delka:]
		result_dnes = [x - y for x, y in zip(prace_in_dnes, prace_out_dnes)]
		if i == 0:
			result_dnes = [int(prace_aktualni + sum(result_dnes[:k + 1])) for k in range(len(result_dnes))]
		else:
			result_dnes = [int(result[-1] + sum(result_dnes[:k + 1])) for k in range(len(result_dnes))]
		result.extend(result_dnes)
	return result


def get_graph_data(prace_aktualni, prace_in_den, prace_out_den, odpoledni_aktivni, ranni_rozdeleni,
				   odpoledni_rozdeleni, spolehlivost_hist_, realne=True):
	exp_dict = {exp_id: get_graph_data_exp(prace_aktualni[exp_id], prace_in_den[exp_id], prace_out_den[exp_id],
										   odpoledni_aktivni[exp_id], ranni_rozdeleni[exp_id],
										   odpoledni_rozdeleni[exp_id], spolehlivost_hist_[exp_id], realne) for exp_id
				in prace_aktualni.keys()}
	if "celkem" not in exp_dict:
		value_len = len(exp_dict[list(exp_dict.keys())[0]])
		exp_dict["celkem"] = [sum([exp_dict[exp_id][i] for exp_id in exp_dict.keys() if exp_id != "celkem"]) for i in
							  range(value_len)]
	return exp_dict


def write_spolehlivost(spolehlivost_, dni):
	# WRITE EP18 , WRITE EP20
	# WRITE EPD12, WRITE EPD176
	if args_.frequency == hodina_arg:
		insert_query = """
		INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (%s, %s, %s, NOW())
		ON DUPLICATE KEY UPDATE
		expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		insert_data_query = """
		INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
		VALUES (%s, %s, %s, NOW() - INTERVAL 1 DAY)
		ON DUPLICATE KEY UPDATE
		expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
		expedice_id = VALUES(expedice_id),
		hodnota = VALUES(hodnota),
		aktualizovano = VALUES(aktualizovano)
		"""
		db, cursor = get_db_cursor(DatabaseAliasLive)
		for exp_id, hodnota in spolehlivost_.items():
			if dni == 1:
				query_execute(cursor, insert_query, (20, exp_id, hodnota))
				query_execute(cursor, insert_data_query, (176, exp_id, hodnota))
			else:
				query_execute(cursor, insert_query, (18, exp_id, hodnota))
				query_execute(cursor, insert_data_query, (12, exp_id, hodnota))
		db.commit()
		db.close()


def write_hodinovy_dluh(hodinovy_dluh_):
	# WRITE EP6 , WRITE EPD8
	insert_query = """
	INSERT INTO expedice_prehled (expedice_prehled_nazev_id, expedice_id, hodnota, aktualizovano)
	VALUES (%s, %s, %s, NOW())
	ON DUPLICATE KEY UPDATE
	expedice_prehled_nazev_id = VALUES(expedice_prehled_nazev_id),
	expedice_id = VALUES(expedice_id),
	hodnota = VALUES(hodnota),
	aktualizovano = VALUES(aktualizovano)
	"""

	insert_data_query = """
	INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
	VALUES (%s, %s, %s, NOW())
	ON DUPLICATE KEY UPDATE
	expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
	expedice_id = VALUES(expedice_id),
	hodnota = VALUES(hodnota),
	aktualizovano = VALUES(aktualizovano)
	"""
	db, cursor = get_db_cursor(DatabaseAliasLive)
	for exp_id, hodnota in hodinovy_dluh_.items():
		query_execute(cursor, insert_query, (6, exp_id, float(hodnota)))
		query_execute(cursor, insert_data_query, (8, exp_id, float(hodnota)))
	db.commit()
	db.close()

def expedicni_skore_efektivity_dnes():
	query = """
	SELECT
		YEAR(aktualizovano) AS rok,
		MONTH(aktualizovano) AS měsíc,
		1000 - ROUND(AVG(skore)) AS vzdalenost_od_optima
	FROM (
		SELECT aktualizovano,
			AVG(CASE
					WHEN hodnota >= 0 THEN hodnota
					ELSE -2 * hodnota
				END) AS skore
		FROM expedice_prehled_data
		WHERE expedice_prehled_data_nazev_id = 8
			AND aktualizovano > '2025-02-06'
		GROUP BY aktualizovano
	) AS a
	GROUP BY YEAR(aktualizovano), MONTH(aktualizovano);
	"""
	results = query_db(query, DatabaseAliasLive)
	return results


def expedicni_skore_dnes_1_den():
	query = """
	SELECT
		expedice_id,
		1000 - ROUND(skore) AS vzdalenost_od_optima
	FROM (
		SELECT aktualizovano,
			expedice_id,
			AVG(CASE
					WHEN hodnota >= 0 THEN hodnota
					ELSE -2 * hodnota
				END) AS skore
		FROM expedice_prehled_data
		WHERE expedice_prehled_data_nazev_id = 8
		GROUP BY aktualizovano, expedice_id
	) AS a
	WHERE DATE(aktualizovano) = DATE(CURDATE())
	"""
	result = query_db(query, DatabaseAliasLive)
	if result:
		return result
	else:
		return None

def expedicni_skore_efektivity_vcera():
	query = """
	SELECT
	YEAR(aktualizovano) AS rok,
	MONTH(aktualizovano) AS měsíc,
	1000 - ROUND(AVG(skore)) AS vzdalenost_od_optima
	FROM (
		SELECT aktualizovano,
			AVG(CASE WHEN hodnota >= 0 THEN hodnota ELSE -2 * hodnota END) AS skore
		FROM (
			SELECT datum AS aktualizovano, pocet_hodin_vcera - pocet_hodin_dnes AS hodnota
			FROM (
				SELECT
					DATE(expedice_prehled_plan_zohlednuje_datetime) AS datum,
					expedice_id,
					pocet_hodin AS pocet_hodin_vcera
				FROM (
					SELECT *, ROW_NUMBER() OVER (
						PARTITION BY DATE(expedice_prehled_plan_vytvoreno_datetime),
									 expedice_prehled_plan_zohlednuje_datetime,
									 expedice_prehled_plan_typ,
									 expedice_id
						ORDER BY expedice_prehled_plan_vytvoreno_datetime ASC
					) AS row_num
					FROM expedice_prehled_plan
					WHERE expedice_prehled_plan_typ = 3
					  AND expedice_id != 0) cte
				WHERE row_num = 1
				  AND (
					  (DATEDIFF(expedice_prehled_plan_zohlednuje_datetime, expedice_prehled_plan_vytvoreno_datetime) = 1 AND WEEKDAY(expedice_prehled_plan_vytvoreno_datetime) < 4)
					  OR
					  (DATEDIFF(expedice_prehled_plan_zohlednuje_datetime, expedice_prehled_plan_vytvoreno_datetime) = 3 AND WEEKDAY(expedice_prehled_plan_vytvoreno_datetime) = 4)
				  )
			) vcera
			JOIN (
				SELECT
					DATE(expedice_prehled_plan_zohlednuje_datetime) AS datum,
					expedice_id,
					pocet_hodin AS pocet_hodin_dnes
				FROM (
					SELECT *, ROW_NUMBER() OVER (
						PARTITION BY DATE(expedice_prehled_plan_vytvoreno_datetime),
									 expedice_prehled_plan_zohlednuje_datetime,
									 expedice_prehled_plan_typ,
									 expedice_id
						ORDER BY expedice_prehled_plan_vytvoreno_datetime ASC
					) AS row_num
					FROM expedice_prehled_plan
					WHERE expedice_prehled_plan_typ = 3
					  AND expedice_id != 0) cte
				WHERE row_num = 1
				  AND DATEDIFF(expedice_prehled_plan_zohlednuje_datetime, expedice_prehled_plan_vytvoreno_datetime) = 0
			) dnes USING (datum, expedice_id)
		) aaa
		GROUP BY aktualizovano
	) bbb
	GROUP BY YEAR(aktualizovano), MONTH(aktualizovano);
	"""
	results = query_db(query, DatabaseAliasLive)
	return results

def expedicni_skore_vcera_1_den():
	query = """
	SELECT expedice_id,
	1000 - ROUND(AVG(CASE WHEN hodnota >= 0 THEN hodnota ELSE -2 * hodnota END)) AS vzdalenost_od_optima
FROM (
	SELECT expedice_id, datum AS aktualizovano, pocet_hodin_vcera - pocet_hodin_dnes AS hodnota
	FROM (
		SELECT
			DATE(expedice_prehled_plan_zohlednuje_datetime) AS datum,
			expedice_id,
			pocet_hodin AS pocet_hodin_vcera
		FROM (
			SELECT *, ROW_NUMBER() OVER (
				PARTITION BY DATE(expedice_prehled_plan_vytvoreno_datetime),
							 expedice_prehled_plan_zohlednuje_datetime,
							 expedice_prehled_plan_typ,
							 expedice_id
				ORDER BY expedice_prehled_plan_vytvoreno_datetime ASC
			) AS row_num
			FROM expedice_prehled_plan
			WHERE expedice_prehled_plan_typ = 3
			  AND expedice_id != 0) cte_vcera
		WHERE row_num = 1
		  AND (
			  (DATEDIFF(expedice_prehled_plan_zohlednuje_datetime, expedice_prehled_plan_vytvoreno_datetime) = 1 AND WEEKDAY(expedice_prehled_plan_vytvoreno_datetime) < 4)
			  OR
			  (DATEDIFF(expedice_prehled_plan_zohlednuje_datetime, expedice_prehled_plan_vytvoreno_datetime) = 3 AND WEEKDAY(expedice_prehled_plan_vytvoreno_datetime) = 4)
		  )
	) vcera
	JOIN (
		SELECT
			DATE(expedice_prehled_plan_zohlednuje_datetime) AS datum,
			expedice_id,
			pocet_hodin AS pocet_hodin_dnes
		FROM (
			SELECT *, ROW_NUMBER() OVER (
				PARTITION BY DATE(expedice_prehled_plan_vytvoreno_datetime),
							 expedice_prehled_plan_zohlednuje_datetime,
							 expedice_prehled_plan_typ,
							 expedice_id
				ORDER BY expedice_prehled_plan_vytvoreno_datetime ASC
			) AS row_num
			FROM expedice_prehled_plan
			WHERE expedice_prehled_plan_typ = 3
			  AND expedice_id != 0) cte_dnes
		WHERE row_num = 1
		  AND DATEDIFF(expedice_prehled_plan_zohlednuje_datetime, expedice_prehled_plan_vytvoreno_datetime) = 0
	) dnes USING (datum, expedice_id)
	WHERE datum = CURDATE()
) aaa
GROUP BY expedice_id;
"""
	result = query_db(query, DatabaseAliasLive)
	if result:
		return result
	else:
		return None


def write_expedicni_skore_efektivity():
	# WRITE EPD178, WRITE EPD179
	insert_query = """
	INSERT INTO expedice_prehled_data (expedice_prehled_data_nazev_id, expedice_id, hodnota, aktualizovano)
	VALUES (%s, %s, %s, NOW())
	ON DUPLICATE KEY UPDATE
	expedice_prehled_data_nazev_id = VALUES(expedice_prehled_data_nazev_id),
	expedice_id = VALUES(expedice_id),
	hodnota = VALUES(hodnota),
	aktualizovano = VALUES(aktualizovano)
	"""
	db, cursor = get_db_cursor(DatabaseAliasLive)
	# Efektivita dnes
	skore_dnes = expedicni_skore_dnes_1_den()
	for exp_id, hodnota in skore_dnes:
		query_execute(cursor, insert_query, (178, exp_id, hodnota))

	# Efektivita včera
	skore_vcera = expedicni_skore_vcera_1_den()
	if skore_vcera:
		for exp_id, hodnota in skore_vcera:
			query_execute(cursor, insert_query, (179, exp_id, hodnota))

	db.commit()
	db.close()



def write_plan_db(zapsano_hodin, optimal_hodin, navrhovane_hodin, chybi_hodin, graf_zapsano, graf_navrhovane):
	query = """
	INSERT INTO expedice_prehled_plan (expedice_prehled_plan_vytvoreno_datetime, expedice_prehled_plan_zohlednuje_datetime, expedice_prehled_plan_typ, expedice_id, pocet_hodin)
	VALUES (NOW(), %s, %s, %s, %s)
	ON DUPLICATE KEY UPDATE
	expedice_prehled_plan_vytvoreno_datetime = VALUES(expedice_prehled_plan_vytvoreno_datetime),
	expedice_prehled_plan_zohlednuje_datetime = VALUES(expedice_prehled_plan_zohlednuje_datetime),
	expedice_prehled_plan_typ = VALUES(expedice_prehled_plan_typ),
	expedice_id = VALUES(expedice_id),
	pocet_hodin = VALUES(pocet_hodin)
	"""
	db, cursor = get_db_cursor(DatabaseAliasLive)
	for exp_id, hodiny in zapsano_hodin.items():
		for den, hod in enumerate(hodiny):
			query_execute(cursor, query, (dt.date.today() + dt.timedelta(days=den), 1, exp_id, int(hod)))

	for exp_id, hodiny in optimal_hodin.items():
		for den, hod in enumerate(hodiny):
			query_execute(cursor, query, (dt.date.today() + dt.timedelta(days=den), 2, exp_id, int(hod)))

	for exp_id, hodiny in navrhovane_hodin.items():
		for den, hod in enumerate(hodiny):
			query_execute(cursor, query, (dt.date.today() + dt.timedelta(days=den), 3, exp_id, int(hod)))

	for exp_id, hodiny in chybi_hodin.items():
		for den, hod in enumerate(hodiny):
			query_execute(cursor, query, (dt.date.today() + dt.timedelta(days=den), 4, exp_id, int(hod)))

	for e, data_point in enumerate(graf_zapsano):
		pulnoc = dt.datetime(dt.date.today().year, dt.date.today().month, dt.date.today().day, 0, 0, 0)
		query_execute(cursor, query, (
			pulnoc + dt.timedelta(hours=e), 5, 0, data_point))

	for e, data_point in enumerate(graf_navrhovane):
		pass
		pulnoc = dt.datetime(dt.date.today().year, dt.date.today().month, dt.date.today().day, 0, 0, 0)
		query_execute(cursor, query, (
			pulnoc + dt.timedelta(hours=e), 6, 0, data_point))
	db.commit()
	db.close()




def fronty_objednavky():
	# Seznam aktivních expedic, stahujeme vždy z live databáze - podmínka 100 objednávek za den
	exp2name = get_active_exps()

	# Vykrytí - jednou za den
	vykryti = {exp_id: (get_objednavkove_vykryti(exp_name, exp_id), get_polozkove_vykryti(exp_name, exp_id))
			   for exp_id, exp_name in exp2name.items()}
	if args_.output:
		print()
		print("Vykrytí objednávek:")
		print([(exp2name[x], round(vykryti[x][0], 2)) for x in [1, 3, 6, 8, 9]])
		print()

	# Naskladňovací rozdělení jednou za týden
	naskladnovaci_rozdeleni = {exp_id: get_naskladnovaci_rozdeleni_3(exp_name, exp_id, vykryti) for exp_id, exp_name in
							   exp2name.items()}

	# Fronty kusů každou hodinu, zapisují pouze frontu.
	fronty_kusu = get_fronty_kusu(exp2name)

	# Rychlosti pozic - jednou za den
	rychlosti_pozic = {exp_id: get_rychlosti_pozic(exp_id, exp_name, 14) for exp_id, exp_name in exp2name.items()}
	rychlosti_pozic_vcera = {exp_id: get_rychlosti_pozic(exp_id, exp_name, 1) for exp_id, exp_name in exp2name.items()}
	rychlost_baleni_regrese = {exp_id: get_rychlost_baleni_regrese(exp_id, exp_name) for exp_id, exp_name in
							   exp2name.items()}
	intercept = {k: v[0] for k, v in rychlost_baleni_regrese.items()}
	slope_1 = {k: v[1] for k, v in rychlost_baleni_regrese.items()}
	slope_2 = {k: v[2] for k, v in rychlost_baleni_regrese.items()}

	# Velikost kompletní a nekompletní objednávky - jednou za den
	velikost_kompletni_objednavky = {exp_id: get_velikost_kompletni(exp_id, exp_name) for exp_id, exp_name in
									 exp2name.items()}
	polozek_kompletni_objednavky = {exp_id: get_polozek_kompletni(exp_id, exp_name) for exp_id, exp_name in
										exp2name.items()}
	velikost_nekompletni_objednavky = {exp_id: get_velikost_nekomplet(exp_id, exp_name) for exp_id, exp_name in
									   exp2name.items()}
	polozek_nekompletni_objednavky = {exp_id: get_polozek_nekomplet(exp_id, exp_name) for exp_id, exp_name in
										exp2name.items()}

	# Fronta práce - jednou za hodinu
	aktualni_mnozstvi_merene_prace = vypocti_aktualni_mnozstvi_prace(exp2name, fronty_kusu, rychlosti_pozic,
																	 naskladnovaci_rozdeleni,
																	 velikost_nekompletni_objednavky,
																	 intercept, slope_1, slope_2)


	# Rozdělení objednávek mezi expedice - jednou za den
	rozdeleni_objednavek_mezi_expedice = get_rozdeleni_objednavek_mezi_expedice(exp2name)

	# Predikce objednávek - jednou za den
	predikce_objednavek = get_predikce_poctu_objednavek(rozdeleni_objednavek_mezi_expedice, exp2name)

	# Rozdělení objednávek mezi hodiny - jednou za týden
	rozdeleni_objednavek_mezi_hodiny = get_rozdeleni_objednavek_mezi_hodiny()


	# Rozdělení doby vykrytí - jednou za týden
	rozdeleni_doby_vykryti = get_rozdeleni_doby_vykryti()

	prisun_objednavek = get_prisun_objednavek(predikce_objednavek, vykryti, rozdeleni_objednavek_mezi_hodiny,
											  exp2name.keys())

	minulost_nekomplet_den = {exp_id: get_minulost_nekomplet_den(exp_id, exp_name) for exp_id, exp_name in
							  exp2name.items()}

	nevykryto_na_objednavku = get_nevykryto_na_objednavku(exp2name)
	preliti_naskladneni = vypocti_preliti_naskladneni(rozdeleni_doby_vykryti, minulost_nekomplet_den,
													  nevykryto_na_objednavku, exp2name.keys(), prisun_objednavek)
	prisun_naskladneni_cas = vypocti_prisun_naskladneni_cas(preliti_naskladneni, exp2name, rychlosti_pozic,
															naskladnovaci_rozdeleni, velikost_nekompletni_objednavky)
	prisun_vyhledani_cas = vypocti_prisun_vyhledani_cas(prisun_objednavek, exp2name, rychlosti_pozic,
														velikost_kompletni_objednavky, polozek_kompletni_objednavky,
														intercept, slope_1, slope_2)

	prisun_total_cas = get_prisun_total_cas(prisun_naskladneni_cas, prisun_vyhledani_cas, exp2name.keys())

	"""fix"""
	# >>> NOVÉ: škálování podle vykrytí (objednávkové vykrytí = vykryti[exp_id][0])
	def koef(vykryti_hodnota: float) -> float:
		return 1.0 + 0.5 * (vykryti_hodnota - 0.6)

	if args_.frequency == hodina_arg:
		prisun_total_cas = {
			exp_id: [val * koef(vykryti.get(exp_id, (0.6, None))[0]) for val in values]
			for exp_id, values in prisun_total_cas.items()
		}
	# <<< KONEC ÚPRAVY
	"""fix"""
	return_variables = [exp2name, rychlosti_pozic, rychlosti_pozic_vcera, aktualni_mnozstvi_merene_prace,
						prisun_total_cas]
	return return_variables

def get_smeny_data(fronty_objednavky_data_):
	exp2name_, rychlosti_pozic_, rychlosti_pozic_vcera_, aktualni_mnozstvi_merene_prace_, prisun_total_cas_merena_ = fronty_objednavky_data_
	# Zjistit počet odpracovaných hodin za poslední měsíc
	podil_merene = get_podil_merene(exp2name_)
	podil_merene_hpp = get_podil_merene_hpp(exp2name_)

	# Omezení expedic
	limity_smeny, technicka_minima_hodin = get_limity_smeny(exp2name_)
	technicka_minima_hodin["celkem"] = sum([technicka_minima_hodin.get(exp_id, 0) for exp_id in exp2name_.keys()])
	limity_smeny = [[limity_smeny.get(exp_id, [0, 0])] * (DNU_DOPREDU_MAXIMUM - dt.date.today().weekday()) for exp_id in
					sorted(exp2name_.keys())]

	# Rozdělení odpracovaných po hodině
	rozdeleni_prace_hodiny_pomer = get_rozdeleni_prace_hodiny(exp2name_)

	get_psh(rychlosti_pozic_, podil_merene, 14)
	get_psh(rychlosti_pozic_vcera_, podil_merene, 1)

	aktivni_smeny = get_aktivni_smeny(dt.date.today(), dnu_simulace, exp2name_)
	hpp_merene = get_hpp_hodiny_merene(exp2name_)
	podil_storno = get_podil_storno(exp2name_)
	# sum prisun_total_cas_merena by blocks of 24
	if args_.frequency == hodina_arg:

		aktualni_nemerena = {exp_id: aktualni_mnozstvi_merene_prace_[exp_id] / max(podil_merene[exp_id], 0.1)
							 for exp_id in exp2name_.keys()}
		rozdeleni_prace_hodiny_ranni_pomer = {exp_id: oriznout_ranni(rozdeleni_prace_hodiny_pomer[exp_id])
											  for exp_id in exp2name_.keys()}
		prisun_total_cas_merena_ = {exp_id: [x * (1 - (podil_storno.get(exp_id, 0))) for x in prisun_total_cas_merena_[exp_id]]
									for exp_id in exp2name_.keys()}
		prisun_total_cas_merena_ = odecti_praci_hpp(prisun_total_cas_merena_, hpp_merene, aktivni_smeny)
		prace_in_real = get_prace_in(prisun_total_cas_merena_, podil_merene)
		prace_in_real.update({"celkem": [sum([prace_in_real[exp_id][i] for exp_id in exp2name_.keys()])
										 for i in range(len(prace_in_real[list(exp2name_.keys())[0]]))]})
		# Spolehlivost
		hodiny_zapsane_hist_soucet = get_zapsane_hodiny(42, exp2name_)
		hodiny_odpracovane_hist_soucet = get_smeny(dt.date.today() - dt.timedelta(days=42), 42, exp2name_, soucet=True)
		spolehlivost_hist = {exp_id: hodiny_odpracovane_hist_soucet[exp_id] / max(hodiny_zapsane_hist_soucet[exp_id], 1)
							 for exp_id in exp2name_.keys()}
		write_spolehlivost(spolehlivost_hist, 28)

		# Spolehlivost včera
		hodiny_zapsane_vcera_soucet = get_zapsane_hodiny(1, exp2name_)
		hodiny_odpracovane_vcera_soucet = get_smeny(dt.date.today() - dt.timedelta(days=1), 1, exp2name_, soucet=True)
		if args_.output:
			print("Včera odpracováno/zapsáno:")
			print(hodiny_odpracovane_vcera_soucet)
			print(hodiny_zapsane_vcera_soucet)
		spolehlivost_vcera = {
			exp_id: hodiny_odpracovane_vcera_soucet[exp_id] / max(hodiny_zapsane_vcera_soucet[exp_id], 1)
			for exp_id in exp2name_.keys()}
		write_spolehlivost(spolehlivost_vcera, 1)

		# Počty zapsaných hodin
		hodiny_zapsane = get_hodiny(dt.date.today(), dnu_simulace, exp2name_, oddelene=False)
		# assert int inside this np array
		pocet_exp = hodiny_zapsane.shape[0]
		hodiny_zapsane = np.array([[int(x) for x in hodiny_zapsane[i]] for i in range(pocet_exp)])
		if args_.output:
			print("Zapsané hodiny")
			print(hodiny_zapsane)
		hodiny_odpracovane_real_odhad = {exp_id: [k * spolehlivost_hist[exp_id] for k in hodiny_zapsane[e]]
										 for e, exp_id in enumerate(sorted(list(exp2name_.keys())))}
		hodiny_odpracovane_dnes = {exp_id: x[0] for exp_id, x in hodiny_odpracovane_real_odhad.items()}
		odpracovano_dnes_real = get_odpracovano_dnes(hodiny_odpracovane_dnes, rozdeleni_prace_hodiny_pomer,
													 rozdeleni_prace_hodiny_ranni_pomer, aktivni_smeny, exp2name_)
		odpracovano_dnes_real["celkem"] = sum([odpracovano_dnes_real[exp_id] for exp_id in exp2name_.keys()])
		prace_out_real = get_prace_out(hodiny_odpracovane_real_odhad, rozdeleni_prace_hodiny_pomer,
									   rozdeleni_prace_hodiny_ranni_pomer, aktivni_smeny, exp2name_)
		prace_out_real.update({"celkem": [sum([prace_out_real[exp_id][i] for exp_id in exp2name_.keys()])
										  for i in range(len(prace_out_real[list(exp2name_.keys())[0]]))]})
		delka_pole = len(prace_in_real[list(exp2name_.keys())[0]])
		prace_in_real_odhad = {exp_id: [sum(prace_in_real[exp_id][max(i, 0):j]) for i, j in
										zip(range(delka_pole % 24, delka_pole - 23, 24),
											range(delka_pole % 24 + 24, delka_pole + 1, 24))] for exp_id in
							   exp2name_.keys()}
		for exp_id in prace_in_real_odhad.keys():
			prace_in_real_odhad[exp_id][0] += aktualni_nemerena[exp_id] - technicka_minima_hodin.get(exp_id, 0)
		prace_optimal_zapsano_np = np.array(
			[[int(max(x, 0) / (spolehlivost_hist[exp_id])) for x in prace_in_real_odhad[exp_id]]
			 for exp_id in
			 sorted(exp2name_.keys())])

		zapsane_smeny = get_hodiny(dt.date.today(), dnu_simulace, exp2name_, oddelene=True)
		zapsane_smeny = np.array(
			[[(int(x), int(y)) for x, y in zapsane_smeny[i]] for i in range(len(exp2name_.keys()))])

		return_variables = [exp2name_, zapsane_smeny, prace_optimal_zapsano_np, limity_smeny,
							rozdeleni_prace_hodiny_pomer, rozdeleni_prace_hodiny_ranni_pomer, aktivni_smeny,
							aktualni_nemerena, prace_in_real, spolehlivost_hist,
							hodiny_zapsane]

		return return_variables
	return None


def vytvoreni_planu(smeny_data_):
	(exp2name_, zapsane_smeny_, prace_out_optimal_np_zapsane_, limity_smeny_, rozdeleni_prace_hodiny_pomer_,
	 rozdeleni_prace_hodiny_ranni_pomer_, aktivni_smeny_, aktualni_nemerena_,
	 prace_in_real_,
	 spolehlivost_hist_, hodiny_zapsane_np) = smeny_data_
	prace_out_optimal_zapsane_ = {exp_id: [int(x) for x in prace_out_optimal_np_zapsane_[e]] for e, exp_id in
								  enumerate(sorted(list(exp2name_.keys())))}
	prace_out_optimal_zapsane_["celkem"] = [sum([prace_out_optimal_zapsane_[exp_id][i] for exp_id in exp2name_.keys()])
											for i in range(len(prace_out_optimal_zapsane_[list(exp2name_.keys())[0]]))]
	hodiny_zapsane_ = {exp_id: [int(x) for x in hodiny_zapsane_np[e]] for e, exp_id in
					   enumerate(sorted(list(exp2name_.keys())))}
	hodiny_zapsane_["celkem"] = [sum([hodiny_zapsane_[exp_id][i] for exp_id in exp2name_.keys()]) for i in
								 range(len(hodiny_zapsane_[list(exp2name_.keys())[0]]))]
	# Automatický plán
	if pouzivame_plan == 1:
		auto_plan = automaticky_plan(zapsane_smeny_, prace_out_optimal_np_zapsane_, limity_smeny_)
	elif pouzivame_plan == 2:
		auto_plan = automaticky_plan_2(zapsane_smeny_, prace_out_optimal_np_zapsane_, limity_smeny_)
	elif pouzivame_plan == 1.5:
		prvni = automaticky_plan(zapsane_smeny_, prace_out_optimal_np_zapsane_, limity_smeny_)
		druhy = automaticky_plan_2(zapsane_smeny_, prace_out_optimal_np_zapsane_, limity_smeny_)
		# prumer, po složkách zaokrouhleno nahoru
		auto_plan = (np.array(prvni) + np.array(druhy)) / 2
	else:
		auto_plan = prace_out_optimal_np_zapsane_
	auto_plan = np.array([[int(x) for x in y] for y in auto_plan])


	hodiny_autoplan_exp = {exp_id: [int(x) for x in auto_plan[e]] for e, exp_id in
						   enumerate(sorted(list(exp2name_.keys())))}
	hodiny_autoplan_exp["celkem"] = [sum([hodiny_autoplan_exp[exp_id][i] for exp_id in exp2name_.keys()]) for i in
									 range(len(hodiny_autoplan_exp[list(exp2name_.keys())[0]]))]

	prace_fronta_zapsane = get_graph_data(aktualni_nemerena_, prace_in_real_, hodiny_zapsane_, aktivni_smeny_,
										  rozdeleni_prace_hodiny_ranni_pomer_, rozdeleni_prace_hodiny_pomer_,
										  spolehlivost_hist_, realne=False)
	prace_fronta_optimal = get_graph_data(aktualni_nemerena_, prace_in_real_, prace_out_optimal_zapsane_,
										  aktivni_smeny_, rozdeleni_prace_hodiny_ranni_pomer_,
										  rozdeleni_prace_hodiny_pomer_, spolehlivost_hist_, realne=False)
	prace_fronta_plan = get_graph_data(aktualni_nemerena_, prace_in_real_, hodiny_autoplan_exp, aktivni_smeny_,
									   rozdeleni_prace_hodiny_ranni_pomer_, rozdeleni_prace_hodiny_pomer_,
									   spolehlivost_hist_, realne=False)
	fronta_real = [int(x) for x in prace_fronta_zapsane["celkem"]]
	fronta_optimal = [int(x) for x in prace_fronta_optimal["celkem"]]
	fronta_plan = [int(x) for x in prace_fronta_plan["celkem"]]
	plot_total(fronta_real, dnu_simulace, show=False, typ=0)
	plot_total(fronta_plan, dnu_simulace, show=False, typ=1)
	for exp_id, exp_name in exp2name_.items():
		plot_total(prace_fronta_zapsane[exp_id], dnu_simulace, show=False, typ=exp_name)
	hodinovy_dluh = {exp_id: prace_out_optimal_zapsane_[exp_id][0] - hodiny_zapsane_[exp_id][0]
					 for exp_id in exp2name_.keys()}
	write_hodinovy_dluh(hodinovy_dluh)
	hodiny_zapsane_np = np.array([[int(x) for x in hodiny_zapsane_np[e]] for e in range(len(exp2name_.keys()))])
	write_excel(dnu_simulace, hodiny_zapsane_np, prace_out_optimal_np_zapsane_, auto_plan)
	chybejici_hodiny = {exp_id: [hodiny_autoplan_exp[exp_id][i] - hodiny_zapsane_[exp_id][i]
								 for i in range(len(hodiny_zapsane_[exp_id]))] for exp_id in exp2name_.keys()}
	chybejici = auto_plan - hodiny_zapsane_np
	if args_.output:
		print("Optimum zapsáno: [h]")
		print(prace_out_optimal_np_zapsane_)
		print("Automatický plán")
		print(auto_plan)
	write_plan_db(hodiny_zapsane_, prace_out_optimal_zapsane_, hodiny_autoplan_exp, chybejici_hodiny, fronta_real,
				  fronta_plan)


t = time.time()

fronty_objednavky_data = fronty_objednavky()

smeny_data = get_smeny_data(fronty_objednavky_data)

if args_.frequency == hodina_arg:
	vytvoreni_planu(smeny_data)
	write_expedicni_skore_efektivity()

if args_.frequency == den_arg:
	insert_zmeny()

print(args_.frequency, time.time() - t)
print("Volání mysql query:", query_execute.calls)