import datetime
import json
import logging
from enum import Enum
from io import BytesIO

import numpy as np
from common.status import HTTP_200_OK, HTTP_204_NO_CONTENT, HTTP_400_BAD_REQUEST
from flask import Blueprint, request, send_file
from flask_jwt_extended import jwt_required
from median.constant import EcoType, HistoryType
from median.models import Historique, SuiviProd, SuiviProdMinutes, Magasin
from peewee import fn
from ressources.acced.production.acced_production_service import _get_config, CONTRAT_DOSE_STACK, \
    CONTRAT_DOSE_PILLBOX
from ressources.acced.production.acced_production_service import get_counter_ratio
from ressources.acced.production.acced_production_service import (get_counter_sum, get_data_from_variable,
                                                                  get_counter_mean)
from ressources.acced.production.acced_production_service import get_trends, get_counters
from ressources.acced.production.acced_production_service import isSuivi_db_connected
from ressources.acced.production.availability_service import get_availability_mean
from ressources.acced.production.blocked_box_service import get_box_blocked_data, get_box_blocked_mean
from ressources.acced.production.completion_service import get_completion_data, get_completion_mean
from ressources.acced.production.cut_rejet_service import get_cut_counter
from ressources.acced.production.cut_rejet_service import get_cut_reject_mean
from ressources.acced.production.duplicate_ratio_service import get_duplicate_ratio_mean, get_duplicate_counter
from ressources.acced.production.pillbox_service import get_pillbox_mean
from ressources.acced.production.stack_service import get_stack_mean
from ressources.blueprint.stats_utils import estimate_coef, lineary_regression

from ressources.astus.utils import close_excel_writer, generate_memory_excel_writer

acced_production_blueprint = Blueprint('acced_production', __name__)

logger = logging.getLogger('median')


class GRAPH(Enum):
    COUPE = 0
    CUEILLETTE = 1
    RETOUR = 2


@acced_production_blueprint.route('connection', methods=['GET'])
@jwt_required()
def suivi_prod_connected():
    return {
        'connection': isSuivi_db_connected()
    }, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/data/duplicate', methods=['POST'])
@jwt_required()
def get_duplicate_trends(type_mag):
    data = json.loads(request.data)
    now = data['end']
    start = data['start']

    datas = get_duplicate_counter(type_mag=type_mag, start=start, end=now)

    return datas, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/data/cut', methods=['POST'])
@jwt_required()
def get_cut_trends(type_mag):
    data = json.loads(request.data)
    now = data['end']
    start = data['start']

    datas = get_cut_counter(type_mags=[type_mag], start=start, end=now)

    return datas, HTTP_200_OK


# @acced_production_blueprint.route('/cut/trends', methods=['POST'])
# @jwt_required()
# def get_all_cut_trends():
#     data = json.loads(request.data)
#     now = data['end']
#     start = data['start']
#
#     equipments = Magasin.select(Magasin.type_mag).where(
#         Magasin.eco_type << [EcoType.Coupe.value, EcoType.Cueillette.value])
#     type_mags = list(map(lambda e: e.type_mag, list(equipments)))
#     datas = get_cut_counter(type_mags=type_mags, start=start, end=now)
#
#     cut_res = None
#     counters = datas.get('counters', None)
#
#     if counters is not None:
#         cut = next(filter(lambda c: c['counter'] == 'cut_number', counters), None)
#         cut_stock = next(filter(lambda c: c['counter'] == 'cut_stock', counters), None)
#         if cut is not None and cut_stock is not None:
#             total_cut = cut['value'].get('sum', 0)
#             total_cut_stock = cut_stock['value'].get('sum', 0)
#             threshold = _get_config(property=CONTRAT_CUT_REJECT_PERCENTAGE)
#             cut_res = {
#                 'unit': 'unit.percentage',
#                 'value': round(100 * (total_cut - total_cut_stock) / total_cut, 2) if total_cut > 0 else 0,
#                 'threshold': {
#                     'max': threshold,
#                     'warning': round(1.1 * threshold, 2),
#                     'unit': 'unit.percentage'
#                 }
#             }
#
#     return {'data': cut_res}, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/data/completion', methods=['POST'])
@jwt_required()
def get_completion_trends(type_mag):
    data = json.loads(request.data)
    now = data['end']
    start = data['start']

    completions = list(filter(lambda o: o['prescription_qty'] > 0,
                              get_completion_data(start=start, end=now, type_mag=type_mag)))
    val_counts = list(map(lambda r: ((1 - (r['prescription_qty'] - r['served_qty']) / r['prescription_qty']) * 100.00)
                          if r['prescription_qty'] > 0 else 0, completions))
    val_prescriptions = list(map(lambda r: r['prescription_qty'], completions))
    val_served = list(map(lambda r: r['served_qty'], completions))
    val_weeks = list(map(lambda c: c['date'], completions))

    prescription_value = get_trends(val_counts=val_prescriptions, val_weeks=val_weeks)
    prescription_value['sum'] = sum(val_prescriptions)
    prescription_value['mean'] = round(np.mean(np.array(val_prescriptions)), 2) if len(val_prescriptions) else 0
    prescription_value['threshold'] = {'min': 0}

    served_value = get_trends(val_counts=val_served, val_weeks=val_weeks)
    served_value['sum'] = sum(val_served)
    served_value['mean'] = round(np.mean(np.array(val_served)), 2) if len(val_served) else 0
    served_value['threshold'] = {'min': 0}

    return {
        'counters': [
            {
                'counter': 'prescription_qty',
                'value': prescription_value
            },
            {
                'counter': 'served_qty',
                'value': served_value
            },
        ],
        'data': get_trends(val_counts=val_counts, val_weeks=val_weeks)
    }, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/data/availability', methods=['POST'])
@jwt_required()
def get_availability_trends(type_mag):
    data = json.loads(request.data)
    end = data['end']
    start = data['start']

    variables = ['API_DISPONIBILITE_Med', 'API_Temps_Panne_Med', 'API_TEMPS_ATTENTE_OPERATEUR_Med',
                 'API_TEMPS_ATTENTE_ECRAN_Med', 'API_TEMPS_RESOLUTION_DEFAUT_Med', 'API_Temps_Chgt_Blister_Med',
                 'API_TEMPS_UTILISATION_Med', 'API_Temps_Dchgt_Plateau_Med']

    counter_values = get_counters(start=start, end=end,
                                  type_mags=[type_mag], all_variables=variables)
    return {
        'counters': [
            {'counter': 'waiting_ope',
             'value': get_counter_sum(counter_values=counter_values,
                                      variable='time_waiting_ope', max_threshold=0)},
            {'counter': 'waiting_before_defect',
             'value': get_counter_sum(counter_values=counter_values,
                                      variable='time_waiting_before_defect', max_threshold=0)},
            {'counter': 'defect_resolve',
             'value': get_counter_sum(counter_values=counter_values,
                                      variable='time_defect_resolve', max_threshold=0)},
            {'counter': 'working',
             'value': get_counter_sum(counter_values=counter_values,
                                      variable='time_working', min_threshold=0)},
            {'counter': 'load_tray',
             'value': get_counter_sum(counter_values=counter_values,
                                      variable='time_load_tray', max_threshold=0)},
            {'counter': 'time_loading',
             'value': get_counter_sum(counter_values=counter_values,
                                      variable='time_loading', max_threshold=0)},
        ],
        'data': get_data_from_variable(counter_values=counter_values,
                                       variable='availability')
    }, HTTP_200_OK


