|
1 package org.campware.cream.modules.actions;
2
3
42
43 import java.util.Date  ;
44 import java.text.ParsePosition ;
45 import org.apache.turbine.modules.actions.VelocitySecureAction;
46 import org.apache.turbine.util.RunData;
47 import org.apache.turbine.util.security.AccessControlList;
48
49
50 import java.util.List ;
51 import java.util.ArrayList ;
52 import java.math.BigDecimal ;
53
54 import java.text.SimpleDateFormat ;
55 import java.text.DecimalFormat ;
56 import java.text.DecimalFormatSymbols ;
57
58 import org.apache.velocity.context.Context;
59
60 import org.apache.torque.util.Criteria;
61 import org.apache.torque.util.BasePeer;
62
63 import com.workingdogs.village.Record;
64
65 import org.campware.cream.om.PaymentPeer;
66 import org.campware.cream.om.CustomerPeer;
67 import org.campware.cream.om.ProductPeer;
68 import org.campware.cream.om.ProjectPeer;
69 import org.campware.cream.om.PaymentItemPeer;
70 import org.campware.cream.om.OnlineSubscriptionPeer;
71 import org.campware.cream.om.PrintSubscriptionPeer;
72 import org.campware.cream.om.ShipmentPeer;
73 import org.campware.cream.om.CurrencyPeer;
74
75
79 public class ReportSQL extends VelocitySecureAction
80 {
81
85 public void doReport(RunData data, Context context)
86 throws Exception
87 {
88
89 try
90 {
91
96 int reportid= data.getParameters().getInt("reportid", 0);
97 Criteria criteria = new Criteria();
98
99 if (reportid==1){
100 criteria.addJoin(PaymentPeer.PAYMENT_ID, PaymentItemPeer.PAYMENT_ID);
101 criteria.addJoin(CustomerPeer.CUSTOMER_ID, PaymentItemPeer.CUSTOMER_ID);
102 criteria.addJoin(ProductPeer.PRODUCT_ID, PaymentItemPeer.PRODUCT_ID);
103 criteria.addJoin(ProjectPeer.PROJECT_ID, PaymentItemPeer.PROJECT_ID);
104
105 criteria.addSelectColumn(PaymentItemPeer.PRODUCT_ID);
106 criteria.addSelectColumn("SUM(" + PaymentItemPeer.ITEM_TOTAL + ")");
107 criteria.addSelectColumn(ProductPeer.PRODUCT_CODE);
108 criteria.addSelectColumn(ProductPeer.PRODUCT_DISPLAY);
109
110 setPaymentCriteria(data, criteria);
111 setCustomerCriteria(data, criteria);
112 setProjectCriteria(data, criteria);
113 setProductCriteria(data, criteria);
114
115 criteria.addGroupByColumn(PaymentItemPeer.PRODUCT_ID);
116 criteria.addGroupByColumn(ProductPeer.PRODUCT_CODE);
117 criteria.addGroupByColumn(ProductPeer.PRODUCT_DISPLAY);
118
119 criteria.addAscendingOrderByColumn(ProductPeer.PRODUCT_CODE);
120
122 List records = BasePeer.doSelect(criteria);
123 List results = new ArrayList ();
124
125 for (int i = 0; i < records.size(); i++) {
126 results.add( ((Record) records.get(i)) );
127 }
128 context.put ("entries", results);
129
130 Criteria critall = new Criteria();
131
132 critall.addJoin(PaymentPeer.PAYMENT_ID, PaymentItemPeer.PAYMENT_ID);
133 critall.addJoin(CustomerPeer.CUSTOMER_ID, PaymentItemPeer.CUSTOMER_ID);
134 critall.addJoin(ProductPeer.PRODUCT_ID, PaymentItemPeer.PRODUCT_ID);
135 critall.addJoin(ProjectPeer.PROJECT_ID, PaymentItemPeer.PROJECT_ID);
136
137 critall.addSelectColumn("SUM(" + PaymentItemPeer.ITEM_TOTAL + ")");
138
139 setPaymentCriteria(data, critall);
140 setCustomerCriteria(data, critall);
141 setProjectCriteria(data, critall);
142 setProductCriteria(data, critall);
143
144 List sumrecord = BasePeer.doSelect(critall);
146 BigDecimal sumAll= ((Record) sumrecord.get(0)).getValue(1).asBigDecimal();
147 context.put ("sumall", sumAll);
148
149 }else if (reportid==2){
150 criteria.addJoin(PaymentPeer.PAYMENT_ID, PaymentItemPeer.PAYMENT_ID);
151 criteria.addJoin(CurrencyPeer.CURRENCY_ID, PaymentItemPeer.CURRENCY_ID);
152 criteria.addJoin(CustomerPeer.CUSTOMER_ID, PaymentItemPeer.CUSTOMER_ID);
153 criteria.addJoin(ProductPeer.PRODUCT_ID, PaymentItemPeer.PRODUCT_ID);
154 criteria.addJoin(ProjectPeer.PROJECT_ID, PaymentItemPeer.PROJECT_ID);
155
156 criteria.addSelectColumn(PaymentItemPeer.CURRENCY_ID);
157 criteria.addSelectColumn("SUM(" + PaymentItemPeer.ITEM_CURR_TOTAL + ")");
158 criteria.addSelectColumn(CurrencyPeer.CURRENCY_CODE);
159 criteria.addSelectColumn(CurrencyPeer.CURRENCY_NAME);
160
161 setPaymentCriteria(data, criteria);
162 setCustomerCriteria(data, criteria);
163 setProjectCriteria(data, criteria);
164 setProductCriteria(data, criteria);
165
166 criteria.addGroupByColumn(PaymentItemPeer.CURRENCY_ID);
167 criteria.addGroupByColumn(CurrencyPeer.CURRENCY_CODE);
168 criteria.addGroupByColumn(CurrencyPeer.CURRENCY_NAME);
169
170 criteria.addAscendingOrderByColumn(CurrencyPeer.CURRENCY_CODE);
171
173 List records = BasePeer.doSelect(criteria);
174 List results = new ArrayList ();
175
176 for (int i = 0; i < records.size(); i++) {
177 results.add( ((Record) records.get(i)) );
178 }
179 context.put ("entries", results);
180
181 }else if (reportid==3){
182 criteria.addJoin(CustomerPeer.CUSTOMER_ID, OnlineSubscriptionPeer.CUSTOMER_ID);
183 criteria.addJoin(ProductPeer.PRODUCT_ID, OnlineSubscriptionPeer.PRODUCT_ID);
184 criteria.addJoin(ProjectPeer.PROJECT_ID, OnlineSubscriptionPeer.PROJECT_ID);
185
186 criteria.addSelectColumn(OnlineSubscriptionPeer.PRODUCT_ID);
187 criteria.addSelectColumn("SUM(" + OnlineSubscriptionPeer.QUANTITY + ")");
188 criteria.addSelectColumn(ProductPeer.PRODUCT_CODE);
189 criteria.addSelectColumn(ProductPeer.PRODUCT_DISPLAY);
190
191 setOnlineSubscriptionCriteria(data, criteria);
192 setCustomerCriteria(data, criteria);
193 setProjectCriteria(data, criteria);
194 setProductCriteria(data, criteria);
195
196 criteria.addGroupByColumn(OnlineSubscriptionPeer.PRODUCT_ID);
197 criteria.addGroupByColumn(ProductPeer.PRODUCT_CODE);
198 criteria.addGroupByColumn(ProductPeer.PRODUCT_DISPLAY);
199
200 criteria.addAscendingOrderByColumn(ProductPeer.PRODUCT_CODE);
201
203 List records = BasePeer.doSelect(criteria);
204 List results = new ArrayList ();
205
206 for (int i = 0; i < records.size(); i++) {
207 results.add( ((Record) records.get(i)) );
208 }
209 context.put ("entries", results);
210
211 Criteria critall = new Criteria();
212
213 critall.addJoin(CustomerPeer.CUSTOMER_ID, OnlineSubscriptionPeer.CUSTOMER_ID);
214 critall.addJoin(ProductPeer.PRODUCT_ID, OnlineSubscriptionPeer.PRODUCT_ID);
215 critall.addJoin(ProjectPeer.PROJECT_ID, OnlineSubscriptionPeer.PROJECT_ID);
216
217 critall.addSelectColumn("SUM(" + OnlineSubscriptionPeer.QUANTITY + ")");
218
219 setOnlineSubscriptionCriteria(data, critall);
220 setCustomerCriteria(data, critall);
221 setProjectCriteria(data, critall);
222 setProductCriteria(data, critall);
223
224 List sumrecord = BasePeer.doSelect(critall);
226 int sumAll= ((Record) sumrecord.get(0)).getValue(1).asInt();
227 context.put ("sumall", new Integer (sumAll));
228
229 }else if (reportid==4){
230 criteria.addJoin(CustomerPeer.CUSTOMER_ID, PrintSubscriptionPeer.CUSTOMER_ID);
231 criteria.addJoin(ProductPeer.PRODUCT_ID, PrintSubscriptionPeer.PRODUCT_ID);
232 criteria.addJoin(ProjectPeer.PROJECT_ID, PrintSubscriptionPeer.PROJECT_ID);
233
234 criteria.addSelectColumn(PrintSubscriptionPeer.PRODUCT_ID);
235 criteria.addSelectColumn("SUM(" + PrintSubscriptionPeer.QUANTITY + ")");
236 criteria.addSelectColumn(ProductPeer.PRODUCT_CODE);
237 criteria.addSelectColumn(ProductPeer.PRODUCT_DISPLAY);
238
239 setPrintSubscriptionCriteria(data, criteria);
240 setCustomerCriteria(data, criteria);
241 setProjectCriteria(data, criteria);
242 setProductCriteria(data, criteria);
243
244 criteria.addGroupByColumn(PrintSubscriptionPeer.PRODUCT_ID);
245 criteria.addGroupByColumn(ProductPeer.PRODUCT_CODE);
246 criteria.addGroupByColumn(ProductPeer.PRODUCT_DISPLAY);
247
248 criteria.addAscendingOrderByColumn(ProductPeer.PRODUCT_CODE);
249
251 List records = BasePeer.doSelect(criteria);
252 List results = new ArrayList ();
253
254 for (int i = 0; i < records.size(); i++) {
255 results.add( ((Record) records.get(i)) );
256 }
257 context.put ("entries", results);
258
259 Criteria critall = new Criteria();
260
261 critall.addJoin(CustomerPeer.CUSTOMER_ID, PrintSubscriptionPeer.CUSTOMER_ID);
262 critall.addJoin(ProductPeer.PRODUCT_ID, PrintSubscriptionPeer.PRODUCT_ID);
263 critall.addJoin(ProjectPeer.PROJECT_ID, PrintSubscriptionPeer.PROJECT_ID);
264
265 critall.addSelectColumn("SUM(" + PrintSubscriptionPeer.QUANTITY + ")");
266
267 setPrintSubscriptionCriteria(data, critall);
268 setCustomerCriteria(data, critall);
269 setProjectCriteria(data, critall);
270 setProductCriteria(data, critall);
271
272 List sumrecord = BasePeer.doSelect(critall);
274 int sumAll= ((Record) sumrecord.get(0)).getValue(1).asInt();
275 context.put ("sumall", new Integer (sumAll));
276
277 }else if (reportid==5){
278 criteria.addJoin(CustomerPeer.CUSTOMER_ID, PrintSubscriptionPeer.RECIPIENT_ID);
279 criteria.addJoin(ProductPeer.PRODUCT_ID, PrintSubscriptionPeer.PRODUCT_ID);
280 criteria.addJoin(ProjectPeer.PROJECT_ID, PrintSubscriptionPeer.PROJECT_ID);
281
282 setPrintSubscriptionCriteria(data, criteria);
283 setCustomerCriteria(data, criteria);
284 setProjectCriteria(data, criteria);
285 setProductCriteria(data, criteria);
286
287 criteria.addAscendingOrderByColumn(CustomerPeer.CUSTOMER_DISPLAY);
288
290 context.put ("entries", PrintSubscriptionPeer.doSelect(criteria));
291
292 }else if (reportid==6){
293 criteria.addJoin(CustomerPeer.CUSTOMER_ID, ShipmentPeer.RECIPIENT_ID);
294 criteria.addJoin(ProjectPeer.PROJECT_ID, ShipmentPeer.PROJECT_ID);
295
296 setShipmentCriteria(data, criteria);
297 setCustomerCriteria(data, criteria);
298 setProjectCriteria(data, criteria);
299
300 criteria.addAscendingOrderByColumn(CustomerPeer.CUSTOMER_DISPLAY);
301
303 context.put ("entries", ShipmentPeer.doSelect(criteria));
304 }
305
306 context.put ("reptitle", data.getParameters().getString("reptitle"));
307
308 context.put("df", new SimpleDateFormat ("dd.MM.yyyy"));
310 DecimalFormatSymbols symb= new DecimalFormatSymbols ();
311 symb.setDecimalSeparator('.');
312 context.put("af", new DecimalFormat ("0.00", symb));
313 context.put("rf", new DecimalFormat ("0.000000", symb));
314 context.put("today", new Date ());
315
316 }
317 catch (Exception e)
318 {
319 throw(e);
320 }
321
322 }
323
324 private void setPaymentCriteria(RunData data, Criteria criteria)
325 throws Exception
326 {
327 Date fromDate= parseDate(data.getParameters().getString("fromdate"));
328 Date toDate= parseDate(data.getParameters().getString("todate"));
329 int status= data.getParameters().getInt("status", 30);
330
331 if (status==50){
332 Criteria.Criterion b1 = criteria.getNewCriterion(PaymentPeer.CLOSED_DATE, fromDate, Criteria.GREATER_EQUAL);
333 Criteria.Criterion b2 = criteria.getNewCriterion(PaymentPeer.CLOSED_DATE, toDate, Criteria.LESS_THAN);
334 criteria.add( b1.and( b2));
335 }else{
336 Criteria.Criterion b1 = criteria.getNewCriterion(PaymentPeer.ISSUED_DATE, fromDate, Criteria.GREATER_EQUAL);
337 Criteria.Criterion b2 = criteria.getNewCriterion(PaymentPeer.ISSUED_DATE, toDate, Criteria.LESS_THAN);
338 criteria.add( b1.and( b2));
339 }
340
341 criteria.add(PaymentPeer.STATUS, new Integer (status), Criteria.EQUAL);
342 }
343
344 private void setOnlineSubscriptionCriteria(RunData data, Criteria criteria)
345 throws Exception
346 {
347 Date fromDate= parseDate(data.getParameters().getString("fromdate"));
348 Date toDate= parseDate(data.getParameters().getString("todate"));
349 int status= data.getParameters().getInt("status", 30);
350
351 if (status==50){
352 Criteria.Criterion b1 = criteria.getNewCriterion(OnlineSubscriptionPeer.CLOSED_DATE, fromDate, Criteria.GREATER_EQUAL);
353 Criteria.Criterion b2 = criteria.getNewCriterion(OnlineSubscriptionPeer.CLOSED_DATE, toDate, Criteria.LESS_THAN);
354 criteria.add( b1.and( b2));
355 }else{
356 Criteria.Criterion b1 = criteria.getNewCriterion(OnlineSubscriptionPeer.ISSUED_DATE, fromDate, Criteria.GREATER_EQUAL);
357 Criteria.Criterion b2 = criteria.getNewCriterion(OnlineSubscriptionPeer.ISSUED_DATE, toDate, Criteria.LESS_THAN);
358 criteria.add( b1.and( b2));
359 }
360
361 criteria.add(OnlineSubscriptionPeer.STATUS, new Integer (status), Criteria.EQUAL);
362 }
363
364 private void setPrintSubscriptionCriteria(RunData data, Criteria criteria)
365 throws Exception
366 {
367 Date fromDate= parseDate(data.getParameters().getString("fromdate"));
368 Date toDate= parseDate(data.getParameters().getString("todate"));
369 int status= data.getParameters().getInt("status", 30);
370
371 if (status==50){
372 Criteria.Criterion b1 = criteria.getNewCriterion(PrintSubscriptionPeer.CLOSED_DATE, fromDate, Criteria.GREATER_EQUAL);
373 Criteria.Criterion b2 = criteria.getNewCriterion(PrintSubscriptionPeer.CLOSED_DATE, toDate, Criteria.LESS_THAN);
374 criteria.add( b1.and( b2));
375 }else{
376 Criteria.Criterion b1 = criteria.getNewCriterion(PrintSubscriptionPeer.ISSUED_DATE, fromDate, Criteria.GREATER_EQUAL);
377 Criteria.Criterion b2 = criteria.getNewCriterion(PrintSubscriptionPeer.ISSUED_DATE, toDate, Criteria.LESS_THAN);
378 criteria.add( b1.and( b2));
379 }
380
381 criteria.add(PrintSubscriptionPeer.STATUS, new Integer (status), Criteria.EQUAL);
382 }
383
384 private void setShipmentCriteria(RunData data, Criteria criteria)
385 throws Exception
386 {
387 Date fromDate= parseDate(data.getParameters().getString("fromdate"));
388 Date toDate= parseDate(data.getParameters().getString("todate"));
389 int status= data.getParameters().getInt("status", 30);
390
391 if (status==50){
392 Criteria.Criterion b1 = criteria.getNewCriterion(ShipmentPeer.CLOSED_DATE, fromDate, Criteria.GREATER_EQUAL);
393 Criteria.Criterion b2 = criteria.getNewCriterion(ShipmentPeer.CLOSED_DATE, toDate, Criteria.LESS_THAN);
394 criteria.add( b1.and( b2));
395 }else{
396 Criteria.Criterion b1 = criteria.getNewCriterion(ShipmentPeer.ISSUED_DATE, fromDate, Criteria.GREATER_EQUAL);
397 Criteria.Criterion b2 = criteria.getNewCriterion(ShipmentPeer.ISSUED_DATE, toDate, Criteria.LESS_THAN);
398 criteria.add( b1.and( b2));
399 }
400
401 criteria.add(ShipmentPeer.STATUS, new Integer (status), Criteria.EQUAL);
402 }
403
404 private void setCustomerCriteria(RunData data, Criteria criteria)
405 throws Exception
406 {
407 int customerId = data.getParameters().getInt("customerid", 999);
408 int customerCatId = data.getParameters().getInt("customercatid", 999);
409 int customerType = data.getParameters().getInt("customertype", 1);
410 int countryId = data.getParameters().getInt("countryid", 999);
411 int regionId = data.getParameters().getInt("regionid", 999);
412 int languageId = data.getParameters().getInt("languageid", 999);
413 int householdCatId = data.getParameters().getInt("householdcatid", 999);
414 int educationCatId = data.getParameters().getInt("educationcatid", 999);
415
416 if (customerId>999){
417 criteria.add(CustomerPeer.CUSTOMER_ID, new Integer (customerId), Criteria.EQUAL);
418 }else{
419
420 if (customerType>1){
421 criteria.add(CustomerPeer.CUSTOMER_TYPE, new Integer (customerType), Criteria.EQUAL);
422 }
423 if (customerCatId>999){
424 criteria.add(CustomerPeer.CUSTOMER_CAT_ID, new Integer (customerCatId), Criteria.EQUAL);
425 }
426 if (countryId>999){
427 criteria.add(CustomerPeer.COUNTRY_ID, new Integer (countryId), Criteria.EQUAL);
428 }
429 if (regionId>999){
430 criteria.add(CustomerPeer.REGION_ID, new Integer (regionId), Criteria.EQUAL);
431 }
432 if (languageId>999){
433 criteria.add(CustomerPeer.LANGUAGE_ID, new Integer (languageId), Criteria.EQUAL);
434 }
435 if (householdCatId>999){
436 criteria.add(CustomerPeer.HOUSEHOLD_CAT_ID, new Integer (householdCatId), Criteria.EQUAL);
437 }
438 if (educationCatId>999){
439 criteria.add(CustomerPeer.EDUCATION_CAT_ID, new Integer (educationCatId), Criteria.EQUAL);
440 }
441 }
442
443 }
444
445 private void setProjectCriteria(RunData data, Criteria criteria)
446 throws Exception
447 {
448 int projectId = data.getParameters().getInt("projectid", 999);
449 int projectCatId = data.getParameters().getInt("projectcatid", 999);
450
451 if (projectId>999){
452 criteria.add(ProjectPeer.PROJECT_ID, new Integer (projectId), Criteria.EQUAL);
453 }else{
454
455 if (projectCatId>999){
456 criteria.add(ProjectPeer.PROJECT_CAT_ID, new Integer (projectCatId), Criteria.EQUAL);
457 }
458 }
459
460 }
461
462 private void setProductCriteria(RunData data, Criteria criteria)
463 throws Exception
464 {
465
466 int productId = data.getParameters().getInt("productid", 999);
467 int productCatId = data.getParameters().getInt("productcatid", 999);
468 int productType = data.getParameters().getInt("producttype", 1);
469 int vendorId = data.getParameters().getInt("vendorid", 999);
470 int uomId = data.getParameters().getInt("uomid", 999);
471
472 if (productId>999){
473 criteria.add(ProductPeer.PRODUCT_ID, new Integer (productId), Criteria.EQUAL);
474 }else{
475
476 if (productType>1){
477 criteria.add(ProductPeer.PRODUCT_TYPE, new Integer (productType), Criteria.EQUAL);
478 }
479 if (productCatId>999){
480 criteria.add(ProductPeer.PRODUCT_CAT_ID, new Integer (productCatId), Criteria.EQUAL);
481 }
482 if (vendorId>999){
483 criteria.add(ProductPeer.VENDOR_ID, new Integer (vendorId), Criteria.EQUAL);
484 }
485 if (uomId>999 || (uomId<900 && uomId>100)){
486 criteria.add(ProductPeer.UOM_ID, new Integer (uomId), Criteria.EQUAL);
487 }
488 }
489 }
490
491
492
499 public void doPerform( RunData data,Context context )
500 throws Exception
501 {
502 data.setMessage("Can't find the button!");
503 }
504
505
514 protected boolean isAuthorized( RunData data ) throws Exception
515 {
516 int reportid= data.getParameters().getInt("reportid", 0);
517 boolean isAuthorized = false;
518
519 AccessControlList acl = data.getACL();
520
521 if (reportid==1 && data.getUser().hasLoggedIn() && (acl.hasPermission( "PAYMENT_VIEW") || acl.hasRole("turbine_root")))
522 {
523 isAuthorized = true;
524 }
525 else if (reportid==2 && data.getUser().hasLoggedIn() && (acl.hasPermission( "PAYMENT_VIEW") || acl.hasRole("turbine_root")))
526 {
527 isAuthorized = true;
528 }
529 else if (reportid==3 && data.getUser().hasLoggedIn() && (acl.hasPermission( "ONLINE_SUBSCRIPTION_VIEW") || acl.hasRole("turbine_root")))
530 {
531 isAuthorized = true;
532 }
533 else if (reportid==4 && data.getUser().hasLoggedIn() && (acl.hasPermission( "PRINT_SUBSCRIPTION_VIEW") || acl.hasRole("turbine_root")))
534 {
535 isAuthorized = true;
536 }
537 else if (reportid==5 && data.getUser().hasLoggedIn() && (acl.hasPermission( "PRINT_SUBSCRIPTION_VIEW") || acl.hasRole("turbine_root")))
538 {
539 isAuthorized = true;
540 }
541 else if (reportid==6 && data.getUser().hasLoggedIn() && (acl.hasPermission( "SHIPMENT_VIEW") || acl.hasRole("turbine_root")))
542 {
543 isAuthorized = true;
544 }
545 else
546 {
547 data.setMessage("Sorry, you don't have permission for this operation!");
548 this.setTemplate( data, "CreamError.vm");
549
550 isAuthorized = false;
551 }
552
553 return isAuthorized;
554 }
555
556 protected Date parseDateTime(String d)
557 throws Exception
558 {
559 SimpleDateFormat formatter = new SimpleDateFormat ("dd.MM.yyyy hh:mm:ss");
561 ParsePosition pos = new ParsePosition (0);
562 return formatter.parse(d, pos);
563 }
564
565 protected Date parseDate(String d)
566 throws Exception
567 {
568 SimpleDateFormat formatter = new SimpleDateFormat ("dd.MM.yyyy");
570 ParsePosition pos = new ParsePosition (0);
571 try{
572 Date myDate= formatter.parse(d, pos);
573 return myDate;
574 }
575 catch (Exception e)
576 {
577 return null;
578 }
579
580 }
581
582 }
583 |
Popular Tags |
Java API By Example, From Geeks To Geeks.