大家讨论一下好吗?

Question

/*************
程式設計思路：employee_code記錄公司員工的信息base_code記錄在打卡記錄中各個flag的意思和意義,如:
code_type 紀錄是請假、加班還是沒有打卡有上級領導簽字
code_name 記錄請什麼假，或加什麼班或上級領導簽字的類型class_master、class_detail這兩個表記錄班表的信息
class_master記錄班表的主信息
class_detail記錄
1、這個班次是否需要上班，因為根據循環時間隻能有固定的休息時間，在國家法定的一些休息時間的信息記錄在這個字段上
2、員工這個班次上班的時間
3、員工這個班次下班的時間employee_card記錄員工打卡的信息，包括請假的信息，加班的信息以及沒有打卡領導簽字的信息
請假、加班的時間可以根據打卡的記錄算出在employee_card表中記錄打卡的有效時間，程式中不考慮一天打多次卡取哪個時間等細節employee_furlough記錄員工請假的信息
furlough記錄員工請假的類型和與請假有關的一些信息employee_overtime記錄員工加班的信息
overtime記錄員工加班類型和與加班相關的一些信息在員工不需要打卡，員工加班和請假的信息從以上四個表中讀取
若員工需要打卡，員工加班和請假的信息根據以上四個表的資料從員工的打卡記錄中讀取員工實際加班和請假的時間.程式處理以下機種狀況：
1、公司不要求員工上下班打卡，員工的請假和加班信息根據請假單和加班單計算
系統讀取班表信息以及員工的加班單和請假單信息
對於加班和請假信息，系統根據加班單和請假單，統計出各種加班/請假類型相應的總時間2、公司要求員工上班打卡下班不打卡或上班不打卡下班打卡，員工的請假加班信息根據請假單和加班單計算
系統讀取班表信息、員工打卡信息以及員工的加班和請假單信息
對於加班和請假信息，系統根據加班單和請假單，統計出各種加班/請假類型相應的總時間3、公司要求員工上班和下班都要打卡，員工的請假和加班信息讀取請假單加班單的信息，並根據打卡記錄讀取加班請假的實際時間
系統讀取班表信息、員工打卡信息以及員工的加班和請假單信息
加班根據加班的時間和填寫的加班單信息,找出加班的類型，將相應的時間記錄到相應的加班類型上去，以方便計算薪資
請假根據請假的時間和填寫的請假單,找出請假的類型，將相應的時間記錄到相應的請假類型上去，以方便計算薪資*************/
create table employee( --記錄公司員工的信息  
corp_id char(6),    --公司id                                      
employee_id char(10),    --員工的id                                     
employee_name varchar2(20) not null,    --員工的姓名                                  
employee_desc varchar2(255),    --員工的描述                                    
class_id char(6) not null,    --此員工應該打哪種卡
primary key(corp_id,employee_id));    create table base_code( --記錄在打卡記錄中各個flag的意思和意義
corp_id char(6), --公司id
code_id char(6), --code的id,主鍵作用
code_type varchar(20) not null, --code的類型('holiday','overtime','senior')
code_name varchar2(40)  not null, --code的名稱
code_descrip varchar2(255) not null, --code的描述
code_re varchar2(255), --code的備注
primary Key(corp_id,code_id));create table class_master( --記錄班表的主信息
corp_id char(6), --公司id
class_id char(6), --班表id
class_name varchar2(40) not null, --班表名稱
class_desc varchar2(255) , --班表描述
cycle_flag char(1) default 'Y', --是否24小時班制
cycle_time Number(6,2) default 24, --班表循環時間（以小時為單位）
class_start date not null, --班表執行開始時間
class_end date not null, --班表執行結束時間
cycle_num integer not null, --班表需要執行次數
start_flag char(1) default 'Y', --上班是否需要打卡
end_flag char(1) default 'Y'  --下班是否需要打卡
primary key(corp_id,class_id));create table class_detail( --記錄班表的詳細信息
corp_id char(6), --公司id
class_id char(6), --班表id
class_index integer, --班表的項次
work_flag char(1) default 'Y', --這個班次是否需要上班
holiday_flag char(1) default 'N', --這個班次是否請假
furlough_id char(10),    --請假單id
overtime_flag char(1) default 'N', --這個班次是否加班
overtime_id char(10),    --加班單id
work_start Date, --工作開始時間
work_end Date, --工作結束時間
primary key(corp_id,class_id,class_index));create table employee_card( --記錄員工打卡的信息                                  
corp_id char(6),    --公司id                                      
card_sn number(20),    --卡的序號,作為表的主鍵，記錄一次自動加1                        
card_id char(10) not null,    --員工卡的id                                  
class_id char(6) not null,    --班表id                                    
class_num number(10) not null,    --記錄使用的班表的項次
employee_id char(10) not null,    --員工的id                                   
senior_flag char(1) default '0', --打卡的狀態(是否有上級領導簽字)
holiday_flag char(1), --請假的類型
overtime_flag char(1), --加班的種類
card_start date,    --員工上班打卡的時間                                
card_end date,    --員工下班打卡的時間                              
primary key(corp_id,card_sn));       --employee_furlough表：記錄員工的請假信息，需要填寫請假單，並批準才記錄此表                              
create table employee_furlough( --記錄員工的請假信息                                  
corp_id char(6),    --公司id                                      
furlough_id char(10),    --請假單id                                     
employee_id char(10) not null,   --員工id                                    
fur_name varchar2(20) not null,    --請假單名稱                                  
fur_desc varchar2(255),    --請假的說明                                  
fur_sn char(6) not null,    --請假的類型id                                   
fur_start date not null,    --請假開始時間                               
fur_end date not null,    --請假結束時間
primary key(corp_id,furlough_id));                                               --furlough表：記錄請假類型和發放薪資的比率信息                                          
create table furlough( --記錄請假類型信息                                   
corp_id char(6),    --公司id                                      
furlough_sn char(6),    --請假類型id                                    
fur_type varchar2(20) not null,    --請假類型                                 
fur_rate number(6,2) not null,    --發放薪資比率                                 
fur_desc varchar2(255),    --請假類型描述
time_start char(5) not null, --請假類型開始時間（hh24:mi）
time_end char(5) not null, --請假類型結束時間（hh24:mi）
primary key(corp_id,furlough_sn));                                                 
        
