CREATE OR REPLACE PACKAGE employee_p AS
    CURSOR get_workday_employees
    IS
    SELECT * FROM workday ;

    CURSOR get_employees
    IS
    SELECT * FROM employees;



    PROCEDURE overtimeAndLatenessAccount;

    PROCEDURE Out_Screen(TOSC IN VARCHAR2);
END employee_p;

CREATE OR REPLACE PACKAGE BODY employee_p AS
PROCEDURE overtimeAndLatenessAccount
IS
    counter_1 NUMBER DEFAULT 0;
    counter_2 NUMBER DEFAULT 0;

    lateness_from_emp NUMBER ;
    overtime_from_emp NUMBER ;
    lateness_calc NUMBER DEFAULT 0;
    overtime_calc NUMBER DEFAULT 0;

    salary_from_emp NUMBER;
    min_salary_in_dept NUMBER;
    sum_lateness NUMBER;

    A NUMBER DEFAULT 0;
    B NUMBER DEFAULT 0;
    C NUMBER DEFAULT 0;

BEGIN

    FOR n IN get_workday_employees LOOP
        DBMS_OUTPUT.enable;
        SELECT lateness INTO lateness_from_emp FROM employees e WHERE e.id=n.id_employee;
        SELECT overtime INTO overtime_from_emp FROM employees e WHERE e.id=n.id_employee;
        SELECT salary INTO salary_from_emp FROM employees WHERE id=n.id_employee;
        SELECT MIN(salary) INTO min_salary_in_dept FROM employees WHERE id_department=(SELECT id_department FROM employees WHERE id=n.id_employee);


        IF n.skud=1 THEN
            counter_1:=counter_1 + 1;
        END IF;
        IF n.skud=2 THEN
            counter_2:=counter_2 + 1;
        END IF;

        IF counter_1 = 1 AND n.skud=1 THEN
            IF (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))>540 THEN
                lateness_calc := lateness_calc +(TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))-540;
            ELSE
                overtime_calc := overtime_calc + 540 - (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')));
            END IF;
        ELSIF counter_1 = 2 AND n.skud=1  THEN
            IF (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))>840 THEN
                lateness_calc := lateness_calc +(TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))-840;
            ELSE
                overtime_calc := overtime_calc + 840 - (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')));
            END IF;
        END IF;
        IF counter_2 = 1  AND n.skud=2  THEN
            IF (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))<780 THEN
                lateness_calc := lateness_calc+ 780-(TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')));
            ELSE
                overtime_calc := overtime_calc + (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))-780;
            END IF;
        ELSIF counter_2 = 2  AND n.skud=2  THEN
            IF (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))<1080 THEN
                lateness_calc := lateness_calc+1080-(TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')));
            ELSE
                overtime_calc := overtime_calc + (TO_NUMBER(TO_CHAR(n.date_in_out, 'HH24'))*60+TO_NUMBER(TO_CHAR(n.date_in_out, 'MI')))-1080;
            END IF;
        END IF;

        IF counter_1=2 THEN
            counter_1 := 0;

        END IF;
        IF counter_2=2 THEN
            counter_2 := 0;


            DBMS_OUTPUT.put_line(n.id_employee);

            IF (lateness_from_emp-lateness_calc)<=60 THEN
                A := ROUND(ABS(lateness_from_emp-lateness_calc)/10,0)*0.05;
                DBMS_OUTPUT.put_line('A');

                DBMS_OUTPUT.put_line(A);
            END IF;
            IF (lateness_from_emp-lateness_calc)>60 THEN
                A := 0.5;
                DBMS_OUTPUT.put_line('A');

                DBMS_OUTPUT.put_line(A);
            END IF;
            IF (overtime_from_emp+overtime_calc)>=60 THEN
                A := ROUND((overtime_from_emp+overtime_calc)/60,0)*1.1;
                DBMS_OUTPUT.put_line('A');

                DBMS_OUTPUT.put_line(A);
            END IF;

            IF salary_from_emp=min_salary_in_dept THEN
                B := 500;
                DBMS_OUTPUT.put_line('B');

                DBMS_OUTPUT.put_line(B);
            ELSE
                B := 200;
                DBMS_OUTPUT.put_line('B');

                DBMS_OUTPUT.put_line(B);
            END IF;
            --ЗАРПЛАТА=OKLAD + OKLAD*A + B + C
            UPDATE employees
            SET lateness=lateness_from_emp-lateness_calc, overtime=overtime_from_emp+overtime_calc,
            payment=salary_from_emp+salary_from_emp*A+B
            WHERE id=n.id_employee;

            lateness_calc := 0;
            overtime_calc := 0;
         END IF;
    END LOOP;

    FOR n IN get_employees LOOP
        SELECT SUM(lateness) INTO sum_lateness FROM employees WHERE id_department=(SELECT id_department FROM employees WHERE id=n.id);
        DBMS_OUTPUT.put_line(n.id);

        SELECT payment INTO salary_from_emp FROM employees WHERE n.id=id;
        IF sum_lateness=0 THEN
            C := 300;
            DBMS_OUTPUT.put_line('C');

            DBMS_OUTPUT.put_line(C);
        END IF;

        DBMS_OUTPUT.put_line('БЕЗ С:');

        DBMS_OUTPUT.put_line(salary_from_emp);

        UPDATE employees
        SET payment=salary_from_emp+C
        WHERE id=n.id;
        C := 0;
    END LOOP;

END overtimeAndLatenessAccount;

PROCEDURE Out_Screen(TOSC IN VARCHAR2)
IS

BEGIN

DBMS_OUTPUT.enable;
DBMS_OUTPUT.put_line(TOSC);

END Out_Screen;

END employee_p;