no, that part works fine. in fact, i have gotten the whole
page to act right thnx, here is the whole code if it can help
someone else out with it.
cfmx 6.1
informix 7.3.1
bulk loader
csv loader
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.InsertRecord") AND FORM.InsertRecord
EQ "loadreceiver">
<cffile action="UPLOAD" filefield="clockfile"
destination="#ExpandPath('uploadFolder')#" nameconflict="overwrite"
>
<cfset CRLF = Chr(13) & Chr(10)>
<cffile action="READ"
file="#ExpandPath('uploadFolder')#/clocktest.csv"
variable="csvData">
<!---
FILE LAYOUT:
#csvArray[1]#,#csvArray[2]#,#csvArray[3]#,#csvArray[4]#,#csvArray[5]#
empl_code, assign_no, regular, overtime, dbl time
--->
<cfquery name="rsPeriod" datasource="standard">
SELECT curr_year, curr_month, curr_qtr, curr_period
FROM stycurpd
</cfquery>
<cfset current_batch_number = form.batch_no>
<cfset current_record_number = 0>
<cfloop index="csvRecord" list="#csvData#"
delimiters="#CRLF#">
<cfset current_record_number = current_record_number +
1>
<cfset csvArray = ArrayNew(1)>
<cfset csvCellCount = 1>
<cfif current_record_number EQ 100>
<cfset current_batch_number = current_batch_number + 1>
<cfset current_record_number = 1>
</cfif>
<cfloop index="csvCell" list="#csvRecord#"
delimiters=",">
<cfset csvArray[variables.csvCellCount] = csvCell>
<cfset csvCellCount = csvCellCount + 1>
</cfloop>
<cfquery name="rsMatch" datasource="standard">
SELECT
a.branch_code, a.bus_segment, a.cust_code, a.assign_dept,
a.order_num, a.reference1, a.reference2, wk.wc_class,
c.wc_state_no,
wr.wc_calc_rate, o.wc_code, a.reg_prate, a.reg_brate,
a.ovt_prate, a.ovt_brate, a.dbl_prate, a.dbl_brate, a.bill_to_code,
a.owner_num,
a.taker_num, cb.slsp_code, cb.sls_area_code, a.type_service
FROM standard:informix.styasgne a, standard:informix.styordre
o, standard:informix.strcustr c, standard:informix.strcbsgr cb,
standard:informix.stywcskr wk, standard:informix.stywcrtr wr
WHERE (a.order_num=o.order_num) AND (a.cust_code=c.cust_code)
AND (c.cust_code=cb.cust_code) AND (o.wc_code=wk.wc_code) AND
(wk.wc_class=wr.wc_class) AND a.assign_no='#csvArray[2]#'
</cfquery>
<cfquery datasource="standard">
INSERT INTO paywetmp
(
batch_no, batch_rec, batch_pflag, batch_user_num,
batch_date, merge_no, empl_code, week_end, branch_code,
bus_segment,
cust_code, cust_dept, order_num, assign_no, reference,
reference2, wc_class, wc_state_no, wc_rate, wc_base_amt, wc_amt,
wc_code, reg_phrs, reg_prate, reg_pamt, reg_bhrs, reg_brate,
reg_bamt, ovt_phrs, ovt_prate, ovt_pamt, ovt_bhrs, ovt_brate,
ovt_bamt, dbl_phrs, dbl_prate, dbl_pamt, dbl_bhrs,
dbl_brate, dbl_bamt, tot_phrs, tot_pamt, tot_bhrs, tot_bamt,
tot_mtax_pamt,
tot_mtax_bamt, tot_mded_pamt, tot_mded_bamt, reg_pa, reg_at,
ovt_pa, ovt_at, dbl_pa, dbl_at, tsht_type, bill_to_code, owner_num,
taker_num, slsp_code, sls_area_code, type_service,
cl_week_end, billed, tot_mntx_pamt, tot_mntx_bamt, pay_type,
pay_year,
pay_month, pay_qtr, pay_period, gl_period, gl_year, div_code
)
VALUES
(
#current_batch_number#
,
#current_record_number#
,
<cfif IsDefined("FORM.batch_pflag") AND
#FORM.batch_pflag# NEQ "">
'#FORM.batch_pflag#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.batch_user_num") AND
#FORM.batch_user_num# NEQ "">
'#FORM.batch_user_num#'
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.batch_date") AND #FORM.batch_date#
NEQ "">
'#FORM.batch_date#'
<cfelse>
NULL
</cfif>
,
0
,
#csvArray[1]#
,
<cfif IsDefined("FORM.week_end") AND #FORM.week_end# NEQ
"">
'#FORM.week_end#'
<cfelse>
NULL
</cfif>
,
'#rsMatch.branch_code#'
,
'#rsMatch.bus_segment#'
,
#rsMatch.cust_code#
,
'#rsMatch.assign_dept#'
,
#rsMatch.order_num#
,
#csvArray[2]#
,
'#rsMatch.reference1#'
,
'#rsMatch.reference2#'
,
'#rsMatch.wc_class#'
,
'#rsMatch.wc_state_no#'
,
#rsMatch.wc_calc_rate#
,
#ABS(ABS(csvArray[3] + csvArray[4] + csvArray[5]) *
rsMatch.reg_prate)#
,
#ABS(ABS(ABS(csvArray[3] + csvArray[4] + csvArray[5]) *
rsMatch.reg_prate) * rsMatch.wc_calc_rate)#
,
'#rsMatch.wc_code#'
,
#csvArray[3]#
,
#rsMatch.reg_prate#
,
#ABS(csvArray[3] * rsMatch.reg_prate)#
,
#csvArray[3]#
,
#rsMatch.reg_brate#
,
#ABS(csvArray[3] * rsMatch.reg_brate)#
,
#csvArray[4]#
,
#rsMatch.ovt_prate#
,
#ABS(csvArray[4] * rsMatch.ovt_prate)#
,
#csvArray[4]#
,
#rsMatch.ovt_brate#
,
#ABS(csvArray[4] * rsMatch.ovt_brate)#
,
#csvArray[5]#
,
#rsMatch.dbl_prate#
,
#ABS(csvArray[5] * rsMatch.dbl_brate)#
,
#csvArray[5]#
,
#rsMatch.dbl_brate#
,
#ABS(csvArray[5] * rsMatch.dbl_brate)#
,
#ABS(csvArray[3] + csvArray[4] + csvArray[5])#
,
#ABS(ABS(csvArray[3] * rsMatch.reg_prate))+ (ABS(csvArray[4]
* rsMatch.ovt_prate)) + (ABS(csvArray[5] * rsMatch.dbl_prate))#
,
#ABS(csvArray[3] + csvArray[4] + csvArray[5])#
,
#ABS(ABS(csvArray[3] * rsMatch.reg_brate))+ (ABS(csvArray[4]
* rsMatch.ovt_brate)) + (ABS(csvArray[5] * rsMatch.dbl_brate))#
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
'R'
,
'#rsMatch.bill_to_code#'
,
#rsMatch.owner_num#
,
#rsMatch.taker_num#
,
'#rsMatch.slsp_code#'
,
'#rsMatch.sls_area_code#'
,
'#rsMatch.type_service#'
,
<cfif IsDefined("FORM.week_end") AND #FORM.week_end# NEQ
"">
'#FORM.week_end#'
<cfelse>
NULL
</cfif>
,
'N'
,
0
,
0
,
'W'
,
#rsPeriod.curr_year#
,
#rsPeriod.curr_month#
,
#rsPeriod.curr_qtr#
,
#rsPeriod.curr_period#
,
#rsPeriod.curr_period#
,
#rsPeriod.curr_year#
,
'MZZ'
)
</cfquery>
</cfloop>
<cflocation url="index.cfm">
</cfif>
<html>
<head>
<title>Load Clock File</title>
<meta http-equiv="Content-Type" content="text/html;
charset=iso-8859-1">
</head>
<body>
<form name="loadreceiver"
action="<cfoutput>#CurrentPage#</cfoutput>"
method="post" enctype="multipart/form-data">
<div align="center">
<input type="file" name="clockfile">
<br />
</div>
<table width="80%" border="1" align="center"
cellpadding="3" cellspacing="1">
<tr valign="baseline">
<td nowrap align="right">Batch Begin:</td>
<td><input type="text" name="batch_no" value=""
size="32"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right">User Number:</td>
<td><input type="text" name="batch_user_num"
value="<cfoutput>#cgi.AUTH_TYPE#</cfoutput>"
size="32"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Client End Date:</td>
<td><input type="text" name="week_end"
value="<cfoutput>#today#</cfoutput>"
size="32"></td>
</tr>
<tr valign="baseline">
<td nowrap align="right">Pull Flags: </td>
<td><input name="batch_pflag" type="text"
id="batch_pflag" size="3"></td>
</tr>
<tr valign="baseline">
<td colspan="2" align="right" nowrap><div
align="center">
<input type="submit" value="Load Clock File">
</div></td>
</tr>
</table>
<input type="hidden" name="InsertRecord"
value="loadreceiver">
<input type="hidden" name="batch_date"
value="<cfoutput>#dateformat(NOW(),
'yyyy-m-d')#</cfoutput>">
</form>
</body>
</html>
4
Replies
AdChoices