--employee_overtime表：記錄員工加班的信息，需要填寫加班單，並批準才記錄此表
create table employee_overtime( --記錄員工的加班信息
corp_id char(6),    --公司id
overtime_id char(10),    --加班單id
employee_id char(10),    --員工id
over_name varchar2(20) not null,    --加班單的名稱
over_desc varchar2(255),     --加班的描述
over_sn char(6) not null,   --加班的類型id
over_start date not null, --加班的開始時間
over_end date not null, --加班的結束時間
primary key(corp_id,overtime_id));
                                                                  
--overtime表：記錄加班類型和發放薪資的比率信息                                          
create table overtime( --記錄加班類型信息                                   
corp_id char(6),   --公司id                                      
overtime_sn char(6),      --加班的類型id                                 
over_type varchar2(20) not null,     --加班的類型                              
over_rate number(6,2) not null,     --發放薪資比率                               
over_desc varchar2(255),     --加班類型的描述
time_start char(5) not null, --加班類型開始時間（hh24:mi）
time_end char(5) not null, --加

Answer 1

create or replace procedure pro_employee_card(
in_start_time Date, --統計開始時間
in_end_time Date, --統計結束時間
in_corp_id char(6) --公司id
) as
cur_employee_id char(10),
cur_employee_name varchar2(20),
cur_class_id char(6),
cur_class_num Number(10),
cur_senior_flag char(1),
cur_holiday_flag char(1),
cur_overtime_flag char(1)
cur_card_start Date,
cur_card_end Date,
c_class_name varchar2(40),
c_cycle_flag char(1),
n_cycle_time Number(6,2),
c_start_flag char(1),
c_end_flag char(1),n_over_time Number(6,2), --記錄總加班時間
n_holi_time Number(6,2), --記錄總請假時間
n_after_time Number(6,2), --記錄總曠工時間--定義遊標cur_employee尋找公司的員工記錄
declare Cursor cur_employee is
select employee_id,employee_name,class_id
from employee_code
where corp_id = in_corp_id;declare Cursor cur_card is
select class_num,senior_flag,holiday_flag,overtime_flag,card_start,card_end
from employee_card
where corp_id = in_corp_id
and employee_id = cur_employee_id
and class_id = cur_class_id;/**********
定義遊標，找出所對應的員工的請假記錄
在公司不要求上下班打卡或上下班隻打一次卡的情況下，算出各類請假的總時間，以方便計算薪資時使用
**********/
delcare Cursor cur_fur is
select furlough_sn,sum(furlough_end - furlough_start) * 24
from employee_furlough
where corp_id = in_corp_id
and furlough_start > in_bgin_date
and furlough_start <= in_end_date
and furlough_end >= in_begin_date
and furlough_end <= in_end_date
and employee_id = var_employee_id
Group by furlough_sn;/*******
定義遊標，找出所對應員工的加班記錄，
在公司不要求上下班打卡或上下班隻打一次卡的情況下，算出各類加班的總時間，以方便計算薪資時使用
************/
declare Cursor cur_over is
select overtime_sn,sum(overtime_end - overtime_start) * 24
from employee_overtime
where corp_id = in_corp_id
and employee_id = var_employee_id
and overtime_start > in_bgin_date
and overtime_start <= in_end_date
and overtime_end >= in_begin_date
and overtime_end <= in_end_date
Group by overtime_sn;
begin
open cur_employee; --打開遊標，讀取員工的資料
loop
fetch cur_employee into cur_employee_id,cur_employee_name,cur_class_id;
exit when cur_employee%notfound;

select class_name, --讀取此員工的班表資料
cycle_flag,
cycle_time,
cycle_num,
start_flag,
end_flag
into :c_class_name,
:c_cycle_flag,
:n_cycle_time,
:c_start_flag,
:c_end_flag
from class_master
where corp_id = in_corp_id
and class_id = cur_class_id
and class_start >= in_start_time
and class_start >= in_end_time
and class_end <= in_start_time
and class_end <= in_end_time;);


