forked from ArctosDB/arctos
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathLoadBarcodes.cfm
145 lines (128 loc) · 6.57 KB
/
LoadBarcodes.cfm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
<cfinclude template = "includes/_header.cfm">
<!--------------
drop table cf_temp_barcodeload;
create table cf_temp_barcodeload (
key number not null,
child_barcode varchar2(255) not null,
parent_barcode varchar2(255) not null,
install_date date);
create or replace public synonym cf_temp_barcodeload for cf_temp_barcodeload;
grant all on cf_temp_barcodeload to manage_container;
CREATE OR REPLACE TRIGGER cf_temp_barcodeload_key
before insert ON cf_temp_barcodeload
for each row
begin
if :NEW.key is null then
select somerandomsequence.nextval into :new.key from dual;
end if;
end;
/
sho err
alter table cf_temp_barcodeload add status varchar2(255);
alter table cf_temp_barcodeload add child_id number;
alter table cf_temp_barcodeload add parent_id number;
--------------->
<cfif action is "nothing">
<cfoutput>
Upload container scans
<br>
Duplicate scans will be ignored.
<p>CSV headers are <strong>child_barcode,parent_barcode</strong></p>
Upload a new file: <br>
<cfform action="LoadBarcodes.cfm" method="post" enctype="multipart/form-data">
<input type="hidden" name="action" value="newScans" />
<input type="file" name="FiletoUpload" size="45">
<input type="submit" value="Upload this file" class="savBtn">
</cfform>
</cfoutput>
</cfif>
<cfif action is "newScans">
<cfquery name="killOld" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
delete from cf_temp_barcodeload
</cfquery>
<cffile action="READ" file="#FiletoUpload#" variable="fileContent">
<cfset fileContent=replace(fileContent,"'","''","all")>
<cfset arrResult = CSVToArray(CSV = fileContent.Trim()) />
<cfset colNames="">
<cfloop from="1" to ="#ArrayLen(arrResult)#" index="o">
<cfset colVals="">
<cfloop from="1" to ="#ArrayLen(arrResult[o])#" index="i">
<cfset thisBit=arrResult[o][i]>
<cfif #o# is 1>
<cfset colNames="#colNames#,#thisBit#">
<cfelse>
<cfset colVals="#colVals#,'#thisBit#'">
</cfif>
</cfloop>
<cfif #o# is 1>
<cfset colNames=replace(colNames,",","","first")>
</cfif>
<cfif len(#colVals#) gt 1>
<cfset colVals=replace(colVals,",","","first")>
<cfquery name="ins" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
insert into cf_temp_barcodeload (#colNames#) values (#preservesinglequotes(colVals)#)
</cfquery>
</cfif>
</cfloop>
<a href="LoadBarcodes.cfm?action=verify">data loaded to temp table - click to verify</a>
</cfif>
<cfif action is "verify">
<cfquery name="child_not_found" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set status='child_not_found' where child_barcode not in (select barcode from container where barcode is not null)
</cfquery>
<cfquery name="parent_not_found" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set status='parent_not_found' where parent_barcode not in (select barcode from container where barcode is not null)
</cfquery>
<cfquery name="child_is_label" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set status='child_is_label' where child_barcode in
(select barcode from container where barcode is not null and container_type like '%label%')
</cfquery>
<cfquery name="parent_is_label" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set status='parent_is_label' where parent_barcode in
(select barcode from container where barcode is not null and container_type like '%label%')
</cfquery>
<cfquery name="infinite_loop" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set status='infinite_loop' where parent_barcode = child_barcode
</cfquery>
<cfquery name="parent_is_colobj" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set status='parent_is_colobj' where parent_barcode in
(select barcode from container where barcode is not null and container_type = 'collection object')
</cfquery>
<cfquery name="ucid" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set child_id=(select container_id from container where container.barcode=cf_temp_barcodeload.child_barcode)
</cfquery>
<cfquery name="upid" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set parent_id=(select container_id from container where container.barcode=cf_temp_barcodeload.parent_barcode)
</cfquery>
<cfquery name="parent_is_colobj" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update cf_temp_barcodeload set status='barcode_not_found' where child_id is null or parent_id is null
</cfquery>
<cfquery name="d" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
select * from cf_temp_barcodeload where status is not null
</cfquery>
<cfif d.recordcount gt 0>
The data will not load.
<cfdump var=#d#>
<cfelse>
The data will probably load - <a href="LoadBarcodes.cfm?action=load">click to continue....</a>
</cfif>
</cfif>
<cfif action is "load">
<cfquery name="d" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
select * from cf_temp_barcodeload
</cfquery>
<cftransaction>
<cfloop query="d">
<cfquery name="d" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
update container set parent_container_id=#parent_id# where container_id=#child_id#
</cfquery>
</cfloop>
</cftransaction>
<p>
Errors above? nothing loaded - try again.
</p>
<p>
No errors? All done.
</p>
</cfif>
<cfinclude template = "includes/_footer.cfm">