-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathloanStats.cfm
249 lines (232 loc) · 8.57 KB
/
loanStats.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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
<cfinclude template="/includes/_header.cfm">
<script src="/includes/sorttable.js"></script>
<cfset title="Loan and Citation statistics">
<cfparam name="loanto" default="">
<cfparam name="loantype" default="">
<cfparam name="loanstatus" default="">
<cfparam name="collectionid" default="">
<cfparam name="citations" default="">
<cfparam name="itemsloaned" default="">
<cfquery name="ctLoanStatus" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#" cachedwithin="#createtimespan(0,0,60,0)#">
select loan_status from ctloan_status order by loan_status
</cfquery>
<cfquery name="ctcollection" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#" cachedwithin="#createtimespan(0,0,60,0)#">
select * from collection order by guid_prefix
</cfquery>
<cfquery name="ctLoanType" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#" cachedwithin="#createtimespan(0,0,60,0)#">
select loan_type from ctloan_type order by loan_type
</cfquery>
<cfquery name="ctStatus" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#" cachedwithin="#createtimespan(0,0,60,0)#">
select loan_status from ctloan_status order by loan_status
</cfquery>
<cfoutput>
<form name="f" method="get" action="loanStats.cfm">
<input type="hidden" name="action" value="srch">
<label for="collectionid">Collection
</label>
<select name="collectionid" size="1" id="collectionid">
<option value=""></option>
<cfloop query="ctcollection">
<option <cfif collectionid is ctcollection.collection_id> selected="selected" </cfif> value="#ctcollection.collection_id#">#ctcollection.collection#</option>
</cfloop>
</select>
<label for="loanto">Loaned To Person</label>
<input type="text" name="loanto" id="loanto" value="#loanto#">
<label for="loantype">Loan Type</label>
<select name="loantype" id="loantype" class="reqdClr">
<option value=""></option>
<cfloop query="ctLoanType">
<option <cfif loantype is ctLoanType.loan_type> selected="selected" </cfif>value="#ctLoanType.loan_type#">#ctLoanType.loan_type#</option>
</cfloop>
</select>
<label for="loanstatus">Loan Status</label>
<select name="loanstatus" id="loanstatus" class="reqdClr">
<option value=""></option>
<cfloop query="ctLoanStatus">
<option <cfif loanstatus is ctLoanStatus.loan_status> selected="selected" </cfif> value="#ctLoanStatus.loan_status#">#ctLoanStatus.loan_status#</option>
</cfloop>
</select>
<label for="citations">Citations</label>
<select name="citations" id="citations" class="reqdClr">
<option value="">whatever</option>
<option <cfif citations is 0> selected="selected" </cfif> value="0">has none</option>
<option <cfif citations is 1> selected="selected" </cfif> value="1">has some</option>
</select>
<label for="itemsloaned">Items Loaned</label>
<select name="itemsloaned" id="itemsloaned" class="reqdClr">
<option value="">whatever</option>
<option <cfif itemsloaned is 0> selected="selected" </cfif> value="0">has none</option>
<option <cfif itemsloaned is 1> selected="selected" </cfif> value="1">has some</option>
</select>
<br><input type="submit" value="go">
</form>
<cfif action is "srch">
<cfquery name="loanData" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey)#">
select
guid_prefix,
collection_id,
TRANSACTION_ID,
loan_number,
loan_type,
loaned_to,
LOAN_STATUS,
RETURN_DUE_DATE,
TRANS_DATE,
count(distinct(derived_from_cat_item)) CntCatNum,
count(distinct(citationID)) cntCited,
trans_remarks
from (
select
guid_prefix,
collection.collection_id,
loan.TRANSACTION_ID,
loan.loan_number,
loan_type,
concattransagent(loan.TRANSACTION_ID,'received by') loaned_to,
LOAN_STATUS,
RETURN_DUE_DATE,
TRANS_DATE,
specimen_part.derived_from_cat_item,
citation.collection_object_id citationID,
trans_remarks
from
loan,
trans,
loan_item,
specimen_part,
citation,
collection
where
loan.transaction_id = trans.transaction_id and
trans.collection_id=collection.collection_id and
loan.transaction_id=loan_item.transaction_id (+) and
loan_item.collection_object_id=specimen_part.collection_object_id (+) and
specimen_part.derived_from_cat_item=citation.collection_object_id (+) union
select
guid_prefix,
collection.collection_id,
loan.TRANSACTION_ID,
loan.loan_number,
loan_type,
concattransagent(loan.TRANSACTION_ID,'received by') loaned_to,
LOAN_STATUS,
RETURN_DUE_DATE,
TRANS_DATE,
specimen_part.derived_from_cat_item,
citation.collection_object_id citationID,
trans_remarks
from
loan,
trans,
loan_item,
specimen_part,
citation,
collection
where
loan.transaction_id = trans.transaction_id and
trans.collection_id=collection.collection_id and
loan.transaction_id=loan_item.transaction_id (+) and
loan_item.collection_object_id=specimen_part.derived_from_cat_item (+) and
loan_item.collection_object_id=citation.collection_object_id (+)
)
where 1=1
<cfif len(loanto) gt 0>
and upper(loaned_to) like '%#ucase(loanto)#%'
</cfif>
<cfif len(loantype) gt 0>
and loan_type='#loantype#'
</cfif>
<cfif len(loanstatus) gt 0>
and loan_status='#loanstatus#'
</cfif>
<cfif len(collectionid) gt 0>
and collection_id=#collectionid#
</cfif>
<cfif len(citations) gt 0>
<cfif citations is 0>
and citationID is null
<cfelse>
and citationID is not null
</cfif>
</cfif>
<cfif len(itemsloaned) gt 0>
<cfif itemsloaned is 0>
and derived_from_cat_item is null
<cfelse>
and derived_from_cat_item is not null
</cfif>
</cfif>
group by
guid_prefix,
collection_id,
TRANSACTION_ID,
loan_number,
loan_type,
loaned_to,
LOAN_STATUS,
RETURN_DUE_DATE,
TRANS_DATE,
trans_remarks
</cfquery>
<cfset clist="Collection,Loan,Type,LoanedTo,Status,TransDate,DueDate,ItemsLoaned,Citations,TransRemarks">
<cfset fileDir = "#Application.webDirectory#">
<cfset variables.encoding="UTF-8">
<cfset fname = "loan-citation-stats.csv">
<cfset variables.fileName="#Application.webDirectory#/download/#fname#">
<cfscript>
variables.joFileWriter = createObject('Component', '/component.FileWriter').init(variables.fileName, variables.encoding, 32768);
variables.joFileWriter.writeLine(ListQualify(clist,'"'));
</cfscript>
<h2>Loan Statistics</h2>
<div style="background-color:lightgray;font-size:small;padding:1em; width:50%; align:center;margin-left:3em;margin:1em;">
Citations apply to cataloged items and do not reflect activity resulting from any particular loan.
<p>
Each line is one loan/collection/citation combination; information may be repeated. Showing #loanData.recordcount# rows.
</p>
<p>
Click headers to sort.
</p>
</div>
<p><a href="/download/#fname#">CSV</a></p>
<table border id="t" class="sortable">
<tr>
<th>Collection</th>
<th>Loan</th>
<th>Type</th>
<th>Loaned To</th>
<th>Status</th>
<th>Trans Date</th>
<th>Due Date</th>
<th>Items Loaned</th>
<th>Citations</th>
<th>TransRemarks</th>
</tr>
<cfloop query="loanData">
<cfset oneLine = '"#guid_prefix#","#loan_number#","#loan_type#","#loaned_to#","#LOAN_STATUS#","#dateformat(TRANS_DATE,"yyyy-mm-dd")#","#dateformat(RETURN_DUE_DATE,"yyyy-mm-dd")#","#CntCatNum#","#cntCited#","#trans_remarks#"'>
<cfscript>
variables.joFileWriter.writeLine(oneLine);
</cfscript>
<tr>
<td nowrap="nowrap">#guid_prefix#</td>
<td nowrap="nowrap"><a href="/Loan.cfm?action=editLoan&TRANSACTION_ID=#TRANSACTION_ID#">#loan_number#</a></td>
<td nowrap="nowrap">#loan_type#</td>
<td nowrap="nowrap">#loaned_to#</td>
<td>#LOAN_STATUS#</td>
<td nowrap="nowrap">#dateformat(TRANS_DATE,"yyyy-mm-dd")# </td>
<td nowrap="nowrap">#dateformat(RETURN_DUE_DATE,"yyyy-mm-dd")# </td>
<td>
<a href="/SpecimenResults.cfm?loan_trans_id=#loanData.TRANSACTION_ID#&collection_id=#loanData.collection_id#">#CntCatNum#</a>
</td>
<td><a href="/SpecimenResults.cfm?loan_trans_id=#loanData.TRANSACTION_ID#&collection_id=#loanData.collection_id#&type_status=any">
#cntCited#</a>
</td>
<td>#trans_remarks#</td>
</tr>
</cfloop>
<cfscript>
variables.joFileWriter.close();
</cfscript>
</table>
</cfif>
</cfoutput>
<cfinclude template="/includes/_footer.cfm">