if (c_start_flag = "N" and c_end_flag = "N" )
or (c_start_flag = "Y" and c_end_flag = "N" )
or (c_start_flag = "N" and c_end_flag = "Y" ) then
/*************************
在員工上下班都不需要打卡或上下班隻需打一次卡的情況下，員工的加班信息和
請假信息都根據加班單和請假單計算
*************************/
/**********
處理員工上下班都不用打卡的記錄
員工上下班都不打卡，系統沒有記錄或不需要讀取員工的打卡記錄
系統系統處理員工加班和請假的資料就好了
**********/
open cur_fur;
loop
fetch cur_fur into var_furlough_sn,var_furlough_time;
exit when cur_fur%notfound;
/****此間計算請假期間薪資的發放的明細********/
/**根據薪資發放比率計算薪資*/
select furlough_rate,furlough_type
into :var_furlough_rate,:var_furlough_type
from work_furlough
where corp_id = in_corp_id
and furlough_sn = var_furlough_sn;
end loop
close cur_fur;

open cur_over;
loop
fetch cur_over into var_overtime_sn,var_overtime_time;
exit then cur_over%notfound;
/****此間計算加班和加班薪資發放的明細*********/
/****根據薪資發放比率計算薪資*/
select over_rate,over_type
into :var_overtime_rate,:var_overtime_type
from work_overtime
where corp_id = in_corp_id
and overtime_sn = var_overtime_sn;
end loop;
close cur_over;
end if;
if (c_start_flag = "Y" and c_end_flag = "N")
or (c_start_flag = "N" and c_end_flag = "Y")
or (c_start_flag = "Y" and c_end_flag = "Y")  then
/*************************
在員工上下班都需要打卡或上下班隻需打一次卡的情況下，
都需要讀取員工的打卡記錄以判斷員工的曠工等情況
*************************/
open cur_card;--打開遊標，讀取相應員工的打卡記錄
loop
fetch cur_card into cur_class_num,cur_senior_flag,cur_holiday_flag,cur_card_start,cur_card_end;
exit when cur_card%notfound;
--讀取相應的班表記錄
select work_flag,
work_start,
work_end,
holiday_flag,
overtime_flag,
senior_flag
into :c_work_flag,
:d_work_start,
:d_work_end,
:c_holiday_flag,
:c_overtime_flag,
:c_senior_flag
from class_detail
where corp_id = in_corp_id
and class_id = cur_class_id 
and class_index = cur_class_num;

