在第600行的時候把這隻程式交出去了... #=====================================================================#
# PRGNAM : abs5121.4gl #
# PRGFUN : 員工加班請假彙總表 #
# AUTHOR-NAME: 吳承澔 #
# USE TABLE : abscmcerrtab,psntab,abs2001tab,abs201typetab, #
# abstypetab #
# USE FORM : abs5121.per #
# DATE : 96.12.03 #
#=====================================================================#
DATABASE payroll
GLOBALS
#----------------
# system area
#----------------
DEFINE sys_date INTEGER,
sys_program CHAR(7),
sys_heading CHAR(30),
sys_user_id CHAR(7),
sys_user_ip CHAR(20),
sys_permision CHAR(6) #使用權限
#----------------
# work area
#----------------
DEFINE query_str,pre_over1 CHAR(1000)
DEFINE pre_str,pre_str1 CHAR(1000)
DEFINE pre_cnt,pre_stand CHAR(1000)
DEFINE pre_holi1,pre_holi2 CHAR(1000)
DEFINE pre_over,prepare_str CHAR(1000)
DEFINE pre_stand1 CHAR(500)
DEFINE i INTEGER
DEFINE p_buffer CHAR(20)
DEFINE g_date INTEGER
DEFINE p_path CHAR(50)
DEFINE answer CHAR(1)
#----------------
# i-o area
#----------------
DEFINE p_abspsn RECORD
psn_code LIKE abspsntab.psn_code,
rest_no LIKE abspsntab.rest_no,
dept_code LIKE abspsntab.dept_code
END RECORD
DEFINE p_inp RECORD
start_date INTEGER,
end_date INTEGER
END RECORD
DEFINE p_psntab RECORD
psn_name LIKE psntab.psn_name,
over_yn LIKE psntab.over_yn,
m_pos INTEGER,
pos INTEGER,
foreign_yn LIKE psntab.foreign_yn
END RECORD
DEFINE p_r ARRAY[20000] OF RECORD
cnt INTEGER,
make INTEGER,
stand INTEGER,
holi_1 DECIMAL(8,1),
holi_2 DECIMAL(8,1),
real DECIMAL(8,1),
over DECIMAL(8,1),
dept_code CHAR(8)
END RECORD
DEFINE p_dt RECORD
cnt INTEGER,
make INTEGER,
stand INTEGER,
holi_1 DECIMAL(8,1),
holi_2 DECIMAL(8,1),
real DECIMAL(8,1),
over DECIMAL(8,1)
END RECORD
END GLOBALS
#----------------------------------------------------------
# MAIN PROGRAM
#----------------------------------------------------------
MAIN
DEFER INTERRUPT
SET LOCK MODE TO WAIT
WHENEVER ERROR STOP
OPTIONS ERROR LINE LAST
OPTIONS INPUT WRAP,
HELP KEY CONTROL-W,
HELP FILE "abshelp.ex"
CALL data_initial()
CALL disp_screen(4,20)
CALL main_menu()
CALL close_screen()
CLEAR SCREEN
END MAIN
#----------------------------------------------------------
# DATA INITIALIZE
#----------------------------------------------------------
FUNCTION data_initial()
CALL sys_data_initial()
END FUNCTION
#----------------------------------------------------------
# MAIN MENU
#----------------------------------------------------------
FUNCTION main_menu()
CALL curr_window("M")
MENU "選項"
BEFORE MENU
IF sys_permision[5,5] != 'Y' THEN
HIDE OPTION "D.資料下傳"
END IF
COMMAND "D.資料下傳" "資料下傳,按<Esc>執行,<Ctrl-C>放棄"
HELP 0001
CALL qryfun()
CALL curr_window("M")
COMMAND "0.離開" "結束執行，回上一功能表"
EXIT MENU
COMMAND KEY(CONTROL-F)
CALL showhelp(5121)
END MENU
END FUNCTION
#----------------------------------------------------
# 資料下傳
#----------------------------------------------------
FUNCTION qryfun()
DEFINE l_start,l_cnt,li,l_date,la INTEGER
DEFINE l_date1,l_c,l_hr,l_hr1,l_ii INTEGER
DEFINE l_y,l_m,l_d,l_ey,l_em,lc,lb INTEGER
DEFINE lx,lj,l_ym1,l_ym,l_day,i1 INTEGER
DEFINE l_dir_name,l_rest CHAR(50)
DEFINE l_symd,l_eymd CHAR(7)
DEFINE l_r CHAR(1)
DEFINE l_psn LIKE psntab.psn_code
DEFINE l_dept_code,l_bef_dept CHAR(8)
DEFINE l_dept_name LIKE pubcod.codsc
CREATE TEMP TABLE temp_tab
( psn_code CHAR(7) NOT NULL,
start_date INTEGER NOT NULL,
rest SMALLINT NOT NULL,
dept_code CHAR(8) NOT NULL)
CREATE INDEX temp_index1 ON temp_tab( psn_code,start_date )
CREATE TEMP TABLE temp_tab1
( date INTEGER,
cnt INTEGER,
make INTEGER,
stand INTEGER,
holi_1 DECIMAL(8,1),
holi_2 DECIMAL(8,1),
real DECIMAL(8,1),
over DECIMAL(8,1),
dept_code CHAR(8))
CALL curr_window("F")
CLEAR FORM
LET INT_FLAG = FALSE
LET l_ii = 0
INITIALIZE p_inp.* TO NULL
MESSAGE " 請輸入下傳條件, ESC.執行下傳, CTRL-C.取消" ATTRIBUTE(REVERSE)
INPUT BY NAME p_inp.* WITHOUT DEFAULTS
AFTER FIELD start_date
IF p_inp.start_date IS NULL THEN
ERROR "開始日期不可空白"
NEXT FIELD start_date
END IF
IF ih_is_date(p_inp.start_date) = FALSE THEN
ERROR "開始日期有誤"
NEXT FIELD start_date
END IF
LET l_symd = p_inp.start_date
AFTER FIELD end_date
IF p_inp.end_date < p_inp.start_date THEN
ERROR "結束日期不可小於開始日期"
NEXT FIELD start_date
END IF
IF p_inp.end_date IS NULL THEN
ERROR "結束日期不可空白"
NEXT FIELD end_date
END IF
IF ih_is_date(p_inp.end_date) = FALSE THEN
ERROR "結束日期有誤"
NEXT FIELD end_date
END IF
LET l_eymd = p_inp.end_date
AFTER INPUT
IF INT_FLAG THEN
LET answer = msg_yn("確定放棄查詢?")
IF answer MATCHES "[Yy]" THEN
EXIT INPUT
ELSE
LET INT_FLAG = FALSE
CONTINUE INPUT
END IF
END IF
IF p_inp.end_date < p_inp.start_date THEN
ERROR "結束日期不可小於開始日期"
NEXT FIELD start_date
END IF
IF p_inp.end_date IS NULL THEN
ERROR "結束日期不可空白"
NEXT FIELD end_date
END IF
IF ih_is_date(p_inp.end_date) = FALSE THEN
ERROR "結束日期有誤"
NEXT FIELD end_date
END IF
IF p_inp.start_date IS NULL THEN
ERROR "開始日期不可空白"
NEXT FIELD start_date
END IF
IF ih_is_date(p_inp.start_date) = FALSE THEN
ERROR "開始日期有誤"
NEXT FIELD start_date
END IF
END INPUT
IF INT_FLAG THEN
DISPLAY "" AT 2,1
ERROR "取消下傳!!"
DROP TABLE temp_tab
DROP TABLE temp_tab1
LET INT_FLAG = FALSE
RETURN
END IF
CONSTRUCT BY NAME query_str ON abspsntab.psn_code,
abspsntab.dept_code
ATTRIBUTE (REVERSE)
AFTER FIELD dept_code
LET p_buffer = GET_FLDBUF (dept_code)
IF p_buffer = '?' THEN
LET prepare_str = " SELECT code , codsc " ,
" FROM pubcod " ,
" WHERE codekind = 'GE' " ,
" ORDER BY code "
CALL construct_window (2,6,25,prepare_str, "abs1113a")
RETURNING p_buffer
DISPLAY p_buffer TO dept_code
NEXT FIELD dept_code
END IF
END CONSTRUCT
IF INT_FLAG THEN
ERROR "取消下傳!!"
DROP TABLE temp_tab
DROP TABLE temp_tab1
LET INT_FLAG = FALSE
RETURN
END IF
INITIALIZE p_abspsn.* TO NULL
LET pre_str =" SELECT abspsntab.psn_code,abspsntab.rest_no,abspsntab.dept_code",
" FROM abspsntab ",
" WHERE ", query_str CLIPPED,
" AND curr_ym BETWEEN ",
(p_inp.start_date/100) MOD 10000 ," AND ",
(p_inp.end_date/100) MOD 10000 ,
" ORDER BY abspsntab.dept_code "
MESSAGE " 資料整理中, 請稍候... " ATTRIBUTE(REVERSE)
PREPARE prepare_exe FROM pre_str
DECLARE abspsn_cursor CURSOR FOR prepare_exe
LET l_c = 0
LET la = LENGTH(l_symd)
LET lb = LENGTH(l_eymd)
#為民國100年之後準備
IF la = 6 THEN --開始日期為民國100年之前
LET l_y = l_symd[1,2]
LET l_m = l_symd[3,4]
LET l_d = l_symd[5,6]
ELSE --開始日期為民國100年之後
LET l_y = l_symd[1,3]
LET l_m = l_symd[4,5]
LET l_d = l_symd[6,7]
END IF
IF lb = 6 THEN --結束日期為民國100年之前
LET l_ey = l_eymd[1,2]
LET l_em = l_eymd[3,4]
ELSE --結束日期為民國100年之後
LET l_ey = l_eymd[1,3]
LET l_em = l_eymd[4,5]
END IF
--DISPLAY la
FOREACH abspsn_cursor INTO p_abspsn.*
LET l_c = l_c + 1
#將一串上班型態(rest_no)切割為每一天
FOR lx = l_y TO l_ey--取年
FOR li = l_m TO l_em --取月
LET l_ym = ((lx*100)+li)
CALL ih_monthday(l_ym) RETURNING l_ym1
FOR lj = l_d TO l_ym1--取日
LET l_date = (lx*10000)+(li*100)+lj
LET l_rest = p_abspsn.rest_no
LET l_r = l_rest[lj,lj]
IF l_r IS NULL OR l_r = ' ' THEN
LET l_r = 1
END IF
SELECT COUNT(*)
INTO lc
FROM temp_tab
WHERE psn_code = p_abspsn.psn_code
AND start_date = l_date
IF lc = 0 THEN
INSERT INTO temp_tab VALUES
(p_abspsn.psn_code,l_date,l_r,p_abspsn.dept_code)
--DISPLAY '工號:',p_abspsn.psn_code,'日期:',l_date,'型態:',l_r,'部門:',p_abspsn.dept_code
ELSE
CONTINUE FOREACH
END IF
END FOR
END FOR
END FOR
END FOREACH
IF l_c = 0 THEN
MESSAGE ""
ERROR "無合乎條件之資料"
DROP TABLE temp_tab
DROP TABLE temp_tab1
RETURN
END IF
LET pre_str1="SELECT start_date,dept_code",
" FROM temp_tab ",
" WHERE start_date BETWEEN ",
p_inp.start_date CLIPPED," AND ",
p_inp.end_date CLIPPED,
" GROUP BY 1,2"
--計算出每一天應上班人數(cnt)
LET pre_cnt ="SELECT a.start_date,COUNT(a.rest) as cnt, a.dept_code ",
" FROM temp_tab a,temp_tab1 b",
" WHERE a.start_date = b.date ",
" AND a.dept_code = b.dept_code ",
" AND rest <> 1 ",
" GROUP BY 1,3 ",
" ORDER BY 1,3"
--標準工時整天班(stand)
LET pre_stand="SELECT a.start_date,COUNT(a.rest) * 8 as stand ,a.dept_code",
" FROM temp_tab a,temp_tab1 b ",
" WHERE a.start_date = b.date",
" AND a.dept_code = b.dept_code ",
" AND a.rest = 0",--rest=0為整天=2為半天=1為休假
" GROUP BY 1,3 "
--標準工時半天班(stand)
LET pre_stand1="SELECT a.start_date,COUNT(a.rest) * 4 as stand ,a.dept_code",
" FROM temp_tab a,temp_tab1 b ",
" WHERE a.start_date = b.date",
" AND a.dept_code = b.dept_code ",
" AND a.rest = 2",
" GROUP BY 1,3 "
--公假時數(holi_1)
LET pre_holi1="SELECT a.start_date,a.trx2001_days,SUM(a.trx2001_hours) as holi_1 ,a.dept_code",
" FROM abs2001tab a,psntab b,temp_tab1 c",
" WHERE a.trx2001_type = '4' ",
" AND a.finish_yn <> 'C' ",
" AND a.psn_code = b.psn_code",
" AND a.dept_code = c.dept_code ",
" AND a.start_date = c.date ",
" AND (b.leave_date = 0 ",
" OR b.leave_date > ",
p_inp.start_date CLIPPED,")",
-- " AND abs2001tab.end_date BETWEEN ",
-- p_inp.start_date CLIPPED," AND ",p_inp.end_date CLIPPED,
" GROUP BY 1,2,4 "
--請假時數(holi_2)
LET pre_holi2="SELECT a.start_date,a.trx2001_days,SUM(a.trx2001_hours) as holi_2,a.dept_code",
" FROM abs2001tab a,psntab b,temp_tab1 c",
" WHERE ((a.trx2001_type IN (1,2,3,6,7,8,9) AND a.finish_yn = 'Y') ",
" OR (a.trx2001_type IN (5,10,11,13,14,15) AND a.finish_yn <> 'C'))",
" AND a.psn_code = b.psn_code",
" AND a.dept_code = c.dept_code ",
" AND a.start_date = c.date ",
" AND (b.leave_date = 0 OR b.leave_date > ",
p_inp.start_date CLIPPED,")",
-- " AND a.start_date BETWEEN ",
-- p_inp.start_date CLIPPED," AND ",p_inp.end_date CLIPPED,
-- " AND a.end_date BETWEEN ",
-- p_inp.start_date CLIPPED," AND ",p_inp.end_date CLIPPED,
" GROUP BY 1,2,4 "
--加班時數(over)
LET pre_over="SELECT a.start_date,SUM(a.over_total) as over,c.dept_code",
" FROM absovertab a,psntab b,temp_tab1 c",
" WHERE a.psn_code = b.psn_code ",
" AND b.dept_code = c.dept_code ",
" AND a.start_date = c.date ",
" AND (b.leave_date = 0 OR b.leave_date > ",
p_inp.start_date CLIPPED,")",
" GROUP BY 1,3 "
--加班人數
LET pre_over1="SELECT a.start_date,COUNT(a.psn_code),b.dept_code",
" FROM absovertab a,psntab b",
" WHERE a.psn_code = b.psn_code ",
" AND a.start_date BETWEEN ",
p_inp.start_date CLIPPED," AND ",p_inp.end_date CLIPPED,
-- " AND b.dept_code = c.dept_code ",
-- " AND a.start_date = c.date ",
-- " AND a.psn_code NOT IN (SELECT temp_tab.psn_code ",
-- " FROM temp_tab,absovertab ",
-- " WHERE temp_tab.start_date = absovertab.start_date )",
" AND (b.leave_date = 0 ",
" OR b.leave_date > ",p_inp.start_date CLIPPED,")",
" GROUP BY 1,3 ",
" ORDER BY 1,3 "
--產量
FOR li = 1 TO 2000
INITIALIZE p_r[li].* TO NULL
END FOR
LET i = 1
PREPARE pre_exe0 FROM pre_str1
DECLARE mas_cur0 CURSOR FOR pre_exe0
FOREACH mas_cur0 INTO g_date,l_dept_code
INSERT INTO temp_tab1 VALUES (g_date,0,0,0,0,0,0,0,l_dept_code)
END FOREACH
LET i = 1
PREPARE pre_exe2 FROM pre_cnt
DECLARE mas_cur2 CURSOR FOR pre_exe2
FOREACH mas_cur2 INTO g_date,p_r[i].cnt,l_dept_code
UPDATE temp_tab1
SET temp_tab1.cnt = p_r[i].cnt
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
END FOREACH
--整天班
LET i = 1
PREPARE pre_exe3 FROM pre_stand
DECLARE mas_cur3 CURSOR FOR pre_exe3
FOREACH mas_cur3 INTO g_date,p_r[i].stand,l_dept_code
UPDATE temp_tab1
SET temp_tab1.stand = p_r[i].stand
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
END FOREACH
--半天班
LET i = 1
PREPARE pre_exe8 FROM pre_stand1
DECLARE mas_cur8 CURSOR FOR pre_exe8
FOREACH mas_cur8 INTO g_date,p_r[i].stand,l_dept_code
UPDATE temp_tab1
SET temp_tab1.stand = temp_tab1.stand + p_r[i].stand
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
END FOREACH
LET i = 1
PREPARE pre_exe4 FROM pre_holi1
DECLARE mas_cur4 CURSOR FOR pre_exe4
FOREACH mas_cur4 INTO g_date,l_day,p_r[i].holi_1,l_dept_code
IF l_day <> 0 THEN LET p_r[i].holi_1 = p_r[i].holi_1 + 8 END IF
UPDATE temp_tab1
SET temp_tab1.holi_1 = p_r[i].holi_1
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
END FOREACH
LET i = 1
PREPARE pre_exe5 FROM pre_holi2
DECLARE mas_cur5 CURSOR FOR pre_exe5
FOREACH mas_cur5 INTO g_date,l_day,p_r[i].holi_2,l_dept_code
--有些請長假的一次是一個月，若無判斷的話會造成實際工時變負數
SELECT cnt
INTO lc
FROM temp_tab1
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
IF lc = 0 THEN
CONTINUE FOREACH
ELSE
IF l_day <> 0 THEN LET p_r[i].holi_2 = p_r[i].holi_2 + 8 END IF
UPDATE temp_tab1
SET temp_tab1.holi_2 = p_r[i].holi_2
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
END IF
END FOREACH
LET i = 1
PREPARE pre_exe6 FROM pre_over
DECLARE mas_cur6 CURSOR FOR pre_exe6
FOREACH mas_cur6 INTO g_date,p_r[i].over,l_dept_code
UPDATE temp_tab1
SET temp_tab1.over = p_r[i].over
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
END FOREACH
LET i = 1
PREPARE pre_exe7 FROM pre_over1
DECLARE mas_cur7 CURSOR FOR pre_exe7
FOREACH mas_cur7 INTO g_date,p_r[i].cnt,l_dept_code
UPDATE temp_tab1
SET temp_tab1.cnt = p_r[i].cnt
WHERE temp_tab1.date = g_date
AND temp_tab1.dept_code = l_dept_code
AND temp_tab1.cnt < p_r[i].cnt
END FOREACH
--產量(make)沒做
MESSAGE ""
SELECT dir_name
INTO l_dir_name
FROM sysdirtab
WHERE sys_no = 'trf'
LET p_path = l_dir_name CLIPPED,"abs5121_",sys_user_id CLIPPED,".txt"
START REPORT abs5121_r TO p_path
LET pre_str1 =" SELECT *",
" FROM temp_tab1 ",
" ORDER BY dept_code,date "
LET la = 0
PREPARE pre_exe FROM pre_str1
DECLARE tmp_cursor CURSOR FOR pre_exe
FOREACH tmp_cursor INTO l_date1,p_r[li].*
LET la = la + 1
LET l_dept_name = codsc1(p_r[li].dept_code,'GE')
--實際工時數:(標準工時+加班時數)-(請假時數+公假時數)
LET p_r[li].real = (p_r[li].stand + p_r[li].over) - (p_r[li].holi_1 + p_r[li].holi_2)
OUTPUT TO REPORT abs5121_r(l_date1,p_r[li].*,l_dept_name)
END FOREACH
DISPLAY "" AT 16,2
IF la = 0 THEN
ERROR "無合乎條件之資料!!"
ELSE
FINISH REPORT abs5121_r
ERROR "資料下傳完畢置於\\\\chgap1\\ihwa_public\\abs5121_",sys_user_id CLIPPED,".txt"
END IF
DROP TABLE temp_tab
DROP TABLE temp_tab1
END FUNCTION
REPORT abs5121_r(r_d2)
DEFINE r_d2 RECORD
date INTEGER,
cnt INTEGER,
make INTEGER,
stand INTEGER,
holi_1 DECIMAL(8,1),
holi_2 DECIMAL(8,1),
real DECIMAL(8,1),
over DECIMAL(8,1),
dept_code CHAR(8),
dept_name LIKE pubcod.codsc
END RECORD
OUTPUT
TOP MARGIN 0
BOTTOM MARGIN 0
LEFT MARGIN 0
FORMAT
FIRST PAGE HEADER
PRINT COLUMN 1,"怡華實業股份有限公司"
PRINT COLUMN 1, ((p_inp.start_date/10000) MOD 100) USING "<<<","年",((p_inp.start_date /100) MOD 100) USING "<<","月"," 生產工時產量對照表 "
PRINT COLUMN 1, "列印日期:", sys_date USING "<<<<<<<","製表人:",ih_get_username(sys_user_id)
PRINT COLUMN 1,"日期員工人數產量千碼標準工時公假時數請假時數實際工時加班時數"
BEFORE GROUP OF r_d2.dept_code
PRINT COLUMN 1,"部門代號:",r_d2.dept_code,"-",r_d2.dept_name
ON EVERY ROW
PRINT COLUMN 1,r_d2.date,"",r_d2.cnt,"",r_d2.make,"",r_d2.stand,"",r_d2.holi_1,"",r_d2.holi_2,"",r_d2.real,"",r_d2.over
END REPORT
FUNCTION close_screen()
CLOSE WINDOW w_user
CLOSE WINDOW w_menu
CLOSE WINDOW w_form
END FUNCTION
FUNCTION codsc1(Lcode,Lcodekind)
DEFINE Lcode LIKE pubcod.code,
Lcodekind LIKE pubcod.codekind ,
Lcodsc LIKE pubcod.codsc
SELECT codsc
INTO Lcodsc
FROM pubcod
WHERE code = Lcode
AND codekind = Lcodekind
IF (STATUS!=0) THEN
INITIALIZE Lcodsc TO NULL
END IF
RETURN Lcodsc
END FUNCTION
分類： 心