@acced_production_blueprint.route('/availability/trends', methods=['POST'])
@jwt_required()
def get_all_availability_trends():
    data = json.loads(request.data)
    end = data['end']
    start = data['start']

    variables = ['API_DISPONIBILITE_Med', 'API_Temps_Panne_Med', 'API_TEMPS_ATTENTE_OPERATEUR_Med',
                 'API_TEMPS_ATTENTE_ECRAN_Med', 'API_TEMPS_RESOLUTION_DEFAUT_Med',
                 'API_TEMPS_UTILISATION_Med', 'API_Temps_Dchgt_Plateau_Med']

    equipments = Magasin.select(Magasin.type_mag).where(
        Magasin.eco_type << [EcoType.Coupe.value, EcoType.Cueillette.value])
    type_mags = list(map(lambda e: e.type_mag, list(equipments)))
    counter_values = get_counters(start=start, end=end,
                                  type_mags=type_mags, all_variables=variables)
    availability = get_availability_mean(counter_values)
    value = availability.get('mean', None) if availability is not None else None
    return {'data': {
        'unit': 'unit.percentage',
        'value': round(value, 0) if value is not None and value > 0 else None,
        'threshold': availability.get('threshold', None),
    }
    }, HTTP_200_OK


@acced_production_blueprint.route('/pillbox/trends', methods=['POST'])
@jwt_required()
def get_all_pillbox_trends():
    data = json.loads(request.data)
    end = data['end']
    start = data['start']

    variables = [
        'API_Nbr_de_Pilulier_Med', 'API_Nbr_DU_Pose_Med', 'API_Nbr_Sachet_Pose_Med', 'API_Nbr_SachetP_Pose_Med',
        'API_Nbr_Carnet_Pilulier_Med',
        'API_Temps_Cueil_Pil_Med', 'API_Nbr_de_Pilulier_heure_Med',
        'API_Nbr_Ticket_Pose_Med'
    ]

    equipments = Magasin.select(Magasin.type_mag).where(
        Magasin.eco_type << [EcoType.Cueillette.value, EcoType.Coupe.value])

    type_mags = list(map(lambda e: e.type_mag, list(equipments)))

    counter_values = get_counters(start=start, end=end, type_mags=type_mags, all_variables=variables)
    pillbox = get_pillbox_mean(counter_values)

    if pillbox is not None:
        cadence_max = 120
        unit = pillbox.get('unit', '')
        if unit == 'unit.dose.h':
            cadence_max = 300
        elif unit == 'unit.pillbox.h':
            cadence_max = 60

        return {'list': [{
            'label': 'dose_h',
            'max': cadence_max,
            'value': pillbox.get('mean', 0),
            'unit': pillbox.get('unit', ''),
            'threshold': pillbox.get('threshold', None),
        }
        ]}, HTTP_200_OK
    else:
        return {}, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/data/pillbox', methods=['POST'])
@jwt_required()
def get_pillbox_trends(type_mag):
    data = json.loads(request.data)
    now = data['end']
    start = data['start']

    variables = [
        'API_Nbr_de_Pilulier_Med', 'API_Nbr_DU_Pose_Med', 'API_Nbr_Sachet_Pose_Med', 'API_Nbr_SachetP_Pose_Med',
        'API_Nbr_Carnet_Pilulier_Med',
        'API_Temps_Cueil_Pil_Med', 'API_Nbr_de_Pilulier_heure_Med',
        'API_Nbr_Ticket_Pose_Med'
    ]

    counter_values = get_counters(start=start, end=now, type_mags=[type_mag], all_variables=variables)

    pillbox = get_pillbox_mean(counter_values)
    if pillbox is not None:
        variable = 'pillbox_cadence_h'
        if pillbox['cadence_doses'] > _get_config(property=CONTRAT_DOSE_PILLBOX):
            variable = 'pillbox_cadence_dose'

        return {
            'counters': [
                {'counter': 'dose_h',
                 'unit': 'unit.dose.h',
                 'value': get_counter_ratio(counter_values=counter_values,
                                            variable_ratio='pillbox_cadence_dose',
                                            variable1='pillbox_dose_num',
                                            variable2='picking_pillbox_time',
                                            min_threshold=0)},
                {'counter': 'num',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='pillbox_num', min_threshold=0)},

                {'counter': 'qty',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='pillbox_dose_num', min_threshold=0),
                 'values': [
                     {'counter': 'unit_dose',
                      'value': get_counter_sum(counter_values=counter_values,
                                               variable='pillbox_unit_dose', min_threshold=0)},
                     {'counter': 'sachet',
                      'value': get_counter_sum(counter_values=counter_values,
                                               variable='pillbox_sachet', min_threshold=0)},
                     {'counter': 'powder_sachet',
                      'value': get_counter_sum(counter_values=counter_values,
                                               variable='pillbox_powder_sachet', min_threshold=0)}
                 ]},

                {'counter': 'pillbox_time',
                 'unit': 'unit.hour',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='picking_pillbox_time', min_threshold=0)},
                {'counter': 'dose',
                 'value': get_counter_mean(counter_values=counter_values,
                                           variable='pillbox_dose', min_threshold=0)},
                {'counter': 'ticket',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='pillbox_ticket', min_threshold=0)}
            ],
            'data': get_data_from_variable(counter_values=counter_values,
                                           variable=variable)
        }, HTTP_200_OK
    else:
        return {}, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/data/stack', methods=['POST'])
@jwt_required()
def get_stack_trends(type_mag):
    data = json.loads(request.data)
    now = data['end']
    start = data['start']

    variables = [
        'API_Nbr_Carnet_Pilulier_Med', 'API_Nbr_Sachet_Pose_Carnet_Med', 'API_Nbr_SachetP_Pose_Carnet_Med',
        'API_Nbr_Du_Pose_Carnet_Med', 'API_Temps_Cueil_Carnet_Med', 'API_Nbr_Carnet_Pilulier_heure_Med',
        'API_Nbr_Ticket_Pose_Carnet_Med'
    ]

    counter_values = get_counters(start=start, end=now, type_mags=[type_mag], all_variables=variables)

    stack = get_stack_mean(counter_values)
    if stack is not None:
        variable = 'stack_cadence_h'
        if stack['cadence_doses'] > _get_config(property=CONTRAT_DOSE_STACK):
            variable = 'stack_cadence_dose'

        return {
            'counters': [
                {'counter': 'num_stack',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='stack_num', min_threshold=0)},
                {'counter': 'unit_dose',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='stack_unit_dose', min_threshold=0)},
                {'counter': 'sachet',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='stack_sachet', min_threshold=0)},
                {'counter': 'powder_sachet',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='stack_powder_sachet', min_threshold=0)},
                {'counter': 'picking_time',
                 'unit': 'unit.hour',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='picking_stack_time', min_threshold=0)},
                {'counter': 'dose',
                 'value': get_counter_mean(counter_values=counter_values,
                                           variable='stack_dose', min_threshold=0)},
                {'counter': 'ticket',
                 'value': get_counter_sum(counter_values=counter_values,
                                          variable='stack_ticket', min_threshold=0)}
            ],
            'data': get_data_from_variable(counter_values=counter_values,
                                           variable=variable)
        }, HTTP_200_OK
    else:
        return {}, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/data/blocked', methods=['POST'])
