SET ECHO ON

CREATE TABLE employee (
  employee_id               NUMBER,
  employee_name             VARCHAR2(40),
  employee_hire_date        DATE DEFAULT sysdate,
  employee_termination_date DATE,
  employee_billing_rate     NUMBER(5,2),
  CONSTRAINT employee_pk
    PRIMARY KEY (employee_id)
);

CREATE TABLE project (
  project_id              NUMBER(4),
  project_name            VARCHAR2(40),
  project_budget          NUMBER(9,2),
  CONSTRAINT project_pk
    PRIMARY KEY (project_id)
);

CREATE TABLE project_hours (
  project_id              NUMBER(4),
  employee_id             NUMBER,
  time_log_date           DATE,
  hours_logged            NUMBER(5,2),
  dollars_charged         NUMBER(8,2),
  CONSTRAINT project_hours_pk
    PRIMARY KEY (project_id, employee_id, time_log_date),
  CONSTRAINT proj_hours_fkto_employee
    FOREIGN KEY (employee_id) REFERENCES employee,
  CONSTRAINT proj_hours_fkto_project
    FOREIGN KEY (project_id) REFERENCES project
);

INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1001, 'Corporate Web Site',1912000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1002, 'Enterprise Resource Planning System',9999999);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1003, 'Accounting System Implementation',897000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1004, 'Data Warehouse Maintenance',294000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1005, 'VPN Implementation',415000);



INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (101, 'Marusia Churai', to_date('15-Nov-1961','dd-mon-yyyy')
         ,null,169);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (102, 'Mykhailo Hrushevsky', to_date('16-Sep-1964','dd-mon-yyyy')
         ,to_date('5-May-2004','dd-mon-yyyy'),135);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (104, 'Pavlo Virsky', to_date('29-Dec-1987','dd-mon-yyyy')
         ,to_date('1-Apr-2004','dd-mon-yyyy'),99);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (105, 'Mykola Leontovych', to_date('15-Jun-2004','dd-mon-yyyy')
         ,null,121);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (107, 'Lesia Ukrainka', to_date('2-Jan-2004','dd-mon-yyyy')
         ,null,45);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (108, 'Pavlo Chubynsky', to_date('1-Mar-1994','dd-mon-yyyy')
         ,to_date('15-Nov-2004','dd-mon-yyyy'),220);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (110, 'Ivan Mazepa', to_date('4-Apr-2004','dd-mon-yyyy')
         ,to_date('30-Sep-2004','dd-mon-yyyy'),84);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (111, 'Taras Shevchenko', to_date('23-Aug-1976','dd-mon-yyyy')
         ,null,100);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (112, 'Igor Sikorsky', to_date('15-Nov-1961','dd-mon-yyyy')
         ,to_date('4-Apr-2004','dd-mon-yyyy'),70);
INSERT INTO employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (113, 'Mykhailo Verbytsky', to_date('3-Mar-2004','dd-mon-yyyy')
         ,to_date('31-Oct-2004','dd-mon-yyyy'),300);

--Generate project_hours data
DECLARE
  --Declare the table type and table used to hold the list of project IDs.
  TYPE project_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  project_table   project_table_type;

  projectx    BINARY_INTEGER;   --Index into project_table
  project_max BINARY_INTEGER;   --The number of projects in project_table.

  log_entry_counter BINARY_INTEGER; --Number of entries created in project_hours.
  hours_to_log      BINARY_INTEGER; --Number of hours for a particular entry.

  first_date  DATE;     --First date for which to log time.
  last_date   DATE;     --Last date for which to log time.
  time_date   DATE;     --The date to use when logging time

  --This cursor is used to fetch the list of employees
  --in order to allow us to generate timelog records.
  CURSOR employee_cursor IS
    SELECT employee_id, employee_hire_date,
           employee_termination_date, employee_billing_rate
      FROM employee;

  --This cursor is used to fetch the entire list of valid project IDs.
  CURSOR project_cursor IS
    SELECT project_id
      FROM project;

BEGIN
  --Retrieve the entire list of project IDs.
  project_max := 0;   --To start with there are zero projects in the list.

  FOR project IN project_cursor LOOP
    project_max := project_max + 1;
    project_table(project_max) := project.project_id;
  END LOOP;   --FOR EACH PROJECT

  --For each employee we are going to generate
  --one timelog entry for each project for
  --each month worked during 2004.
  log_entry_counter := 0;
  FOR employee IN employee_cursor LOOP
    --Figure out the first date the employee worked in 2004.
    first_date := employee.employee_hire_date;
    IF (TRUNC(first_date,'yyyy') < to_date('1-Jan-2004','dd-mon-yyyy'))
    OR (first_date is null) THEN
      first_date := to_date('1-Jan-2004','dd-mon-yyyy');
    END IF;

    --Figure out the last date the employee worked in 2004.
    last_date := employee.employee_termination_date;
    IF (TRUNC(last_date,'yyyy') > to_date('1-Jan-2004','dd-mon-yyyy'))
    OR (last_date is null) THEN
      last_date := to_date('31-Dec-2004','dd-mon-yyyy');
    END IF;

    --dbms_output.put_line('Employee ' || employee.employee_id);
    --dbms_output.put_line(' last_date ' || to_char(last_date));

    --Now generate one timelog record per project for each
    --month worked during 2004.
    time_date := first_date;
    log_entry_counter := 1;
    WHILE time_date <= last_date LOOP
      --dbms_output.put_line(' time_date ' || to_char(time_date));
      FOR projectx in 1..project_max LOOP
        --Come up w/a semi-random number of hours to log for this employee.
        hours_to_log := log_entry_counter mod 8;
        log_entry_counter := log_entry_counter + 1;
        IF hours_to_log IN (1,3,5,7) then
          INSERT INTO project_hours
            (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (project_table(projectx), employee.employee_id, time_date
                   ,hours_to_log, hours_to_log*employee.employee_billing_rate);
        END IF;
      END LOOP; -- FOR EACH PROJECT
      time_date := ADD_MONTHS(time_date,1);   --add one month
    END LOOP;   -- WHILE
  END LOOP;   --FOR EACH EMPLOYEE

  --Commit all the inserts.
  COMMIT;
END;
/

COMMIT;
EXIT;