txt파일을 읽어서 해당 필요한 항목을 db에 저장하기
[ 업로드 버튼에 스크립트 작성 ]
- tb_29002 : txt file upload
- tb_29001 : txt file의 내용중 필요 항목을 가져와서 저장
Long ll_rtn
String ls_dt, ls_name, ls_null
Double ll_cnt, ll_rc, i, j, ll_div
String ls_trans_data, ls_busi_no, ls_tran_dt, ls_card_div, ls_sale_div, ls_sale_dt, ls_sale_amt, ls_fee_amt, ls_ori_sale_dt
String ls_card_no
pointer oldpointer
Setnull(ls_null)
oldpointer = SetPointer(HourGlass!)
dw_entry.acceptText()
ls_dt = String(dw_entry.object.as_dt[1], 'yyyymmdd')
ls_name = "C:\semplus\N001_DDC_REP." + ls_dt + ".txt"
select count(*)
into :ll_cnt
from tb_29001
where work_dt = to_date(:ls_dt, 'yyyymmdd')
and sale_div <> '99' ;
if ll_cnt > 0 then // 기등록 내역이 있는경우
ll_rtn = messageBox("알림", "이미 등록된 데이타가 있습니다. 삭제하시겠습니까?", Exclamation!, OKCancel!, 2)
if ll_rtn = 1 then
delete from tb_29001
where work_dt = to_date(:ls_dt, 'yyyymmdd')
and sale_div <> '99' ;
ll_rtn = dw_1.Importfile(ls_name)
for i = 1 to ll_rtn
dw_1.object.seq[i] = i
next
if ll_rtn > 0 then
ll_rc = dw_1.update()
if ll_rc = 1 then
declare cur cursor for
select trans_data
from tb_29002
order by seq ;
open cur;
do
fetch cur into :ls_trans_data ;
if midA(ls_trans_data,1,2) = '52' then
ls_busi_no = midA(ls_trans_data,19,10)
ls_tran_dt = midA(ls_trans_data,41,6)
ls_card_div = midA(ls_trans_data,86,2)
elseif midA(ls_trans_data,1,1) = '6' then
ls_sale_div =midA(ls_trans_data,1,2)
ls_sale_dt = midA(ls_trans_data,7,6)
ls_sale_amt = midA(ls_trans_data,40,10)
ls_fee_amt = midA(ls_trans_data,69,10)
ls_card_no = midA(ls_trans_data,19,19)
if midA(ls_trans_data,1,2) = '67' then
ls_ori_sale_dt = midA(ls_trans_data,56,6)
ll_div = -1
else
ls_ori_sale_dt = ls_null
ll_div = 1
end if
j = j + 1
insert into tb_29001
( work_dt,
busi_no,
trans_dt,
card_div,
sale_div,
sale_dt,
seq,
sale_amt,
fee_amt,
ori_sale_dt,
card_no,
create_id,
create_dt,
create_ip )
values ( to_date(:ls_dt, 'yyyymmdd'),
:ls_busi_no,
to_char(to_date(:ls_tran_dt,'yymmdd'), 'yyyymmdd'),
:ls_card_div,
:ls_sale_div,
to_char(to_date(:ls_sale_dt,'yymmdd'), 'yyyymmdd'),
:j,
to_number(:ls_sale_amt) * :ll_div,
to_number(:ls_fee_amt) * :ll_div,
decode(:ls_ori_sale_dt, null, null, to_char(to_date(:ls_ori_sale_dt,'yymmdd'), 'yyyymmdd')),
:ls_card_no,
:gs_userid,
sysdate,
:gs_ip_add);
end if;
loop until sqlca.sqlcode = 100 ;
close cur;
delete from tb_29002 ;
commit;
messagebox('알림', 'UPLOAD 완료되었습니다!!!')
else
rollback ;
messagebox('알림', 'UPLOAD 실패하였습니다!!!')
end if
elseif ll_rtn = -8 then
rollback ;
messagebox("ERROR", "Not a TXT file !!!")
elseif ll_rtn = -7 then
rollback ;
messagebox("ERROR", "Error reading the text !!!")
elseif ll_rtn = -2 then
rollback ;
messagebox("ERROR", "파일의 데이타가 없습니다(Empty file)!!!")
elseif ll_rtn = -1 then
rollback ;
messagebox("ERROR", "No rows !!!" )
elseif ll_rtn = 0 then
rollback ;
messagebox("ERROR", "End of file; too many rows !!!" )
elseif ll_rtn = -9 then
rollback ;
messagebox("ERROR", "The user canceled the import !!!" )
else
rollback ;
messagebox("ERROR", string(ll_rtn,'0') + 'error')
end if
else
MessageBox("알림", "이미 등록된 데이타가 있습니다. UPLOAD할수 없습니다!!!")
end if
/////////////
// 신규
/////////////
else
ll_rtn = dw_1.ImportFile(ls_name)
for i = 1 to ll_rtn
dw_1.object.seq[i] = i
next
if ll_rtn > 0 then
ll_rc = dw_1.update()
if ll_rc = 1 then
declare cur1 cursor for
select trans_data
from tb_29002
order by seq ;
open cur1;
do
fetch cur1 into :ls_trans_data ;
if midA(ls_trans_data,1,2) = '52' then
ls_busi_no = midA(ls_trans_data,19,10)
ls_tran_dt = midA(ls_trans_data,41,6)
ls_card_div = midA(ls_trans_data,86,2)
elseif midA(ls_trans_data,1,1) = '6' then
ls_sale_div =midA(ls_trans_data,1,2)
ls_sale_dt = midA(ls_trans_data,7,6)
ls_sale_amt = midA(ls_trans_data,40,10)
ls_fee_amt = midA(ls_trans_data,69,10)
ls_card_no = midA(ls_trans_data,19,19)
if midA(ls_trans_data,1,2) = '67' then
ls_ori_sale_dt = midA(ls_trans_data,56,6)
ll_div = -1
else
ls_ori_sale_dt = ls_null
ll_div = 1
end if
j= j + 1
insert into tb_29001
( work_dt,
busi_no,
trans_dt,
card_div,
sale_div,
sale_dt,
seq,
sale_amt,
fee_amt,
ori_sale_dt,
card_no,
create_id,
create_dt,
create_ip )
values ( to_date(:ls_dt, 'yyyymmdd'),
:ls_busi_no,
to_char(to_date(:ls_tran_dt,'yymmdd'), 'yyyymmdd'),
:ls_card_div,
:ls_sale_div,
to_char(to_date(:ls_sale_dt,'yymmdd'), 'yyyymmdd'), :j,
to_number(:ls_sale_amt) * :ll_div,
to_number(:ls_fee_amt) * :ll_div,
decode(:ls_ori_sale_dt, null, null, to_char(to_date(:ls_ori_sale_dt,'yymmdd'), 'yyyymmdd')),
:ls_card_no,
:gs_userid,
sysdate,
:gs_ip_add)
end if;
loop until sqlca.sqlcode = 100 ;
close cur1;
delete from tb_29002 ;
commit ;
messagebox('알림', 'UPLOAD 완료되었습니다!!!')
else
rollback ;
messagebox('알림', 'UPLOAD 실패하였습니다!!!')
end if
elseif ll_rtn = -8 then
rollback ;
messagebox("ERROR", "Not a TXT file !!!")
elseif ll_rtn = -7 then
rollback ;
messagebox("ERROR", "Error reading the text !!!")
elseif ll_rtn = -2 then
rollback ;
messagebox("ERROR", "파일의 데이타가 없습니다(Empty file)!!!")
elseif ll_rtn = -1 then
rollback ;
messagebox("ERROR", "No rows !!!" )
elseif ll_rtn = 0 then
rollback ;
messagebox("ERROR", "End of file; too many rows !!!" )
elseif ll_rtn = -9 then
rollback ;
messagebox("ERROR", "The user canceled the import !!!" )
else
rollback ;
messagebox("ERROR", string(ll_rtn,'0') + 'error')
end if
end if
SetPointer(oldpointer)