@jwt_required()
def get_box_blocked_trends(type_mag):
    data = json.loads(request.data)
    now = data['end']
    start = data['start']
    variables = ['API_Temps_Cueil_Pil_Med', 'API_Temps_Cueil_Carnet_Med']
    counter_values = get_counters(start=start, end=now, type_mags=[type_mag], all_variables=variables)

    blocked_datas = get_box_blocked_data(start=start, end=now,
                                         counter_values=counter_values,
                                         type_mag=type_mag)

    blocked_boxes_ratio = list(map(lambda r: r['ratio'], blocked_datas))
    blocked_boxes_weeks = list(map(lambda r: r['date'], blocked_datas))
    blocked_boxes_num = list(map(lambda r: r['blocked'], blocked_datas))

    blocked_boxes_value = get_trends(val_counts=blocked_boxes_num, val_weeks=blocked_boxes_weeks)
    blocked_boxes_value['sum'] = sum(blocked_boxes_num)
    blocked_boxes_value['mean'] = round(np.mean(np.array(blocked_boxes_num)), 2) if len(blocked_boxes_num) else 0
    blocked_boxes_value['threshold'] = {'min': 0}

    weeks = np.array(range(len(blocked_boxes_weeks)))
    ratios = np.array(blocked_boxes_ratio)
    count_coeff = estimate_coef(weeks, ratios) if len(ratios) > 0 else None
    count_r2 = lineary_regression(count_coeff, weeks, ratios) if (count_coeff is not None and
                                                                  count_coeff[0] is not None and
                                                                  count_coeff[1] is not None) else None

    return {
        'counters': [
            {
                'counter': 'picking_time',
                'unit': 'unit.hour',
                'value': get_counter_sum(counter_values=counter_values,
                                         variable='picking_time', min_threshold=0)
            },
            {
                'counter': 'blocked',
                'value': blocked_boxes_value
            }
        ],
        'data': {
            'datas': blocked_datas,
            'trends': round(count_coeff[0] / abs(count_coeff[1]) * 100.0, 2)
            if count_r2 is not None and count_r2 > 0.65 else None
        },

    }, HTTP_200_OK


@acced_production_blueprint.route('/<string:type_mag>/current', methods=['POST'])
@jwt_required()
def get_current_kpi(type_mag):
    data = json.loads(request.data)

    now = data['end']
    start = data['start']
    type_mags = [type_mag]

    counter_values = get_counters(start=start, end=now, type_mags=type_mags)

    return {
        'type_mag': type_mag,
        "pillbox": get_pillbox_mean(counter_values),
        "stack": get_stack_mean(counter_values),
        "availability": get_availability_mean(counter_values),
        "completion": get_completion_mean(start=start, end=now, type_mag=type_mag),
        "cut_reject": get_cut_reject_mean(start=start, end=now, type_mag=type_mag),
        "box_blocked": get_box_blocked_mean(start=start, end=now,
                                            counter_values=counter_values,
                                            type_mag=type_mag),
        "duplicate_ratio": get_duplicate_ratio_mean(start=start, end=now, type_mag=type_mag),

    }, HTTP_200_OK


class TypeMachine(Enum):
    Acced = 'acced'
    Riedl = 'riedl'


@acced_production_blueprint.route('<string:type>/export', methods=['PATCH'])
@jwt_required()
def export_excel(type):
    data = json.loads(request.data)
    try:
        v_date_debut = data['date_debut']
        v_date_fin = data['date_fin']
        v_ref = data['produit']
        v_service = data['service']
        headers = data['headers']

        date = datetime.datetime.strptime(v_date_fin, "%Y-%m-%d")
        v_date_fin = date + datetime.timedelta(days=1)
        eco_types = []

        if type == TypeMachine.Acced.value:
            eco_types = [EcoType.Cueillette.value, EcoType.Coupe.value]
        elif type == TypeMachine.Riedl.value:
            eco_types = [EcoType.Riedl.value]

        equipments = (Magasin.select(Magasin.libelle)
                      .where(Magasin.eco_type << eco_types))

        list_coupe = Historique \
            .select(fn.substr(Historique.chrono, 1, 10).alias('x_chrono'),
                    fn.SUM(Historique.quantite_mouvement).alias("x_qte_mvt"),
                    Magasin.libelle.alias('mag')) \
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag)) \
            .where((Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin) &
                   ((Historique.type_mouvement == HistoryType.Coupe.value) |
                    (Historique.type_mouvement == HistoryType.Directe.value)) &
                   ((v_ref == '') | (Historique.reference == v_ref)) &
                   ((v_service == '') | (Historique.service == v_service)) &
                   (Magasin.eco_type << eco_types)) \
            .order_by(Historique.chrono, Magasin.libelle) \
            .group_by(fn.Substr(Historique.chrono, 1, 10), Magasin.libelle).objects()

        list_export = (Historique
                       .select(fn.substr(Historique.chrono, 1, 10).alias('x_chrono'),
                               fn.IFNULL(fn.SUM(Historique.quantite_mouvement), 0).alias("x_qte_mvt"),
                               Historique.type_mouvement,
                               Historique.service,
                               Magasin.libelle.alias('mag'))
                       .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
                       .where((Historique.chrono >= v_date_debut) &
                              (Historique.chrono <= v_date_fin) &
                              ((v_ref == '') | (Historique.reference == v_ref)) &
                              ((v_service == '') | (Historique.service == v_service)) &
                              (Magasin.eco_type << eco_types))
                       .order_by(Historique.chrono, Magasin.libelle)
                       .group_by(fn.Substr(Historique.chrono, 1, 10),
                                 Historique.type_mouvement, Magasin.libelle, Historique.service)).objects()

        output = BytesIO()
        writer = generate_memory_excel_writer(output=output)

        if type != TypeMachine.Riedl.value:
            create_sheet(writer, headers,
                         transform_list(equipments=equipments, global_list=list_coupe),
                         GRAPH.COUPE.value, equipments=equipments)

        create_sheet(writer, headers,
                     transform_list(equipments=equipments,
                                    global_list=filter(lambda h: (h.type_mouvement == HistoryType.Sortie.value),
                                                       list(list_export))),
                     GRAPH.CUEILLETTE.value,
                     equipments)
        create_sheet(writer, headers,
                     transform_list(equipments=equipments,
                                    global_list=filter(lambda h: (h.type_mouvement == HistoryType.Entree.value) &
                                                                 (h.service != ''),
                                                       list(list_export))),
                     GRAPH.RETOUR.value,
                     equipments)

        close_excel_writer(writer=writer)
        output.seek(0)
        return send_file(output, as_attachment=True, download_name="acced_stat_prod.xlsx")

    except Exception as error:
        logger.error(error.args)
        return {'message': error.args}, HTTP_400_BAD_REQUEST


def transform_list(equipments, global_list):
    objs = []
    for item in global_list:
        cut = next(filter(lambda c: c['x_chrono'] == item.x_chrono, objs), None)

        if cut is None:
            cut = {
                'x_chrono': item.x_chrono,
                'x_qte_mvt': 0
            }
            for eq in equipments:
                cut[eq.libelle] = 0
            objs.append(cut)

        cut['x_qte_mvt'] = cut['x_qte_mvt'] + item.x_qte_mvt
        cut[item.mag] = cut[item.mag] + item.x_qte_mvt

    return objs


def create_sheet(writer, headers, datas, val, equipments):
    format_court = writer.add_format({'num_format': 'dd/mm/yyyy'})

    t = list(filter(lambda a: a['val'] == val, headers))

    worksheet = writer.add_worksheet(t[0]['label'])
    col = 0
    for h in t[0]['headers']:
        worksheet.write(0, col, h)
        col = col + 1

    for e in equipments:
        worksheet.write(0, col, e.libelle)
        col = col + 1

    row = 1
    if len(datas) > 0:
        for coupe in datas:
            idx = 0
            for key in coupe.keys():
                if key == 'x_chrono':
                    worksheet.write_datetime(row, idx, coupe[key], format_court)
                else:
                    worksheet.write(row, idx, coupe[key])
                idx = idx + 1

            row = row + 1


