create table staffreport_ispro_staff_temp as table staffreport_ispro_staff;
delete from staffreport_ispro_staff_temp;
alter table staffreport_ispro_staff_temp drop column id;

create table staffreport_learn_programs_temp as table staffreport_learn_programs;
truncate table staffreport_learn_programs_temp;


create table temp2 as table staffreport_ispro_staff;
delete from temp2;
alter table temp2 drop column id;
copy temp2 from 'c:\ISPRO_StaffForLP.csv' with (FORMAT csv, ENCODING 'WIN1251', HEADER, DELIMITER ';', QUOTE '`');
delete from staffreport_ispro_staff;
insert into staffreport_ispro_staff (id, filial, corp_number, tabele_num, fio, position, order_n, order_date, contract_n, contract_date, protocol_n, protocol_date, science_degree, academic_rank, payroll, category, education_type, specialty, qualification, retraining, experience)
select row_number() over ()             as rownumber,
       filial,
       corp_number,
       tabele_num,
       fio,
       position,
       order_n,
       order_date,
       contract_n,
       contract_date,
       protocol_n,
       protocol_date,
       string_agg(science_degree, ', ') as science_degree,
       academic_rank,
       payroll,
       category,
       string_agg(education_type, ', ') as education_type,
       string_agg(specialty, ', ')      as specialty,
       string_agg(qualification, ', ')  as qualification,
       string_agg(retraining, ', ')     as retraining,
       experience
from temp2
group by filial, corp_number, tabele_num, fio, position, order_n,
         order_date, contract_n, contract_date, protocol_n,
         protocol_date, academic_rank, payroll, category, experience
order by tabele_num;
drop table temp2;


COPY staffreport_teacher_load_temp FROM 'C:\export_TeachersLoad.dsv' WITH (FORMAT csv, HEADER, DELIMITER ';');
truncate table staffreport_teacher_load;
INSERT INTO staffreport_teacher_load (id, id_key, "teachertasktype$d", learnyear, learnprogram, "learnprogram$d", description, "course$d", subjectunit, "learnperiod$d", regdepartment, depcode, facdepartment, facdepartmentid, depfaculty, workerid, lastname, firstname, middlename, birthdate, "position", positioncategory, perscategory, parttime, workercode, tabelenum, depworkerid, academicstatus, "facdepcategory$d", "positioncategory$d", "teachercategory$d", "tasklevel$d", taskvalue, positionpart, resultvalue, planclass, minortype, freeplantype$d)
SELECT ROW_NUMBER() OVER () as rownumber,
       id,
       "teachertasktype$d",
       learnyear,
       learnprogram,
       "learnprogram$d",
       description,
       "course$d",
       subjectunit,
       "learnperiod$d",
       regdepartment,
       depcode,
       facdepartment,
       facdepartmentid,
       depfaculty,
       workerid,
       lastname,
       firstname,
       middlename,
       birthdate,
       "position",
       positioncategory,
       perscategory,
       parttime,
       workercode,
       tabelenum,
       depworkerid,
       academicstatus,
       "facdepcategory$d",
       "positioncategory$d",
       "teachercategory$d",
       "tasklevel$d",
       taskvalue,
       positionpart,
       resultvalue,
       planclass,
       minortype,
       freeplantype$d
FROM staffreport_teacher_load_temp;
DROP TABLE staffreport_teacher_load_temp;
CREATE TABLE staffreport_teacher_load_temp AS TABLE staffreport_teacher_load;
truncate table staffreport_teacher_load_temp;
ALTER TABLE staffreport_teacher_load_temp DROP COLUMN id;
ALTER TABLE staffreport_teacher_load_temp RENAME COLUMN id_key to id;