if c_start_flag = "Y" and c_end_flag = "N" then --上班需要打卡，下班不需要打卡
if (cur_card_start >= d_work_start) or c_senior_flag = 1 then --上班打卡時間早於上班時間，屬於正常上班
n_count = n_count + 1;--正常上班次數加1
elsif  --上班打卡時間晚於上班時間，計算曠工時間

select (cur_card_start - d_work_start)*24
into temp_num --此次曠工時間，精確到小時
from dual;
n_after_time = n_after_time + temp_num; --記錄到總曠工時間中
end if;
end if;

if c_end_flag = "Y" and c_start_flag = "N" then --下班需要打卡，上班不需要打卡
if (cur_card_end =< d_work_end) or c_senior_flag = 2  then --下班打卡時間晚於下班時間，屬於正常上班
n_count = n_count + 1;--正常上班次數加1
else --下班打卡時間早於下班時間，計算曠工時間
select (d_work_start - cur_card_end)*24
into temp_num --此次曠工時間，精確到小時
from dual;
n_after_time = n_after_time + temp_num; --記錄到總曠工時間中
end if;
end if;

if c_start_flag = "Y" and c_end_flag = "Y" then --上下班都需要打卡

if cur_card_start >= d_work_start and cur_card_end <= d_work_end then --正常上班
n_count = n_count + 1;--正常上班次數加1
if c_overtime_flag = "Y" then --有加班
select (d_work_start - cur_card_start) * 24 + (cur_card_end - d_work_start) * 24
into :temp_over_num --此次上班加班時間
from dual;
n_over_time = n_over_time + temp_over_num;
/**************
加班根據加班的時間和填寫的加班單信息,找出加班的類型，
將相應的時間記錄到相應的加班類型上去，以方便計算薪資
**************/
end if;
else --不正常上班
if cur_card_start < d_work_start then--上班時間晚於班表時間
select (cur_card_start - d_work_start)*24
into temp_num --此次曠工時間，精確到小時
from dual;
if c_holiday_flag = "Y" then --有加班單
n_holi_time = n_holi_time + temp_num;--記錄到總請假時間中
else
n_after_time = n_after_time + temp_num; --記錄到總曠工時間中
end if;
end if;
if cur_card_end > d_work_end then --下班時間早於班表時間
select (d_work_start - cur_card_end)*24
into temp_num --此次曠工時間，精確到小時
from dual;
if c_holiday_flag = "Y" then --有加班單
n_holi_time = n_holi_time + temp_num;--記錄到總請假時間中
else
n_after_time = n_after_time + temp_num; --記錄到總曠工時間中
end if;
end if;
/**************
請假根據請假的時間和填寫的請假單,找出請假的類型，
將相應的時間記錄到相應的請假類型上去，以方便計算薪資
**************/
end if;
end loop;
close cur_employee;
end if;
end loop;
close cur_employee;
end;

Answer 2

這是我用oracle數據庫初步設想的一個結構，其中有哪些不足，請大家指出。也可能我考慮的太復雜，可能數據庫沒有必要這樣設計。