@acced_production_blueprint.route('<string:type>/picking', methods=['GET'])
@jwt_required()
def get_picking(type):
    args = request.args
    v_date_debut = args['date_debut']
    v_date_fin = args['date_fin']
    v_affichage = args['affichage']
    v_ref = args['produit']
    v_service = args['service']

    date = datetime.datetime.strptime(v_date_fin, "%Y-%m-%d")
    v_date_fin = date + datetime.timedelta(days=1)

    labels = []
    data = []

    l_seuil = []

    eco_types = []
    if type == TypeMachine.Acced.value:
        eco_types = [EcoType.Cueillette.value, EcoType.Coupe.value]
    elif type == TypeMachine.Riedl.value:
        eco_types = [EcoType.Riedl.value]

    if v_affichage == "1":
        l_seuil = (Historique
                   .select(fn.substr(Historique.chrono, 1, 10).alias('x_chrono'), fn.SUM(
                       Historique.quantite_mouvement).alias("x_qte_mvt"))
                   .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
                   .where(
                       (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                       & (Historique.type_mouvement == 'SOR')
                       & ((v_ref == '') | (Historique.reference == v_ref))
                       & ((v_service == '') | (Historique.service == v_service))
                       & (Magasin.eco_type << eco_types)).group_by(
                       fn.Substr(Historique.chrono, 1, 10)))

    if v_affichage == "2":
        l_seuil = (
            Historique
            .select(fn.date_format(Historique.chrono, '%Y-%V').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & (Historique.type_mouvement == 'SOR')
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types))
            .group_by(
                fn.date_format(Historique.chrono, '%Y-%V')))

    if v_affichage == "3":
        l_seuil = (
            Historique
            .select(fn.date_format(Historique.chrono, '%Y-%m').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & (Historique.type_mouvement == 'SOR')
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types))
            .group_by(
                fn.date_format(Historique.chrono, '%Y-%m')))

    if (v_affichage == "4"):
        l_seuil = (
            Historique.select(fn.date_format(Historique.chrono, '%Y').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & (Historique.type_mouvement == 'SOR')
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types))
            .group_by(
                fn.date_format(Historique.chrono, '%Y')))

    for ls in l_seuil:
        if v_affichage == "1":
            labels.append(str(ls.x_chrono)[8:10] + "/" + str(ls.x_chrono)[5:7] + "/" + str(ls.x_chrono)[0:4])
        elif v_affichage == "2":
            labels.append("S" + str(ls.x_chrono)[5:7] + "-" + str(ls.x_chrono)[0:4])
        elif v_affichage == "3":
            labels.append("" + str(ls.x_chrono)[5:7] + "-" + str(ls.x_chrono)[0:4])
        elif v_affichage == "4":
            labels.append("" + str(ls.x_chrono)[0:4])
        data.append(ls.x_qte_mvt)

    data = {
        'labels': labels,
        'data': data
    }
    return data


@acced_production_blueprint.route('<string:type>/cut', methods=['GET'])
@jwt_required()
def get_cut(type):
    args = request.args
    v_date_debut = args['date_debut']
    v_date_fin = args['date_fin']
    v_affichage = args['affichage']
    v_ref = args['produit']
    v_service = args['service']

    labels = []
    data = []

    l_seuil = []

    eco_types = []
    if type == TypeMachine.Acced.value:
        eco_types = [EcoType.Cueillette.value, EcoType.Coupe.value]
    else:
        return {

        }, HTTP_204_NO_CONTENT

    if (v_affichage == "1"):
        l_seuil = (
            Historique.select(fn.substr(Historique.chrono, 1, 10).alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & ((Historique.type_mouvement == 'CPE') | (Historique.type_mouvement == 'DIR'))
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types)).group_by(
                fn.Substr(Historique.chrono, 1, 10)))

    if (v_affichage == "2"):
        l_seuil = (
            Historique.select(fn.date_format(Historique.chrono, '%Y-%V').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & ((Historique.type_mouvement == 'CPE') | (Historique.type_mouvement == 'DIR'))
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types)).group_by(
                fn.date_format(Historique.chrono, '%Y-%V')))

    if (v_affichage == "3"):
        l_seuil = (
            Historique.select(fn.date_format(Historique.chrono, '%Y-%m').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & ((Historique.type_mouvement == 'CPE') | (Historique.type_mouvement == 'DIR'))
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types)).group_by(
                fn.date_format(Historique.chrono, '%Y-%m')))

    if (v_affichage == "4"):
        l_seuil = (
            Historique.select(fn.date_format(Historique.chrono, '%Y').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & ((Historique.type_mouvement == 'CPE') | (Historique.type_mouvement == 'DIR'))
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types)).group_by(
                fn.date_format(Historique.chrono, '%Y')))

    for ls in l_seuil:
        if (v_affichage == "1"):
            labels.append(str(ls.x_chrono)[8:10] + "/" + str(ls.x_chrono)[5:7] + "/" + str(ls.x_chrono)[0:4])
        elif (v_affichage == "2"):
            labels.append("S" + str(ls.x_chrono)[5:7] + "-" + str(ls.x_chrono)[0:4])
        elif (v_affichage == "3"):
            labels.append("" + str(ls.x_chrono)[5:7] + "-" + str(ls.x_chrono)[0:4])
        elif (v_affichage == "4"):
            labels.append("" + str(ls.x_chrono)[0:4])
        data.append(ls.x_qte_mvt)

    data = {
        'labels': labels,
        'data': data
    }
    return data


@acced_production_blueprint.route('<string:type>/return', methods=['GET'])
@jwt_required()
def get_return(type):
    args = request.args
    v_date_debut = args['date_debut']
    v_date_fin = args['date_fin']
    v_affichage = args['affichage']
    v_ref = args['produit']
    v_service = args['service']

    date = datetime.datetime.strptime(v_date_fin, "%Y-%m-%d")
    v_date_fin = date + datetime.timedelta(days=1)

    labels = []
    data = []

    l_seuil = []

    eco_types = []
    if type == TypeMachine.Acced.value:
        eco_types = [EcoType.Cueillette.value, EcoType.Coupe.value]
    elif type == TypeMachine.Riedl.value:
        eco_types = [EcoType.Riedl.value]

    if (v_affichage == "1"):
        l_seuil = (Historique
                   .select(fn.substr(Historique.chrono, 1, 10).alias('x_chrono'), fn.SUM(
                       Historique.quantite_mouvement).alias("x_qte_mvt"))
                   .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
                   .where(
                       (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                       & (Historique.type_mouvement == 'ENT') & (Historique.service != '')
                       & ((v_ref == '') | (Historique.reference == v_ref))
                       & ((v_service == '') | (Historique.service == v_service))
                       & (Magasin.eco_type << eco_types))
                   .group_by(
                       fn.Substr(Historique.chrono, 1, 10)))

    if (v_affichage == "2"):
        l_seuil = (
            Historique.select(fn.date_format(Historique.chrono, '%Y-%V').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & (Historique.type_mouvement == 'ENT') & (Historique.service != '')
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types)).group_by(
                fn.date_format(Historique.chrono, '%Y-%V')))

    if v_affichage == "3":
        l_seuil = (
            Historique.select(fn.date_format(Historique.chrono, '%Y-%m').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & (Historique.type_mouvement == 'ENT') & (Historique.service != '')
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types)).group_by(
                fn.date_format(Historique.chrono, '%Y-%m')))

    if (v_affichage == "4"):
        l_seuil = (
            Historique.select(fn.date_format(Historique.chrono, '%Y').alias('x_chrono'), fn.SUM(
                Historique.quantite_mouvement).alias("x_qte_mvt"))
            .join(Magasin, on=Historique.adresse.startswith(Magasin.mag))
            .where(
                (Historique.chrono >= v_date_debut) & (Historique.chrono <= v_date_fin)
                & (Historique.type_mouvement == 'ENT') & (Historique.service != '')
                & ((v_ref == '') | (Historique.reference == v_ref))
                & ((v_service == '') | (Historique.service == v_service))
                & (Magasin.eco_type << eco_types)).group_by(
                fn.date_format(Historique.chrono, '%Y')))

    for ls in l_seuil:
        if (v_affichage == "1"):
            labels.append(str(ls.x_chrono)[8:10] + "/" + str(ls.x_chrono)[5:7] + "/" + str(ls.x_chrono)[0:4])
        elif (v_affichage == "2"):
            labels.append("S" + str(ls.x_chrono)[5:7] + "-" + str(ls.x_chrono)[0:4])
        elif (v_affichage == "3"):
            labels.append("" + str(ls.x_chrono)[5:7] + "-" + str(ls.x_chrono)[0:4])
        elif (v_affichage == "4"):
            labels.append("" + str(ls.x_chrono)[0:4])
        data.append(ls.x_qte_mvt)

    data = {
        'labels': labels,
        'data': data
    }
    return data


