create table contactDirectory
(CONT_TYPE int,
CONT_NAME varchar(255),
CONSTRAINT pk_contactDirectory PRIMARY KEY (CONT_TYPE)
);

create table empContactData
(ldap varchar(255),
CONT_MAIN int,
CONT_TYPE int,
CONT_VALUE varchar(255),
CONSTRAINT pk_empContactData PRIMARY KEY (ldap),
CONSTRAINT fk_CONT_TYPE FOREIGN KEY (CONT_TYPE)
REFERENCES contactDirectory (CONT_TYPE)
);

create table empData
(ldap_oper varchar(255),
LDAP_LINE_MANAGER varchar(255),
CONSTRAINT fk_ldap FOREIGN KEY (ldap_oper)
REFERENCES empContactData (ldap)
);

create table empHistData
(date_create date,
ldap_oper varchar(255),
PEOP_STATE enum('болеет','в отпуске','в командировке','уволен','увольняется','оформляется','работает','стажируется'),
CONSTRAINT fk_ldap_Hist FOREIGN KEY (ldap_oper)
REFERENCES empContactData (ldap)
);

create table data_dialogs
(date_create date,
ldap_oper varchar(255),
oper_fio varchar(255),
totalDialogs int,
totalAcceptedDialogs int,
RPBranch char(4),
RPName varchar(255),
CONSTRAINT fk_ldap_d_dial FOREIGN KEY (ldap_oper)
REFERENCES empContactData (ldap)
);

select a.ldap_oper, a.oper_fio, a.RPBranch, a.RPName, a.totalDialogs, SUM(a.totalAcceptedDialogs), (SUM(a.totalAcceptedDialogs)*100/a.totalDialogs) AS reyt, m.LDAP_LINE_MANAGER, c.CONT_VALUE
from data_dialogs a JOIN empData m
ON a.ldap_oper = m.ldap_oper
JOIN empContactData c
ON m.LDAP_LINE_MANAGER = c.ldap
JOIN empHistData h_l
ON a.ldap_oper = h_l.ldap_oper
JOIN empHistData h_d
ON a.date_create = h_d.date_create
WHERE MONTH(a.date_create) = MONTH(NOW()) AND YEAR(a.date_create) = YEAR(NOW()) AND c.CONT_MAIN = 1 AND (h_d.PEOP_STATE = 'увольняется' OR 'оформляется' OR 'работает' OR 'стажируется')
GROUP BY a.ldap_oper
ORDER BY reyt;