@acced_production_blueprint.route('picking_v3', methods=['POST'])
@jwt_required()
def post():
    args = request.form
    v_date = args['date']
    v_mag = args['mag']

    labels = []
    data = []

    date_fin = datetime.datetime.strptime(v_date, "%Y-%m-%d") + datetime.timedelta(days=1)

    l_date = []

    l_date = (SuiviProd.select(fn.MIN(SuiviProd.chrono).alias('x_min'), fn.MAX(
        SuiviProd.chrono).alias('x_max')).where(
        (SuiviProd.chrono >= v_date) & (SuiviProd.chrono < date_fin)
        & (SuiviProd.poste == v_mag) &
        ((SuiviProd.variable == 'API_Nbr_Produit_Pilulier_Med') |
         (SuiviProd.variable == 'API_Nbr_de_Produit_Carnet_Med'))))

    for ld in l_date:
        x_min = ld.x_min
        x_max = ld.x_max

    l_seuil = (
        SuiviProd.select(
            SuiviProd.chrono.alias('x_chrono'),
            SuiviProd.valeur_num.alias("x_qte_mvt"), SuiviProd.variable.alias('x_variable')).where(
            (SuiviProd.chrono >= v_date) & (SuiviProd.chrono < date_fin)
            & (SuiviProd.poste == v_mag) &
            ((SuiviProd.variable == 'API_Nbr_Produit_Pilulier_Med') |
             (SuiviProd.variable == 'API_Nbr_de_Produit_Carnet_Med'))))

    labels1 = []

    data1 = []
    data2 = []
    data3 = []
    data4 = []

    l_seuil1 = (
        SuiviProd.select(
            SuiviProd.chrono.alias('x_chrono'),
            SuiviProd.valeur_num.alias("x_qte_mvt"), SuiviProd.variable.alias('x_variable')).where(
            (SuiviProd.chrono >= v_date) & (SuiviProd.chrono < date_fin)
            & (SuiviProd.poste == v_mag) &
            ((SuiviProd.variable == 'API_Nbr_de_Pilulier_Med') |
             (SuiviProd.variable == 'API_Nbr_Carnet_Pilulier_Med'))))

    x_min = str(x_min)[11:19]
    x_max = str(x_max)[11:19]

    l_seuil4 = (SuiviProdMinutes.select(
        SuiviProdMinutes.chrono.alias('x_chrono'),
        SuiviProdMinutes.valeur_num.alias("x_qte_mvt"), SuiviProdMinutes.variable.alias('x_variable')).where(
        (fn.TIME(SuiviProdMinutes.chrono) >= x_min) & (fn.TIME(SuiviProdMinutes.chrono) <= x_max)))

    l_seuil2 = (l_seuil | l_seuil1 | l_seuil4).order_by('x_chrono')
    l_seuil3 = l_seuil2.order_by(fn.TIME(l_seuil2.c.x_chrono))

    for ls in l_seuil3:

        if (ls.x_variable == 'API_Nbr_de_Pilulier_Med'):
            labels1.append(str(ls.x_chrono)[11:19])
            labels.append(str(ls.x_chrono)[11:19])
            data1.append(ls.x_qte_mvt)
            data3.append(None)
            data2.append(None)
            data4.append(None)

        elif ls.x_variable == 'API_Nbr_Carnet_Pilulier_Med':
            labels1.append(str(ls.x_chrono)[11:19])
            labels.append(str(ls.x_chrono)[11:19])
            data4.append(ls.x_qte_mvt)
            data1.append(None)
            data2.append(None)
            data3.append(None)

        elif ls.x_variable == 'API_Nbr_Produit_Pilulier_Med':
            labels1.append(str(ls.x_chrono)[11:19])
            labels.append(str(ls.x_chrono)[11:19])
            data3.append(ls.x_qte_mvt)
            data1.append(None)
            data2.append(None)
            data4.append(None)

            # if (last_API_Nbr_Produit_Pilulier_Med < ls.x_qte_mvt):
            #     last_API_Nbr_Produit_Pilulier_Med = ls.x_qte_mvt

        elif (ls.x_variable == 'API_Nbr_de_Produit_Carnet_Med'):
            labels1.append(str(ls.x_chrono)[11:19])
            labels.append(str(ls.x_chrono)[11:19])
            data2.append(ls.x_qte_mvt)
            data1.append(None)
            data3.append(None)
            data4.append(None)

            # if (last_API_Nbr_de_Produit_Carnet_Med < ls.x_qte_mvt):
            #     last_API_Nbr_de_Produit_Carnet_Med = ls.x_qte_mvt
        else:
            labels.append(str(ls.x_chrono)[11:19])
            # data.append(ls.x_qte_mvt)
            data1.append(None)
            data2.append(None)
            data.append(None)
            data3.append(None)
            data4.append(None)

    # for ls in l_seuil1:
    #     labels1.append(ls.x_chrono)
    #     data1.append(ls.x_qte_mvt)
    #     labels.append(ls.x_chrono)
    #     data.append('')

    data = {
        'magasin': v_mag,
        'labels': labels,
        'data': data,
        'labels1': labels1,
        'data1': data1,
        'data2': data2,
        'data3': data3,
        'data4': data4,
    }
    return data


@acced_production_blueprint.route('counters', methods=['GET'])
@jwt_required()
def get():
    args = request.args
    v_mag = args['mag']
    v_date = args['date']

    CPT_Goulotte = 0
    CPT_Coupe = 0
    CPT_BoitePassCP = 0
    CPT_Barillet = 0
    CPT_BoitePassBarillet = 0
    CPT_Chargement = 0
    CPT_BoiteStock = 0
    CPT_BoitePassPrépaDirecte = 0
    CPT_BoiteRejetDpdE1 = 0
    CPT_BoiteRejetDlectE1 = 0
    CPT_BoiteRejetErrAxeE1 = 0
    CPT_BoiteRejetDpsE1 = 0
    CPT_BoiteRejetTestE1 = 0
    CPT_BoiteRejetErrTestE1 = 0
    CPT_BoiteRejetDpdE2 = 0
    CPT_BoiteRejetDlectE2 = 0
    CPT_BoiteRejetErrAxeE2 = 0
    CPT_BoiteRejetDpsE2 = 0
    CPT_BoiteRejetTestE2 = 0
    CPT_BoiteRejetErrTestE2 = 0

    API_Nbr_de_Pilulier_Med = 0
    API_Nbr_de_Plateau_Med = 0

    API_Nbr_Carnet_Pilulier_Med = 0
    API_Nbr_de_Plateau_Carnet_Med = 0

    API_Nbr_de_Pilulier_Carnet_Med = 0

    API_Nbr_Blister_Charge_Med = 0
    API_Nbr_Goulotte_Chargee_Med = 0
    API_Nbr_Erreur_Goulotte_Med = 0
    API_Nbr_Blister_pris_Med = 0
    API_Nbr_Rejet_Blister_RB2_Med = 0
    API_Nbr_Rejet_Sachet_RB2_Med = 0
    API_Nbr_Rejet_SachetP_RB2_Med = 0
    API_Nbr_Rejet_Blister_RB1_Med = 0
    API_Nbr_Rejet_Sachet_RB1_Med = 0
    API_Nbr_Rejet_SachetP_RB1_Med = 0
    API_Nbr_Rejet_DU_Med = 0
    API_Nbr_Rejet_Ticket_Med = 0
    API_Nbr_Sachet_Pose_Med = 0
    API_Nbr_SachetP_Pose_Med = 0
    API_Nbr_DU_Pose_Med = 0
    API_Nbr_Ticket_Pose_Med = 0
    API_Nbr_Sachet_Pose_Carnet_Med = 0
    API_Nbr_SachetP_Pose_Carnet_Med = 0
    API_Nbr_Du_Pose_Carnet_Med = 0
    API_Nbr_Ticket_Pose_Carnet_Med = 0
    API_Nbr_Carnet_Pose_Med = 0
    API_Nbr_Coupe_Med = 0
    API_Nbr_BoitePass_RECU_Med = 0
    API_Nbr_BoitePass_CP_Med = 0
    API_Nbr_BoitePass_Barillet_Med = 0
    API_Nbr_Barillet_Med = 0
    API_Nbr_BoiteStock_Med = 0
    API_Nbr_BoitePass_PrepaDir_Med = 0
    API_Nbr_Rejet_Dpd_E1_Med = 0
    API_Nbr_Rejet_Dlect_E1_Med = 0
    API_Nbr_Rejet_Dps_E1_Med = 0
    API_Nbr_Rejet_Test_E1_Med = 0
    API_Nbr_Rejet_ErrTest_E1_Med = 0
    API_Nbr_Rejet_Dpd_E2_Med = 0
    API_Nbr_Rejet_Dlect_E2_Med = 0
    API_Nbr_Rejet_Dps_E2_Med = 0
    API_Nbr_Rejet_Test_E2_Med = 0
    API_Nbr_Rejet_ErrTest_E2_Med = 0
    API_Temps_Chgt_Blister_Med = 0
    API_Temps_Dchgt_Plateau_Med = 0
    API_Temps_Cueil_Pil_Med = 0
    API_Temps_Cueil_Carnet_Med = 0
    API_Nbr_Coupe_Heure_Med = 0
    API_Nbr_RetourProduit_Med = 0
    API_Nbr_Dose_Rangee_Med = 0
    API_Nbr_Dose_Rangee_Heure_Med = 0
    API_Temps_Panne_Med = 0
    API_TEMPS_ATTENTE_OPERATEUR_Med = 0
    API_TEMPS_ATTENTE_ECRAN_Med = 0
    API_TEMPS_RESOLUTION_DEFAUT_Med = 0
    API_TEMPS_UTILISATION_Med = 0
    API_TEMPS_CYCLE_REEL_Med = 0
    API_DISPONIBILITE_Med = 0

    API_Nbr_de_Pilulier_heure_Med = 0
    API_Nbr_de_Pilulier_Carnet_heure_Med = 0

    Nb_doses_Pilulier = 0
    Nb_doses_Carnet = 0

    date_fin = datetime.datetime.strptime(v_date, "%Y-%m-%d") + datetime.timedelta(days=1)

    # subquery = (SuiviProd
    #     .select(
    #         SuiviProd.variable.alias('variable'),
    #         fn.MAX(SuiviProd.pk).alias('max_pk'))
    #         .where((SuiviProd.poste == v_mag) & (
    #             fn.Substr(SuiviProd.chrono, 1, 10) == v_date))
    #         .group_by(SuiviProd.variable))

    subquery = (SuiviProd.select(SuiviProd.variable.alias('variable'),
                                 fn.MAX(SuiviProd.pk).alias('max_pk'))
                .where((SuiviProd.poste == v_mag) &
                       (SuiviProd.chrono >= v_date) &
                       (SuiviProd.chrono < date_fin))
                .group_by(SuiviProd.variable))

    compteur = (SuiviProd
                .select(SuiviProd.pk, SuiviProd.variable, SuiviProd.valeur_num,
                        SuiviProd.valeur_alpha)
                .join(subquery, on=((subquery.c.max_pk == SuiviProd.pk) &
                                    (subquery.c.variable == SuiviProd.variable))))

    for var in compteur:
        if var.variable == "CPT_Goulotte":
            CPT_Goulotte = var.valeur_num
        elif var.variable == "CPT_Coupe":
            CPT_Coupe = var.valeur_num
        elif var.variable == "CPT_BoitePassCP":
            CPT_BoitePassCP = var.valeur_num
        elif var.variable == "CPT_Barillet":
            CPT_Barillet = var.valeur_num
        elif var.variable == "CPT_BoitePassBarillet":
            CPT_BoitePassBarillet = var.valeur_num
        elif var.variable == "CPT_Chargement":
            CPT_Chargement = var.valeur_num
        elif var.variable == "CPT_BoiteStock":
            CPT_BoiteStock = var.valeur_num
        elif var.variable == "CPT_BoitePassPrépaDirecte":
            CPT_BoitePassPrépaDirecte = var.valeur_num
        elif var.variable == "CPT_BoiteRejetDpdE1":
            CPT_BoiteRejetDpdE1 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetDlectE1":
            CPT_BoiteRejetDlectE1 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetErrAxeE1":
            CPT_BoiteRejetErrAxeE1 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetDpsE1":
            CPT_BoiteRejetDpsE1 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetTestE1":
            CPT_BoiteRejetTestE1 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetErrTestE1":
            CPT_BoiteRejetErrTestE1 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetDpdE2":
            CPT_BoiteRejetDpdE2 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetDlectE2":
            CPT_BoiteRejetDlectE2 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetErrAxeE2":
            CPT_BoiteRejetErrAxeE2 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetDpsE2":
            CPT_BoiteRejetDpsE2 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetTestE2":
            CPT_BoiteRejetTestE2 = var.valeur_num
        elif var.variable == "CPT_BoiteRejetErrTestE2":
            CPT_BoiteRejetErrTestE2 = var.valeur_num
        elif var.variable == "API_Nbr_de_Pilulier_Med":
            API_Nbr_de_Pilulier_Med = var.valeur_num
        elif var.variable == "API_Nbr_de_Plateau_Med":
            API_Nbr_de_Plateau_Med = var.valeur_num
        elif var.variable == "API_Nbr_Carnet_Pilulier_Med":
            API_Nbr_Carnet_Pilulier_Med = var.valeur_num
        elif var.variable == "API_Nbr_de_Plateau_Carnet_Med":
            API_Nbr_de_Plateau_Carnet_Med = var.valeur_num
        elif var.variable == "API_Nbr_de_Pilulier_Carnet_Med":
            API_Nbr_de_Pilulier_Carnet_Med = var.valeur_num
        elif var.variable == "API_Nbr_Blister_Charge_Med":
            API_Nbr_Blister_Charge_Med = var.valeur_num
        elif var.variable == "API_Nbr_Goulotte_Chargee_Med":
            API_Nbr_Goulotte_Chargee_Med = var.valeur_num
        elif var.variable == "API_Nbr_Erreur_Goulotte_Med":
            API_Nbr_Erreur_Goulotte_Med = var.valeur_num
        elif var.variable == "API_Nbr_Blister_pris_Med":
            API_Nbr_Blister_pris_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Blister_RB2_Med":
            API_Nbr_Rejet_Blister_RB2_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Sachet_RB2_Med":
            API_Nbr_Rejet_Sachet_RB2_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_SachetP_RB2_Med":
            API_Nbr_Rejet_SachetP_RB2_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Blister_RB1_Med":
            API_Nbr_Rejet_Blister_RB1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Sachet_RB1_Med":
            API_Nbr_Rejet_Sachet_RB1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_SachetP_RB1_Med":
            API_Nbr_Rejet_SachetP_RB1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_DU_Med":
            API_Nbr_Rejet_DU_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Ticket_Med":
            API_Nbr_Rejet_Ticket_Med = var.valeur_num
        elif var.variable == "API_Nbr_Sachet_Pose_Med":
            API_Nbr_Sachet_Pose_Med = var.valeur_num
        elif var.variable == "API_Nbr_SachetP_Pose_Med":
            API_Nbr_SachetP_Pose_Med = var.valeur_num
        elif var.variable == "API_Nbr_DU_Pose_Med":
            API_Nbr_DU_Pose_Med = var.valeur_num
        elif var.variable == "API_Nbr_Ticket_Pose_Med":
            API_Nbr_Ticket_Pose_Med = var.valeur_num
        elif var.variable == "API_Nbr_Sachet_Pose_Carnet_Med":
            API_Nbr_Sachet_Pose_Carnet_Med = var.valeur_num
        elif var.variable == "API_Nbr_SachetP_Pose_Carnet_Med":
            API_Nbr_SachetP_Pose_Carnet_Med = var.valeur_num
        elif var.variable == "API_Nbr_Du_Pose_Carnet_Med":
            API_Nbr_Du_Pose_Carnet_Med = var.valeur_num
        elif var.variable == "API_Nbr_Ticket_Pose_Carnet_Med":
            API_Nbr_Ticket_Pose_Carnet_Med = var.valeur_num
        elif var.variable == "API_Nbr_Carnet_Pose_Med":
            API_Nbr_Carnet_Pose_Med = var.valeur_num
        elif var.variable == "API_Nbr_Coupe_Med":
            API_Nbr_Coupe_Med = var.valeur_num
        elif var.variable == "API_Nbr_BoitePass_RECU_Med":
            API_Nbr_BoitePass_RECU_Med = var.valeur_num
        elif var.variable == "API_Nbr_BoitePass_CP_Med":
            API_Nbr_BoitePass_CP_Med = var.valeur_num
        elif var.variable == "API_Nbr_BoitePass_Barillet_Med":
            API_Nbr_BoitePass_Barillet_Med = var.valeur_num
        elif var.variable == "API_Nbr_Barillet_Med":
            API_Nbr_Barillet_Med = var.valeur_num
        elif var.variable == "API_Nbr_BoiteStock_Med":
            API_Nbr_BoiteStock_Med = var.valeur_num
        elif var.variable == "API_Nbr_BoitePass_PrepaDir_Med":
            API_Nbr_BoitePass_PrepaDir_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Dpd_E1_Med":
            API_Nbr_Rejet_Dpd_E1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Dlect_E1_Med":
            API_Nbr_Rejet_Dlect_E1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Dps_E1_Med":
            API_Nbr_Rejet_Dps_E1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Test_E1_Med":
            API_Nbr_Rejet_Test_E1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_ErrTest_E1_Med":
            API_Nbr_Rejet_ErrTest_E1_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Dpd_E2_Med":
            API_Nbr_Rejet_Dpd_E2_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Dlect_E2_Med":
            API_Nbr_Rejet_Dlect_E2_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Dps_E2_Med":
            API_Nbr_Rejet_Dps_E2_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_Test_E2_Med":
            API_Nbr_Rejet_Test_E2_Med = var.valeur_num
        elif var.variable == "API_Nbr_Rejet_ErrTest_E2_Med":
            API_Nbr_Rejet_ErrTest_E2_Med = var.valeur_num
        elif var.variable == "API_Temps_Chgt_Blister_Med":
            time = var.valeur_num
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_Temps_Chgt_Blister_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_Temps_Dchgt_Plateau_Med":
            time = var.valeur_num
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_Temps_Dchgt_Plateau_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_Temps_Cueil_Pil_Med":
            time = var.valeur_num
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_Temps_Cueil_Pil_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_Temps_Cueil_Carnet_Med":
            time = var.valeur_num
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_Temps_Cueil_Carnet_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_Nbr_Coupe_Heure_Med":
            API_Nbr_Coupe_Heure_Med = var.valeur_num
        elif var.variable == "API_Nbr_RetourProduit_Med":
            API_Nbr_RetourProduit_Med = var.valeur_num
        elif var.variable == "API_Nbr_Dose_Rangee_Med":
            API_Nbr_Dose_Rangee_Med = var.valeur_num
        elif var.variable == "API_Nbr_Dose_Rangee_Heure_Med":
            API_Nbr_Dose_Rangee_Heure_Med = var.valeur_num
        elif var.variable == "API_Temps_Panne_Med":
            time = var.valeur_num
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_Temps_Panne_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_TEMPS_ATTENTE_OPERATEUR_Med":
            time = var.valeur_num / 3600
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_TEMPS_ATTENTE_OPERATEUR_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_TEMPS_ATTENTE_ECRAN_Med":
            time = var.valeur_num / 3600
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_TEMPS_ATTENTE_ECRAN_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_TEMPS_RESOLUTION_DEFAUT_Med":
            time = var.valeur_num / 3600
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_TEMPS_RESOLUTION_DEFAUT_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_TEMPS_UTILISATION_Med":
            time = var.valeur_num / 3600
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_TEMPS_UTILISATION_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_TEMPS_CYCLE_REEL_Med":
            time = var.valeur_num / 3600
            hours = int(time)
            minutes = (time * 60) % 60.
            seconds = (time * 3600) % 60.
            API_TEMPS_CYCLE_REEL_Med = "%02d:%02d:%02d" % (hours, minutes, seconds)
        elif var.variable == "API_DISPONIBILITE_Med":
            API_DISPONIBILITE_Med = var.valeur_num
        elif var.variable == "API_Nbr_de_Pilulier_heure_Med":
            API_Nbr_de_Pilulier_heure_Med = round(var.valeur_num, 2)
        elif var.variable == "API_Nbr_Carnet_Pilulier_heure_Med":
            API_Nbr_de_Pilulier_Carnet_heure_Med = round(var.valeur_num, 2)

    # if temps_cueillette_pilulier:
    #     Cadence_Pilulier = round(float(API_Nbr_de_Pilulier_Med) / float(temps_cueillette_pilulier),2)
    # else:
    #     Cadence_Pilulier = 0

    # if temps_cueillette_carnet:
    #     Cadence_Carnet = round(float(API_Nbr_Carnet_Pose_Med) / float(temps_cueillette_carnet),2)
    # else:
    #     Cadence_Carnet = 0

    if API_Nbr_de_Pilulier_Med != 0:
        Nb_doses_Pilulier = ((API_Nbr_DU_Pose_Med + API_Nbr_Sachet_Pose_Med + API_Nbr_SachetP_Pose_Med)
                             / API_Nbr_de_Pilulier_Med)
        Nb_doses_Pilulier = round(Nb_doses_Pilulier, 2)
    else:
        Nb_doses_Pilulier = 0

    if API_Nbr_Carnet_Pilulier_Med != 0:
        Nb_doses_Carnet = ((API_Nbr_Sachet_Pose_Carnet_Med + API_Nbr_SachetP_Pose_Carnet_Med
                            + API_Nbr_Du_Pose_Carnet_Med) / API_Nbr_Carnet_Pilulier_Med)
        Nb_doses_Carnet = round(Nb_doses_Carnet, 2)
    else:
        Nb_doses_Carnet = 0

    return {
        'type_mag': v_mag,
        'CPT_Goulotte': CPT_Goulotte,
        'CPT_Coupe': CPT_Coupe,
        'CPT_BoitePassCP': CPT_BoitePassCP,
        'CPT_Barillet': CPT_Barillet,
        'CPT_BoitePassBarillet': CPT_BoitePassBarillet,
        'CPT_Chargement': CPT_Chargement,
        'CPT_BoiteStock': CPT_BoiteStock,
        'CPT_BoitePassPrépaDirecte': CPT_BoitePassPrépaDirecte,
        'CPT_BoiteRejetDpdE1': CPT_BoiteRejetDpdE1,
        'CPT_BoiteRejetDlectE1': CPT_BoiteRejetDlectE1,
        'CPT_BoiteRejetErrAxeE1': CPT_BoiteRejetErrAxeE1,
        'CPT_BoiteRejetDpsE1': CPT_BoiteRejetDpsE1,
        'CPT_BoiteRejetTestE1': CPT_BoiteRejetTestE1,
        'CPT_BoiteRejetErrTestE1': CPT_BoiteRejetErrTestE1,
        'CPT_BoiteRejetDpdE2': CPT_BoiteRejetDpdE2,
        'CPT_BoiteRejetDlectE2': CPT_BoiteRejetDlectE2,
        'CPT_BoiteRejetErrAxeE2': CPT_BoiteRejetErrAxeE2,
        'CPT_BoiteRejetDpsE2': CPT_BoiteRejetDpsE2,
        'CPT_BoiteRejetTestE2': CPT_BoiteRejetTestE2,
        'CPT_BoiteRejetErrTestE2': CPT_BoiteRejetErrTestE2,
        'API_Nbr_de_Pilulier_Med': API_Nbr_de_Pilulier_Med,
        'API_Nbr_de_Plateau_Med': API_Nbr_de_Plateau_Med,
        'API_Nbr_Carnet_Pilulier_Med': API_Nbr_Carnet_Pilulier_Med,
        'API_Nbr_de_Plateau_Carnet_Med': API_Nbr_de_Plateau_Carnet_Med,
        'API_Nbr_de_Pilulier_Carnet_Med': API_Nbr_de_Pilulier_Carnet_Med,
        'API_Nbr_Blister_Charge_Med': API_Nbr_Blister_Charge_Med,
        'API_Nbr_Goulotte_Chargee_Med': API_Nbr_Goulotte_Chargee_Med,
        'API_Nbr_Erreur_Goulotte_Med': API_Nbr_Erreur_Goulotte_Med,
        'API_Nbr_Blister_pris_Med': API_Nbr_Blister_pris_Med,
        'API_Nbr_Rejet_Blister_RB2_Med': API_Nbr_Rejet_Blister_RB2_Med,
        'API_Nbr_Rejet_Sachet_RB2_Med': API_Nbr_Rejet_Sachet_RB2_Med,
        'API_Nbr_Rejet_SachetP_RB2_Med': API_Nbr_Rejet_SachetP_RB2_Med,
        'API_Nbr_Rejet_Blister_RB1_Med': API_Nbr_Rejet_Blister_RB1_Med,
        'API_Nbr_Rejet_Sachet_RB1_Med': API_Nbr_Rejet_Sachet_RB1_Med,
        'API_Nbr_Rejet_SachetP_RB1_Med': API_Nbr_Rejet_SachetP_RB1_Med,
        'API_Nbr_Rejet_DU_Med': API_Nbr_Rejet_DU_Med,
        'API_Nbr_Rejet_Ticket_Med': API_Nbr_Rejet_Ticket_Med,
        'API_Nbr_Sachet_Pose_Med': API_Nbr_Sachet_Pose_Med,
        'API_Nbr_SachetP_Pose_Med': API_Nbr_SachetP_Pose_Med,
        'API_Nbr_DU_Pose_Med': API_Nbr_DU_Pose_Med,
        'API_Nbr_Ticket_Pose_Med': API_Nbr_Ticket_Pose_Med,
        'API_Nbr_Sachet_Pose_Carnet_Med': API_Nbr_Sachet_Pose_Carnet_Med,
        'API_Nbr_SachetP_Pose_Carnet_Med': API_Nbr_SachetP_Pose_Carnet_Med,
        'API_Nbr_Du_Pose_Carnet_Med': API_Nbr_Du_Pose_Carnet_Med,
        'API_Nbr_Ticket_Pose_Carnet_Med': API_Nbr_Ticket_Pose_Carnet_Med,
        'API_Nbr_Carnet_Pose_Med': API_Nbr_Carnet_Pose_Med,
        'API_Nbr_Coupe_Med': API_Nbr_Coupe_Med,
        'API_Nbr_BoitePass_RECU_Med': API_Nbr_BoitePass_RECU_Med,
        'API_Nbr_BoitePass_CP_Med': API_Nbr_BoitePass_CP_Med,
        'API_Nbr_BoitePass_Barillet_Med': API_Nbr_BoitePass_Barillet_Med,
        'API_Nbr_Barillet_Med': API_Nbr_Barillet_Med,
        'API_Nbr_BoiteStock_Med': API_Nbr_BoiteStock_Med,
        'API_Nbr_BoitePass_PrepaDir_Med': API_Nbr_BoitePass_PrepaDir_Med,
        'API_Nbr_Rejet_Dpd_E1_Med': API_Nbr_Rejet_Dpd_E1_Med,
        'API_Nbr_Rejet_Dlect_E1_Med': API_Nbr_Rejet_Dlect_E1_Med,
        'API_Nbr_Rejet_Dps_E1_Med': API_Nbr_Rejet_Dps_E1_Med,
        'API_Nbr_Rejet_Test_E1_Med': API_Nbr_Rejet_Test_E1_Med,
        'API_Nbr_Rejet_ErrTest_E1_Med': API_Nbr_Rejet_ErrTest_E1_Med,
        'API_Nbr_Rejet_Dpd_E2_Med': API_Nbr_Rejet_Dpd_E2_Med,
        'API_Nbr_Rejet_Dlect_E2_Med': API_Nbr_Rejet_Dlect_E2_Med,
        'API_Nbr_Rejet_Dps_E2_Med': API_Nbr_Rejet_Dps_E2_Med,
        'API_Nbr_Rejet_Test_E2_Med': API_Nbr_Rejet_Test_E2_Med,
        'API_Nbr_Rejet_ErrTest_E2_Med': API_Nbr_Rejet_ErrTest_E2_Med,
        'API_Temps_Chgt_Blister_Med': API_Temps_Chgt_Blister_Med,
        'API_Temps_Dchgt_Plateau_Med': API_Temps_Dchgt_Plateau_Med,
        'API_Temps_Cueil_Pil_Med': API_Temps_Cueil_Pil_Med,
        'API_Temps_Cueil_Carnet_Med': API_Temps_Cueil_Carnet_Med,
        'API_Nbr_Coupe_Heure_Med': API_Nbr_Coupe_Heure_Med,
        'API_Nbr_RetourProduit_Med': API_Nbr_RetourProduit_Med,
        'API_Nbr_Dose_Rangee_Med': API_Nbr_Dose_Rangee_Med,
        'API_Nbr_Dose_Rangee_Heure_Med': API_Nbr_Dose_Rangee_Heure_Med,
        'API_Temps_Panne_Med': API_Temps_Panne_Med,
        'API_TEMPS_ATTENTE_OPERATEUR_Med': API_TEMPS_ATTENTE_OPERATEUR_Med,
        'API_TEMPS_ATTENTE_ECRAN_Med': API_TEMPS_ATTENTE_ECRAN_Med,
        'API_TEMPS_RESOLUTION_DEFAUT_Med': API_TEMPS_RESOLUTION_DEFAUT_Med,
        'API_TEMPS_UTILISATION_Med': API_TEMPS_UTILISATION_Med,
        'API_TEMPS_CYCLE_REEL_Med': API_TEMPS_CYCLE_REEL_Med,
        'API_DISPONIBILITE_Med': API_DISPONIBILITE_Med,
        'Cadence_Pilulier': API_Nbr_de_Pilulier_heure_Med,
        'Cadence_Carnet': API_Nbr_de_Pilulier_Carnet_heure_Med,
        'Nb_doses_Pilulier': Nb_doses_Pilulier,
        'Nb_doses_Carnet': Nb_doses_Carnet
    }
