Advertisement

07.22.2008 at 08:42AM PDT, ID: 23585501
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
8.1

need help in creating a cron process to this program to send emails daily from teusday thru saturday

Asked by sadaf_syed in Java Programming Language

I have this application that works fine just now i need to add a email cron funtion to it so it send out emails daily from teusday thru saturday to inform people any new cases added or not. if new cases are added then case id and case details need to be emailed out if no new cases are added then an email stating no cases were added will go out. Start Free Trial 
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:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
573:
574:
575:
576:
577:
578:
579:
580:
581:
582:
583:
584:
585:
586:
587:
588:
589:
590:
591:
592:
593:
594:
595:
596:
597:
598:
599:
600:
601:
602:
603:
604:
605:
606:
607:
608:
609:
610:
611:
612:
613:
614:
615:
616:
617:
618:
619:
620:
621:
622:
623:
624:
625:
626:
627:
628:
629:
630:
631:
632:
633:
634:
635:
636:
637:
638:
639:
640:
641:
642:
643:
644:
645:
646:
647:
648:
649:
650:
651:
652:
653:
654:
655:
656:
657:
658:
659:
660:
661:
662:
663:
664:
665:
666:
667:
668:
669:
670:
671:
672:
673:
674:
675:
676:
677:
678:
679:
680:
681:
682:
683:
684:
685:
686:
687:
688:
689:
690:
691:
692:
693:
694:
695:
696:
697:
698:
699:
700:
701:
702:
703:
704:
705:
706:
707:
708:
709:
710:
711:
712:
713:
714:
715:
716:
717:
718:
719:
720:
721:
722:
723:
724:
725:
726:
727:
728:
729:
730:
731:
732:
733:
734:
735:
736:
737:
738:
739:
740:
741:
742:
743:
744:
745:
746:
747:
748:
749:
750:
751:
752:
753:
754:
755:
756:
757:
758:
759:
760:
761:
762:
763:
764:
765:
766:
767:
768:
769:
770:
771:
772:
773:
774:
775:
776:
777:
778:
779:
780:
781:
782:
783:
784:
785:
786:
787:
788:
789:
790:
791:
792:
793:
794:
795:
796:
797:
798:
799:
800:
801:
802:
803:
804:
805:
806:
807:
808:
809:
810:
811:
812:
813:
814:
815:
816:
817:
818:
819:
820:
821:
822:
823:
824:
825:
826:
827:
828:
829:
830:
831:
832:
833:
834:
835:
836:
837:
838:
839:
840:
841:
842:
843:
844:
845:
846:
847:
848:
849:
850:
851:
852:
853:
854:
855:
856:
857:
858:
859:
860:
861:
862:
863:
864:
865:
866:
867:
868:
869:
870:
871:
872:
873:
874:
875:
876:
877:
878:
879:
880:
881:
882:
883:
884:
885:
886:
887:
888:
889:
890:
891:
892:
893:
894:
895:
896:
897:
898:
899:
900:
901:
902:
903:
904:
905:
906:
907:
908:
909:
910:
911:
912:
913:
914:
915:
916:
917:
918:
919:
920:
921:
922:
923:
924:
925:
926:
927:
928:
929:
930:
931:
932:
933:
934:
935:
936:
937:
938:
939:
940:
941:
942:
943:
944:
945:
946:
947:
948:
949:
950:
951:
952:
953:
954:
955:
956:
957:
958:
959:
960:
961:
962:
963:
964:
965:
966:
967:
968:
969:
970:
971:
972:
973:
974:
975:
976:
977:
978:
979:
980:
981:
982:
983:
984:
985:
986:
987:
988:
989:
990:
991:
992:
993:
994:
995:
996:
997:
998:
999:
1000:
1001:
1002:
1003:
1004:
1005:
1006:
1007:
1008:
1009:
1010:
1011:
1012:
1013:
1014:
1015:
1016:
1017:
1018:
1019:
1020:
1021:
1022:
1023:
1024:
1025:
1026:
1027:
1028:
1029:
1030:
1031:
1032:
1033:
1034:
1035:
1036:
1037:
1038:
1039:
1040:
1041:
1042:
1043:
1044:
1045:
1046:
1047:
1048:
1049:
1050:
1051:
1052:
1053:
1054:
1055:
1056:
1057:
1058:
1059:
1060:
1061:
1062:
1063:
1064:
1065:
1066:
1067:
1068:
1069:
1070:
1071:
1072:
1073:
1074:
1075:
1076:
1077:
1078:
1079:
1080:
1081:
1082:

package com.comtrol.actions;
 
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;
import com.synfoserv.actions.Action;
import com.synfoserv.actions.ActionBase;
import com.synfoserv.actions.ActionRouter;
import com.synfoserv.actions.SUserAction;
import com.synfoserv.global.connections.*;
import com.synfoserv.global.security.*;
import com.synfoserv.global.util.Utils;
import com.synfoserv.beans.ContextBean;
import com.synfoserv.beans.PersonListBean;
import com.synfoserv.beans.SecurityBean;
import com.synfoserv.beans.SUserListBean;
import com.comtrol.global.IDGenerator;
import com.comtrol.beans.CaseListBean;
import com.comtrol.beans.CaseNoteListBean;
import com.comtrol.global.util.CookieManager;
 
public class CaseAction extends ActionBase implements Action{
  private HttpServletResponse m_response = null;
  private CookieManager cm = new CookieManager();  
  private HttpSession session = null;
  private String msUsername = null;
  private String msCaseId = null;
  private boolean mbLimit = false;
  private String msNotesCaseId = null;
  private String msUserEmail = null;
  private HttpServletRequest mRequest = null;
  private String mOnlineCaseURL = "http://my.comtrol.com/support/case.asp?case=";
  
  public CaseAction(){super();}
  
  //adds WHERE or AND to the where clause
  private StringBuffer addSyntax(StringBuffer sWhereClause){
    if (sWhereClause.length()>0){sWhereClause.append(" AND ");}
    else{sWhereClause.append(" WHERE ");}
    return sWhereClause;
  }
  
  private void caseDelete(HttpServletRequest req){
    try{
      Utils utilObj = new Utils();
      java.sql.Timestamp tsp = utilObj.currentTimestamp();
      String sCaseId = (String)req.getParameter("casedelid");
      cstmt = m_conDb.prepareCall("{call sp_case_delete(?,?,?)}");
      cstmt.setInt(1,Integer.parseInt(sCaseId));
      cstmt.setString(2,msUsername);
      cstmt.setTimestamp(3,tsp);
      cstmt.execute();
    }
    catch(Exception _e){_e.printStackTrace();}
  }
  
  private ArrayList caseEdit(HttpServletRequest req, boolean bOverride){
    ArrayList alCaseList = new ArrayList();
    ArrayList alCaseNoteList = new ArrayList();
    HttpSession  session = req.getSession();
    ContextBean cBean = (ContextBean) session.getAttribute("context");
    Utils utilObj = new Utils();
    java.sql.Timestamp tsp = utilObj.currentTimestamp();
    
    String sCaseId="0";
    String sAssignto = "";
    String sActionReq = "";
    String sPriority = "";
    String sOpenDate = "";
    String sCloseDate = "";
    String sStatus = "";
    String sSummary = "";
    String sNote = "";
    String sPartNo = "";
    String sOS = "";
    String sDriverVer = "";
 
    msNotesCaseId = null;
    
    try{
      //getting data from the request object'
      if (bOverride){
        if(msCaseId != null && !msCaseId.equals("0") && msCaseId.length() > 0){
          sCaseId = msCaseId;
          msCaseId = null;
        }else if(cBean.getCaseId() != null && !cBean.getCaseId().equals("0") && cBean.getCaseId().length() > 0){
          sCaseId = cBean.getCaseId();
        }        
      }
      else{
        sCaseId = (String)req.getParameter("caseid");
        msCaseId = null;
      }
      
      //for fetching case notes to display read only.
      msNotesCaseId = sCaseId;
      
      //IN -> CASE_ID,COMPANY_ID,DIVISION_ID,PERSON_ID
      cstmt = m_conDb.prepareCall("{call sp_case_edit(?,?,?,?)}");
      
      cstmt.setInt(1,Integer.parseInt(sCaseId));
      cstmt.setInt(2,0);
      cstmt.setInt(3,0);
      cstmt.setInt(4,0);
      
      cstmt.execute();
      rs = cstmt.getResultSet();
      
      if(rs.next()){
        CaseListBean clbObj = getListBean(rs,true);
        alCaseList.add(clbObj);
      }
    }
    catch(Exception _e){_e.printStackTrace();}
    
    return alCaseList;
  }
  
  private ArrayList getCaseNotesList(HttpServletRequest req){
    ArrayList alCaseNoteList = new ArrayList();
    HttpSession  session = req.getSession();
    
    String sCaseId = msNotesCaseId;
    
    try{
      if(sCaseId != null && !sCaseId.equals("0") && sCaseId.length() > 0){
        cstmt = m_conDb.prepareCall("{call sp_case_note_edit(?,?)}");
        cstmt.setInt(1,0);
        cstmt.setInt(2,Integer.parseInt(sCaseId));
        cstmt.execute();
        rs = cstmt.getResultSet();
      
        while(rs.next()){
          CaseNoteListBean cnlbObj = new CaseNoteListBean();
          cnlbObj.setCaseNoteId(Integer.toString(rs.getInt("CASE_NOTE_ID")));
          cnlbObj.setCaseId(Integer.toString(rs.getInt("CASE_ID")));
          cnlbObj.setSummary(utilObj.nullToEmpty(rs.getString("SUMMARY")));
          cnlbObj.setNote(utilObj.nullToEmpty(rs.getString("NOTE")));
          cnlbObj.setCreateDate(utilObj.formatDateTime(rs.getTimestamp("CREATE_DATE")));
          cnlbObj.setCreateUser(utilObj.nullToEmpty(rs.getString("CREATE_USER")));
          cnlbObj.setModifyDate(utilObj.formatDateTime(rs.getTimestamp("MODIFY_DATE")));
          cnlbObj.setModifyUser(utilObj.nullToEmpty(rs.getString("MODIFY_USER")));
          cnlbObj.setRespondTo(utilObj.nullToEmpty(rs.getString("RESPOND_TO")));
          alCaseNoteList.add(cnlbObj);
        }
      }
    }
    catch(Exception _e){_e.printStackTrace();}
    
    msNotesCaseId = null;
    
    return alCaseNoteList;
  }
  
  private ArrayList caseInsert(HttpServletRequest req){
    Utils ut = new Utils();
    Timestamp ts = ut.currentTimestamp();    
    ArrayList alCaseList = new ArrayList();
    CaseListBean clbObj = new CaseListBean();
    clbObj.setOpenDate(ut.extractDate(ts));
    clbObj.setAssignTo(msUsername);
    clbObj.setActionReq("TECHSUP");
    clbObj.setStatus("OPEN");
    alCaseList.add(clbObj);
    
    return alCaseList;
  }
  
  private void caseInsertSave(HttpServletRequest req){
    ContextBean cBean = (ContextBean)session.getAttribute("context");
    String sCompanyId = cBean.getCompanyId();
    String sDivisionId = cBean.getDivisionId();
    String sPersonId = cBean.getPersonId();
    
    try{
      Utils utilObj = new Utils();
      java.sql.Timestamp tsp = utilObj.currentTimestamp();
      
      //String sAssignToOriginal = req.getParameter("assigned_original"); will always be null
      String sAssignto = (String)req.getParameter("assignto");
      String sActionReq = (String)req.getParameter("actionrequired");
      String sPriority = (String)req.getParameter("priority");
      String sOpenDate = (String)req.getParameter("opendate");
      String sCloseDate = (String)req.getParameter("closedate");
      String sStatus = (String)req.getParameter("status");
      String sSummary = (String)req.getParameter("summary");
      String sNote = (String)req.getParameter("note");
      String sPartNo = (String)req.getParameter("partno");
      String sOS = (String)req.getParameter("opsys");
      String sDriverVer = (String)req.getParameter("driverversion");
      String sCaseType = req.getParameter("casetype");
      
      StringBuffer sSQL = new StringBuffer();
      sSQL.append("INSERT INTO CASE(");
      sSQL.append("CASE_ID,COMPANY_ID,DIVISION_ID,PERSON_ID,ASSIGN_TO,ACTION_REQ,");
      sSQL.append("PRIORITY,STATUS,PRODUCT,OS,OSDRIVERVER,OPEN_DATE,CLOSE_DATE,");
      sSQL.append("SUMMARY,NOTE,CREATE_DATE,CREATE_USER,MODIFY_DATE,");
      sSQL.append("MODIFY_USER,SYNC_DATE,SYNC_USER,ACTIVE,CASE_TYPE) ");
      sSQL.append("VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
      
      pstmt = m_conDb.prepareStatement(sSQL.toString());
      int iPrimaryKey = IDGenerator.getInstance().generateID(req,"case");
      pstmt.setInt(1,iPrimaryKey);
      pstmt.setInt(2,Integer.parseInt(sCompanyId));
      
      if (sDivisionId==null || sDivisionId.length()==0){
        pstmt.setInt(3, 0);
      } else {
        pstmt.setInt(3,Integer.parseInt(sDivisionId));
      }
      
      if (sPersonId==null || sPersonId.length()==0){
        pstmt.setInt(4, 0);
      } else {
        pstmt.setInt(4,Integer.parseInt(sPersonId));
      }
      
      pstmt.setString(5,sAssignto);
      pstmt.setString(6,sActionReq);
      pstmt.setString(7,sPriority);
      pstmt.setString(8,sStatus);
      pstmt.setString(9,sPartNo.toUpperCase());
      pstmt.setString(10,sOS.toUpperCase());
      pstmt.setString(11,sDriverVer.toUpperCase());
      pstmt.setString(12,sOpenDate);
      pstmt.setString(13,sCloseDate);
      pstmt.setString(14,sSummary);
      pstmt.setString(15,sNote);
      pstmt.setTimestamp(16,tsp);
      pstmt.setString(17,msUsername);
      pstmt.setTimestamp(18,tsp);
      pstmt.setString(19,msUsername);
      pstmt.setTimestamp(20,tsp);
      pstmt.setString(21,msUsername);
      pstmt.setString(22,"Y");
      pstmt.setString(23,sCaseType);
      pstmt.execute();
      
      msCaseId = String.valueOf(iPrimaryKey);
      
      if(!sAssignto.equalsIgnoreCase(msUsername)){
        notifyAcctOwner("Indeo Alert: New Customer Case Created",
                        "New Case #: " + msCaseId + " was assigned to you.",sAssignto);
      }
    }
    catch(Exception _e){
      _e.printStackTrace();
    }
  }//end caseInsertSave()
  
  private ArrayList caseSearch(HttpServletRequest req,boolean bOverride){
    ArrayList alCaseList = new ArrayList();
    HttpSession session = req.getSession();
    ContextBean cBean = (ContextBean)session.getAttribute("context");
    int iMaxRecordListing = Integer.parseInt(htConfig.get("max_record_listing").toString());
    
    String sSQL = null;
    String sCompanyId="0";
    String sDivisionId="0";
    String sPersonId="0";
    String sCaseId="0";
    
    String sCreatedBy = null;
    String sModifiedBy = null;
    String sCreatedStart = null;
    String sCreatedEnd = null;
    String sModifiedStart = null;
    String sModifiedEnd = null;
    String sTimeStamp = null;
    
    if(bOverride & cBean != null){
      sCompanyId = cBean.getCompanyId();
      sDivisionId = cBean.getDivisionId();
      sPersonId = cBean.getPersonId();
      sCaseId = cBean.getCaseId();
      
      if(sCompanyId == null || sCompanyId.length() <= 0){sCompanyId = "0";}
      if(sDivisionId == null || sDivisionId.length() <= 0){sDivisionId = "0";}
      if(sPersonId == null || sPersonId.length() <= 0){sPersonId = "0";}
      if(sCaseId == null || sCaseId.length() <= 0){sCaseId = "0";}
    }
    
    String sFName = req.getParameter("fname");
    String sLName = req.getParameter("lname");
    String sComName = req.getParameter("company");
    String sDivName = req.getParameter("division");
    String sAssignto = req.getParameter("assignto");
    String sActionReq = req.getParameter("actionrequired");
    String sPriority = req.getParameter("priority");
    String sOpenDate = req.getParameter("opendate");
    String sCloseDate = req.getParameter("closedate");
    String sStatus = req.getParameter("status");
    String sSummary = req.getParameter("summary");
    String sPartNo = req.getParameter("partno");
    String sOS = req.getParameter("opsys");
    String sDriverVer = req.getParameter("driverversion");
    String sNote = req.getParameter("note");
    sCreatedBy = req.getParameter("createdby");
    sModifiedBy = req.getParameter("modifiedby");
    sCreatedStart = req.getParameter("createstart");
    sCreatedEnd = req.getParameter("createend");
    sModifiedStart = req.getParameter("modifiedstart");
    sModifiedEnd = req.getParameter("modifiedend");
    
    try{
      if(sAssignto != null && sAssignto.trim().length() > 0)
        m_response.addCookie(new Cookie("caseassignto",sAssignto));
      else if(cm.cookieExists(req,"caseassignto"))
        m_response.addCookie(cm.deleteCookie(req,"caseassignto"));
 
 
      if(sActionReq != null && sActionReq.trim().length() > 0)
        m_response.addCookie(new Cookie("actionrequired",sActionReq));
      else if(cm.cookieExists(req,"actionrequired"))
        m_response.addCookie(cm.deleteCookie(req,"actionrequired"));
 
 
      if(sPriority != null && sPriority.trim().length() > 0)
        m_response.addCookie(new Cookie("priority",sPriority));
      else if(cm.cookieExists(req,"priority"))
        m_response.addCookie(cm.deleteCookie(req,"priority"));
 
 
      if(sOpenDate != null && sOpenDate.trim().length() > 0)
        m_response.addCookie(new Cookie("opendate",sOpenDate));
      else if(cm.cookieExists(req,"opendate"))
        m_response.addCookie(cm.deleteCookie(req,"opendate"));
 
 
      if(sCloseDate != null && sCloseDate.trim().length() > 0)
        m_response.addCookie(new Cookie("closedate",sCloseDate));
      else if(cm.cookieExists(req,"closedate"))
        m_response.addCookie(cm.deleteCookie(req,"closedate"));
 
 
      if(sStatus != null && sStatus.trim().length() > 0)
        m_response.addCookie(new Cookie("casestatus",sStatus));
      else if(cm.cookieExists(req,"casestatus"))
        m_response.addCookie(cm.deleteCookie(req,"casestatus"));
 
 
      if(sSummary != null && sSummary.trim().length() > 0)
        m_response.addCookie(new Cookie("casesummary",sSummary));
      else if(cm.cookieExists(req,"casesummary"))
        m_response.addCookie(cm.deleteCookie(req,"casesummary"));
 
 
      if(sPartNo != null && sPartNo.trim().length() > 0)
        m_response.addCookie(new Cookie("casepartno",sPartNo));
      else if(cm.cookieExists(req,"casepartno"))
        m_response.addCookie(cm.deleteCookie(req,"casepartno"));
 
 
      if(sOS != null && sOS.trim().length() > 0)
        m_response.addCookie(new Cookie("opsys",sOS));
      else if(cm.cookieExists(req,"opsys"))
        m_response.addCookie(cm.deleteCookie(req,"opsys"));
 
 
      if(sDriverVer != null && sDriverVer.trim().length() > 0)
        m_response.addCookie(new Cookie("driverversion",sDriverVer));
      else if(cm.cookieExists(req,"driverversion"))
        m_response.addCookie(cm.deleteCookie(req,"driverversion"));
 
 
      if(sNote != null && sNote.trim().length() > 0)
        m_response.addCookie(new Cookie("casenote",sNote));
      else if(cm.cookieExists(req,"casenote"))
        m_response.addCookie(cm.deleteCookie(req,"casenote"));
 
 
      if(sCreatedBy != null && sCreatedBy.trim().length() > 0)
        m_response.addCookie(new Cookie("casecreatedby",sCreatedBy));
      else if(cm.cookieExists(req,"casecreatedby"))
        m_response.addCookie(cm.deleteCookie(req,"casecreatedby"));
 
 
      if(sModifiedBy != null && sModifiedBy.trim().length() > 0)
        m_response.addCookie(new Cookie("casemodifiedby",sModifiedBy));
      else if(cm.cookieExists(req,"casemodifiedby"))
        m_response.addCookie(cm.deleteCookie(req,"casemodifiedby"));
 
 
      if(sCreatedStart != null && sCreatedStart.trim().length() > 0)
        m_response.addCookie(new Cookie("casecreatestart",sCreatedStart));
      else if(cm.cookieExists(req,"casecreatestart"))
        m_response.addCookie(cm.deleteCookie(req,"casecreatestart"));
 
 
      if(sCreatedEnd != null && sCreatedEnd.trim().length() > 0)
        m_response.addCookie(new Cookie("casecreateend",sCreatedEnd));
      else if(cm.cookieExists(req,"casecreateend"))
        m_response.addCookie(cm.deleteCookie(req,"casecreateend"));
 
 
      if(sModifiedStart != null && sModifiedStart.trim().length() > 0)
        m_response.addCookie(new Cookie("casemodifiedstart",sModifiedStart));
      else if(cm.cookieExists(req,"casemodifiedstart"))
        m_response.addCookie(cm.deleteCookie(req,"casemodifiedstart"));
 
 
      if(sModifiedEnd != null && sModifiedEnd.trim().length() > 0)
        m_response.addCookie(new Cookie("casemodifiedend",sModifiedEnd));
      else if(cm.cookieExists(req,"casemodifiedend"))
        m_response.addCookie(cm.deleteCookie(req,"casemodifiedend"));
    }catch(Exception e){System.out.println(e.getMessage());}
    
    
    StringBuffer sWhereClause = new StringBuffer();
    
    /*
    sWhereClause.append("SELECT C.*,COMPANY.COM_NAME,DIVISION.DIV_NAME, ");
    sWhereClause.append("PERSON.FNAME,PERSON.LNAME ");
    sWhereClause.append("FROM CASE C,COMPANY,DIVISION,PERSON,CASE_NOTE CN ");
    sWhereClause.append("WHERE C.COMPANY_ID = COMPANY.COMPANY_ID ");
    sWhereClause.append("AND C.DIVISION_ID = DIVISION.DIVISION_ID ");
    sWhereClause.append("AND C.PERSON_ID = PERSON.PERSON_ID ");
    sWhereClause.append("AND C.CASE_ID = CN.CASE_ID ");
    */
    
    sWhereClause.append("SELECT DISTINCT C.*,COMPANY.COM_NAME,DIVISION.DIV_NAME, ");
    sWhereClause.append("PERSON.FNAME,PERSON.LNAME FROM CASE C ");
    sWhereClause.append("INNER JOIN COMPANY ON C.COMPANY_ID = COMPANY.COMPANY_ID ");
    sWhereClause.append("LEFT JOIN DIVISION ON C.DIVISION_ID = DIVISION.DIVISION_ID ");
    sWhereClause.append("LEFT JOIN PERSON ON C.PERSON_ID = PERSON.PERSON_ID WHERE ");
//    sWhereClause.append("LEFT JOIN CASE_NOTE CN ON C.CASE_ID = CN.CASE_ID WHERE ");
    sWhereClause.append(" C.CASE_ID!=0 AND C.ACTIVE = 'Y' ");
 
    try{
      if(m_conDb==null) System.out.println("ERROR: NO CONNECTION");
      
      if(bOverride && !sCompanyId.equals("0")) {
        cstmt = m_conDb.prepareCall("{call sp_case_edit(?,?,?,?)}");
        
        cstmt.setInt(1,Integer.parseInt(sCaseId));
        cstmt.setInt(2,Integer.parseInt(sCompanyId));
        cstmt.setInt(3,Integer.parseInt(sDivisionId));
        cstmt.setInt(4,Integer.parseInt(sPersonId));
        
        cstmt.execute();
        rs = cstmt.getResultSet();
        
        while(rs.next()){
          CaseListBean clbObj = getListBean(rs,false);
          clbObj.setComName(utilObj.nullToEmpty(rs.getString("COM_NAME")));
          clbObj.setDivName(utilObj.nullToEmpty(rs.getString("DIV_NAME")));
          clbObj.setFName(utilObj.nullToEmpty(rs.getString("FNAME")));
          clbObj.setLName(utilObj.nullToEmpty(rs.getString("LNAME")));
          alCaseList.add(clbObj);
        }
      }else{
        boolean blnIsLimitedSearch = false;
        
        sCaseId = (String)req.getParameter("caseid");
        if(sCaseId != null && sCaseId.trim().length() > 0 && !sCaseId.trim().equalsIgnoreCase("0"))
          m_response.addCookie(new Cookie("caseid",sCaseId));
        else if(cm.cookieExists(req,"caseid"))
          m_response.addCookie(cm.deleteCookie(req,"caseid"));
 
        
        if(sCaseId != null && sCaseId.length() > 0 && !sCaseId.equals("0")) {
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.CASE_ID = " + sCaseId);
        }
        if(mbLimit){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.ASSIGN_TO = '" + msUsername + "'");
        }else if(sAssignto != null && sAssignto.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.ASSIGN_TO = '" + sAssignto + "'");
        }
        if(sActionReq != null && sActionReq.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.ACTION_REQ = '" + sActionReq + "'");
        }
        if(sPriority != null && sPriority.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.PRIORITY = '" + sPriority + "'");
        }
        if(sOpenDate != null && sOpenDate.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.OPEN_DATE = '" + sOpenDate + "'");
        }
        if(sCloseDate != null && sCloseDate.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.CLOSE_DATE = '" + sCloseDate + "'");
        }
        if(sStatus != null && sStatus.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.STATUS = '" + sStatus + "'");
        }
/*        
        if(sComName != null && sComName.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" UPPER(COM_NAME) like '%"+sComName.toUpperCase()+"%'");
        }
        if(sDivName != null && sDivName.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" UPPER(DIV_NAME) like '%" + sDivName.toUpperCase() + "%'");
        }
 */
        
/*        
        if(sFName != null && sFName.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" UPPER(FNAME) like '%" + sFName.toUpperCase() + "%'");
        }
        if(sLName != null && sLName.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" UPPER(LNAME) like '%" + sLName.toUpperCase() + "%'");
        }
 */
        if(sPartNo != null && sPartNo.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.PRODUCT like '%" + sPartNo.toUpperCase() + "%'");
        }
        if(sOS != null && sOS.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.OS like '" + sOS.toUpperCase() + "%'");
        }
        if(sDriverVer != null && sDriverVer.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.OSDRIVERVER like '%" + sDriverVer.toUpperCase() + "%'");
        }
        
        if(sModifiedEnd != null && sModifiedEnd.length() > 0){
          sModifiedEnd = utilObj.formatDateForStatement(sModifiedEnd);
          sTimeStamp = sModifiedEnd.substring(1,(sModifiedEnd.length()-1)) + " 23:59:59.999";
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.MODIFY_DATE <= TIMESTAMP('"+sTimeStamp+"') ");
//          sWhereClause.append(" (C.MODIFY_DATE <= TIMESTAMP('"+sTimeStamp+"')");
//          sWhereClause.append("  OR CN.MODIFY_DATE <= TIMESTAMP('"+sTimeStamp+"'))");
        }
        if(sCreatedEnd != null && sCreatedEnd.length() > 0){
          sCreatedEnd = utilObj.formatDateForStatement(sCreatedEnd);
          sTimeStamp = sCreatedEnd.substring(1,(sCreatedEnd.length()-1)) + " 23:59:59.999";
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.CREATE_DATE <= TIMESTAMP('"+sTimeStamp+"') ");
//          sWhereClause.append(" (C.CREATE_DATE <= TIMESTAMP('"+sTimeStamp+"')");
//          sWhereClause.append("  OR CN.CREATE_DATE <= TIMESTAMP('"+sTimeStamp+"'))");
        }
        if(sModifiedStart != null && sModifiedStart.length() > 0){
          sModifiedStart = utilObj.formatDateForStatement(sModifiedStart);
          sTimeStamp = sModifiedStart.substring(1,(sModifiedStart.length()-1)) + " 00:00:00.0";
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.MODIFY_DATE >= TIMESTAMP('"+sTimeStamp+"') ");
//          sWhereClause.append(" (C.MODIFY_DATE >= TIMESTAMP('"+sTimeStamp+"')");
//          sWhereClause.append("  OR CN.MODIFY_DATE >= TIMESTAMP('"+sTimeStamp+"'))");
        }
        if(sCreatedStart != null && sCreatedStart.length() > 0){
          sCreatedStart = utilObj.formatDateForStatement(sCreatedStart);
          sTimeStamp = sCreatedStart.substring(1,(sCreatedStart.length()-1)) + " 00:00:00.0";
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.CREATE_DATE >= TIMESTAMP('"+sTimeStamp+"') ");
//          sWhereClause.append(" (C.CREATE_DATE >= TIMESTAMP('"+sTimeStamp+"')");
//          sWhereClause.append("  OR CN.CREATE_DATE >= TIMESTAMP('"+sTimeStamp+"'))");
        }
        if(sModifiedBy != null && sModifiedBy.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.MODIFY_USER = '"+sModifiedBy.toLowerCase()+"' ");
//          sWhereClause.append(" (C.MODIFY_USER = '"+sModifiedBy.toLowerCase()+"'");
//          sWhereClause.append("  OR CN.MODIFY_USER = '"+sModifiedBy.toLowerCase()+"')");
        }
        if(sCreatedBy != null && sCreatedBy.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" C.CREATE_USER = '"+sCreatedBy.toLowerCase()+"' ");
//          sWhereClause.append(" (C.CREATE_USER = '"+sCreatedBy.toLowerCase()+"'");
//          sWhereClause.append("  OR CN.CREATE_USER = '"+sCreatedBy.toLowerCase()+"')");
        }
        
        if(sSummary != null && sSummary.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" UPPER(C.SUMMARY) like '%" + sSummary.toUpperCase() + "%' ");
//          sWhereClause.append(" (UPPER(C.SUMMARY) like '%" + sSummary.toUpperCase() + "%'");
//          sWhereClause.append("  OR UPPER(CN.SUMMARY) like '%" + sSummary.toUpperCase() + "%')");
        }
        
        // query to complex to include ... need to find workaround
        if(sNote != null && sNote.length() > 0){
          blnIsLimitedSearch = true;
          sWhereClause = addSyntax(sWhereClause);
          sWhereClause.append(" UPPER(C.NOTE) like '%" + sNote.toUpperCase() + "%' ");
//          sWhereClause.append(" (UPPER(C.NOTE) like '%" + sNote.toUpperCase() + "%'");
//          sWhereClause.append("  OR UPPER(CN.NOTE) like '%" + sNote.toUpperCase() + "%')");
        }
 
        sWhereClause.append(" ORDER BY C.CASE_ID DESC, COM_NAME ASC, DIV_NAME ASC, FNAME ASC, LNAME ASC ");
        sSQL = sWhereClause.toString();
 
//        System.out.println(sSQL);
 
        if(blnIsLimitedSearch){
          if (stmt==null) stmt = m_conDb.createStatement();
          stmt.execute(sSQL);
          rs = stmt.getResultSet();
          
          int iCounter = 0; //counts number of records
          while(rs.next() & blnIsLimitedSearch){
            CaseListBean clbObj = getListBean(rs,false);
            clbObj.setComName(utilObj.nullToEmpty(rs.getString("COM_NAME")));
            clbObj.setDivName(utilObj.nullToEmpty(rs.getString("DIV_NAME")));
            clbObj.setFName(utilObj.nullToEmpty(rs.getString("FNAME")));
            clbObj.setLName(utilObj.nullToEmpty(rs.getString("LNAME")));
            alCaseList.add(clbObj);
          }
        }
      }
    }
    catch(Exception _e){
      System.out.println(sSQL);
      _e.printStackTrace();
    }
    
    return alCaseList;
  }
 
  private ArrayList caseMySearch(HttpServletRequest req){
    ArrayList alCaseList = new ArrayList();
    
    try{
      StringBuffer sSQL = new StringBuffer();
      sSQL.append("SELECT C.*,COMPANY.COM_NAME,DIVISION.DIV_NAME, ");
      sSQL.append("PERSON.FNAME,PERSON.LNAME ");
      sSQL.append("FROM CASE C,COMPANY,DIVISION,PERSON ");
      sSQL.append("WHERE C.COMPANY_ID = COMPANY.COMPANY_ID ");
      sSQL.append("AND C.DIVISION_ID = DIVISION.DIVISION_ID ");
      sSQL.append("AND C.PERSON_ID = PERSON.PERSON_ID ");
      sSQL.append("AND C.CASE_ID!=0 AND C.ACTIVE = 'Y' ");
      sSQL.append("AND C.STATUS != 'CLOSED' AND C.ASSIGN_TO = '" + msUsername + "' ");
      sSQL.append("ORDER BY C.CASE_ID DESC");
      
      if(m_conDb==null) System.out.println("ERROR: NO CONNECTION");
        
      if(stmt==null){stmt = m_conDb.createStatement();}
      stmt.execute(sSQL.toString());
      rs = stmt.getResultSet();
          
      while(rs.next()){
        CaseListBean clbObj = getListBean(rs,false);
        clbObj.setComName(utilObj.nullToEmpty(rs.getString("COM_NAME")));
        clbObj.setDivName(utilObj.nullToEmpty(rs.getString("DIV_NAME")));
        clbObj.setFName(utilObj.nullToEmpty(rs.getString("FNAME")));
        clbObj.setLName(utilObj.nullToEmpty(rs.getString("LNAME")));
        alCaseList.add(clbObj);
      }
    }
    catch(Exception _e){_e.printStackTrace();}
    
    return alCaseList;
  }//caseMySearch()
  
  private void caseUpdate(HttpServletRequest req){
    ContextBean cBean = (ContextBean)session.getAttribute("context");
    String sCompanyId = cBean.getCompanyId();
    String sDivisionId = cBean.getDivisionId();
    String sPersonId = cBean.getPersonId();
    String sCaseId = cBean.getCaseId();
    java.sql.Date dDate = null;
    String sPPass = null;
    String sPEmail = null;
    boolean bValidDate = false;
    
    try{
      Utils utilObj = new Utils();
      java.sql.Timestamp tsp = utilObj.currentTimestamp();
      
      if(sCaseId == null || sCaseId.length() <= 0 || sCaseId.equals("0"))
        sCaseId = (String)req.getParameter("caseid");
      
      String sStatusOriginal = req.getParameter("status_original");
      String sAssignToOriginal = req.getParameter("assigned_original");
      String sAssignto = req.getParameter("assignto");
      String sActionReq = req.getParameter("actionrequired");
      String sPriority = req.getParameter("priority");
      String sOpenDate = req.getParameter("opendate");
      String sCloseDate = req.getParameter("closedate");
      String sStatus = req.getParameter("status");
      String sSummary = req.getParameter("summary");
      String sNote = req.getParameter("note");
      String sPartNo = req.getParameter("partno");
      String sOS = req.getParameter("opsys");
      String sDriverVer = req.getParameter("driverversion");
      String sCaseType = req.getParameter("casetype");
      
      StringBuffer sSQL = new StringBuffer();
      sSQL.append("UPDATE CASE SET ");
      sSQL.append("COMPANY_ID=?,DIVISION_ID=?,PERSON_ID=?,ASSIGN_TO=?,ACTION_REQ=?,");
      sSQL.append("PRIORITY=?,STATUS=?,PRODUCT=?,OS=?,OSDRIVERVER=?,OPEN_DATE=?,CLOSE_DATE=?,");
      sSQL.append("SUMMARY=?,NOTE=?,MODIFY_DATE=?,MODIFY_USER=?,SYNC_DATE=?,SYNC_USER=?,ACTIVE=?, ");
      sSQL.append("CASE_TYPE=? WHERE CASE_ID=?");
      
      pstmt = m_conDb.prepareStatement(sSQL.toString());
      pstmt.setInt(1,Integer.parseInt(sCompanyId));
      
      if(sDivisionId==null || sDivisionId.length()==0){pstmt.setInt(2, 0);} 
      else{pstmt.setInt(2,Integer.parseInt(sDivisionId));}
      
      if(sPersonId==null || sPersonId.length()==0){pstmt.setInt(3, 0);}
      else{pstmt.setInt(3,Integer.parseInt(sPersonId));}
      
      pstmt.setString(4,sAssignto);
      pstmt.setString(5,sActionReq);
      pstmt.setString(6,sPriority);
      pstmt.setString(7,sStatus);
      pstmt.setString(8,sPartNo);
      pstmt.setString(9,sOS);
      pstmt.setString(10,sDriverVer);
      pstmt.setString(11,sOpenDate);
      pstmt.setString(12,sCloseDate);
      pstmt.setString(13,sSummary);
      pstmt.setString(14,sNote);
      pstmt.setTimestamp(15,tsp);
      pstmt.setString(16,msUsername);
      pstmt.setTimestamp(17,tsp);
      pstmt.setString(18,msUsername);
      pstmt.setString(19,"Y");
      pstmt.setString(20,sCaseType);
      pstmt.setInt(21,Integer.parseInt(sCaseId));
      pstmt.execute();
      
      if(!sAssignToOriginal.equalsIgnoreCase(sAssignto)){
        notifyAcctOwner("Indeo Alert: Customer Case Assignment",
                        "Case #: " + sCaseId + " is now assigned to you.",sAssignto);
      }
      
      if(sStatusOriginal != null && sStatusOriginal.length() > 0){
        if(sStatus != null && (sStatus.equalsIgnoreCase("CLOSED") & sStatusOriginal.equals("OPEN"))){
          stmt = m_conDb.createStatement();
        
          rs = stmt.executeQuery("SELECT C.CREATE_DATE, P.EMAIL, P.PASSWORD FROM CASE C INNER JOIN " + 
                                 "PERSON P ON P.PERSON_ID = C.PERSON_ID WHERE C.CASE_ID = " + 
                                 sCaseId + " AND C.PERSON_ID = " + sPersonId);
          if(rs.next()){
            dDate = utilObj.getDateFromTS(rs.getTimestamp("CREATE_DATE"));
            sPPass = utilObj.nullToEmpty(rs.getString("PASSWORD"));
            sPEmail = utilObj.nullToEmpty(rs.getString("EMAIL"));
            sPEmail = sPEmail.trim();
            sPPass = sPPass.trim();
          }
          
          if(dDate != null && (dDate.compareTo(java.sql.Date.valueOf("2005-07-01")) >= 0)){
            bValidDate = true;
          }
          
          if(bValidDate && sPPass != null && sPPass.length() >= 5 && sPEmail != null && sPEmail.length() > 0){
            //System.out.println("Case status closed test for notifyCustomer() execute passed with values: " + sStatus + " and " + sStatusOriginal);
            notifyCustomer("Case [#" + sCaseId + "]: Closed",
                           "Your technical support case #" + sCaseId + " is now closed.\n\n" +
                           "The case can be reopened at your convenience by visiting the link below. " + 
                           "Should you decide to reopen the case please state the reason for doing so.\n\n" +
                           "Thank You\n\n" +
                           mOnlineCaseURL + sCaseId,sPersonId, msUserEmail);  
          }
        }
      }
    }
    catch(Exception _e){
      _e.printStackTrace();
    }
  }
  
  private ArrayList personCompanyList(HttpServletRequest req){
    ArrayList alPerson = new ArrayList();
    ArrayList alCaseList = caseEdit(req, false);
    Iterator i = alCaseList.iterator();
    CaseListBean clb = new CaseListBean();
    String sPersonCompanyId = "";
    
    if (i.hasNext()){
      clb = (CaseListBean)i.next();
      sPersonCompanyId = clb.getCompanyId();
      String sSQL = "SELECT * FROM PERSON WHERE COMPANY_ID=" + sPersonCompanyId + " AND ACTIVE = 'Y' ORDER BY FNAME, LNAME";
      try{
        Statement stmtPeople = m_conDb.createStatement();
        ResultSet _rs = stmtPeople.executeQuery(sSQL);
        
        while (_rs.next()){
          PersonListBean plbObj = new PersonListBean();
          String sPersonId = _rs.getString("PERSON_ID");
          plbObj.setPersonId(sPersonId);
          String sCompanyId = _rs.getString("COMPANY_ID");
          plbObj.setCompanyId(sCompanyId);
          String sDivisionId = _rs.getString("DIVISION_ID");
          plbObj.setDivisionId(sDivisionId);
    
          String sFname = utilObj.nullToEmpty(_rs.getString("FNAME"));
          plbObj.setFName(sFname);
    
          String sLname = utilObj.nullToEmpty(_rs.getString("LNAME"));
          plbObj.setLName(sLname);
          plbObj.setTitle(utilObj.nullToEmpty(_rs.getString("TITLE")));
          plbObj.setAddress1(utilObj.nullToEmpty(_rs.getString("ADDRESS1")));
          plbObj.setAddress2(utilObj.nullToEmpty(_rs.getString("ADDRESS2")));
          plbObj.setCity(utilObj.nullToEmpty(_rs.getString("CITY")));
          plbObj.setState(utilObj.nullToEmpty(_rs.getString("STATE")));
          plbObj.setCountry(utilObj.nullToEmpty(_rs.getString("COUNTRY")));
          plbObj.setZip(utilObj.nullToEmpty(_rs.getString("ZIP")));
          plbObj.setPhone(utilObj.nullToEmpty(_rs.getString("PHONE")));
          plbObj.setExt(utilObj.nullToEmpty(_rs.getString("EXT")));
          plbObj.setCellPhone(utilObj.nullToEmpty(_rs.getString("CELLPHONE")));
          plbObj.setPager(utilObj.nullToEmpty(_rs.getString("PAGER")));
          plbObj.setFax(utilObj.nullToEmpty(_rs.getString("FAX")));
          plbObj.setEmail(utilObj.nullToEmpty(_rs.getString("EMAIL")));
          plbObj.setNote(utilObj.nullToEmpty(_rs.getString("NOTE")));
          plbObj.setUserName("");
          //plbObj.setUserName(utilObj.nullToEmpty(_rs.getString("USERNAME")));
          plbObj.setPassword(utilObj.nullToEmpty(_rs.getString("PASSWORD")));
          plbObj.setCreateDate(utilObj.formatDateTime(_rs.getTimestamp("CREATE_DATE")));
          plbObj.setCreateUser(utilObj.nullToEmpty(_rs.getString("CREATE_USER")));
          plbObj.setModifyDate(utilObj.formatDateTime(_rs.getTimestamp("MODIFY_DATE")));
          plbObj.setModifyUser(utilObj.nullToEmpty(_rs.getString("MODIFY_USER")));
          plbObj.setFaxList(_rs.getString("FAX_LIST"));
          plbObj.setCTitle(_rs.getString("PREFIX")); //Mr., Ms, etc.
          String sComname = "";
          plbObj.setComName(sComname);
          String sDivname = "";
          plbObj.setDivName(sDivname);
          alPerson.add(plbObj);
        }
      }
      catch(Exception e){
        e.printStackTrace();
      }
    }
    
    return alPerson;
    
  }
  
  private void changeContact(HttpServletRequest req){
    String sCaseId = req.getParameter("caseid");
    String sPersonId = req.getParameter("newperson");
    String sSQL = "UPDATE CASE SET PERSON_ID = " + sPersonId + " WHERE CASE_ID = " + sCaseId;
    try{
      Statement stmtContact = m_conDb.createStatement();
      stmtContact.execute(sSQL);
    }
    catch(Exception e){
      e.printStackTrace();
    }
 
  }
  
  //INDEO-1357, change email sender to Support Autoresponder (support@comtrol.com)
  private void notifyCustomer(String sSubject, String sMsg, String sPersonId, String sTechEmail){
    try{
      EmailAction ea = new EmailAction();
      String sPersonEmail = ea.getEmailFromPersonId(sPersonId,this.m_conDb);
      
      try{
        if(sPersonEmail != null && sPersonEmail.length() > 0){
          boolean bOK = ea.processMailItem("support@comtrol.com", sPersonEmail, sSubject, sMsg, mRequest);
          if(bOK){System.out.println("Case Closed Notification sent to " + sPersonEmail);}
          else{System.out.println("Case Closed Notification to " + sPersonEmail + " failed.");}
        }
      }catch(Exception e){e.printStackTrace();}
 
      ea = null;
    }catch(Exception e){e.printStackTrace();}
  }
  
  private void notifyAcctOwner(String sSubject, String sMsg, String sOwner){
    try{
      SUserAction sua = new SUserAction();
      SUserListBean sulb = sua.suserLookup(sOwner, m_conDb);
      String sOwnerEmail = sulb.getEmail();
      EmailAction ea = new EmailAction();
      try{
        boolean bOK = ea.processMailItem(msUserEmail, sOwnerEmail, sSubject, sMsg, mRequest);
        if(bOK){System.out.println("Case Assignment Notification sent to " + sOwnerEmail);}
        else{System.out.println("Case Assignment Notification to " + sOwnerEmail + " failed.");}
      }catch(Exception e){e.printStackTrace();}
 
      sulb = null;
      ea = null;
    }catch(Exception e){e.printStackTrace();}
  }
  
  private CaseListBean getListBean(ResultSet _rs, boolean bSetSession) throws Exception{
    
    CaseListBean clbObj = new CaseListBean();
    
    clbObj.setCaseUID(_rs.getString("CASE_UID"));
    
    clbObj.setCaseId(Integer.toString(_rs.getInt("CASE_ID")));
 
    String sCompanyId = Integer.toString(_rs.getInt("COMPANY_ID"));
    clbObj.setCompanyId(sCompanyId);
    
    String sDivisionId = Integer.toString(_rs.getInt("DIVISION_ID"));
    clbObj.setDivisionId(sDivisionId);
    
    String sPersonId = Integer.toString(_rs.getInt("PERSON_ID"));
    clbObj.setPersonId(sPersonId);
    
    clbObj.setAssignTo(utilObj.nullToEmpty(_rs.getString("ASSIGN_TO")));
    clbObj.setActionReq(utilObj.nullToEmpty(_rs.getString("ACTION_REQ")));
    clbObj.setPriority(utilObj.nullToEmpty(_rs.getString("PRIORITY")));
    clbObj.setStatus(utilObj.nullToEmpty(_rs.getString("STATUS")));
    clbObj.setProduct(utilObj.nullToEmpty(_rs.getString("PRODUCT")));
    clbObj.setOS(utilObj.nullToEmpty(_rs.getString("OS")));
    clbObj.setOSDriverVer(utilObj.nullToEmpty(_rs.getString("OSDRIVERVER")));
    clbObj.setOpenDate(utilObj.nullToEmpty(_rs.getString("OPEN_DATE")));
    clbObj.setCloseDate(utilObj.nullToEmpty(_rs.getString("CLOSE_DATE")));
    clbObj.setSummary(utilObj.nullToEmpty(_rs.getString("SUMMARY")));
    clbObj.setNote(utilObj.nullToEmpty(_rs.getString("NOTE")));
    clbObj.setCreateDate(utilObj.formatDateTime(_rs.getTimestamp("CREATE_DATE")));
    clbObj.setCreateUser(utilObj.nullToEmpty(_rs.getString("CREATE_USER")));
    clbObj.setModifyDate(utilObj.formatDateTime(_rs.getTimestamp("MODIFY_DATE")));
    clbObj.setModifyUser(utilObj.nullToEmpty(_rs.getString("MODIFY_USER")));
    clbObj.setCaseType(utilObj.nullToEmpty(_rs.getString("CASE_TYPE")));
    
    if (bSetSession){
      String sComname = utilObj.nullToEmpty(_rs.getString("COM_NAME"));
      clbObj.setComName(sComname);
      
      String sDivname = utilObj.nullToEmpty(_rs.getString("DIV_NAME"));
      clbObj.setDivName(sDivname);
      
      String sFname = utilObj.nullToEmpty(_rs.getString("FNAME"));
      clbObj.setFName(sFname);
      
      String sLname = utilObj.nullToEmpty(_rs.getString("LNAME"));
      clbObj.setLName(sLname);
      
      //setting up context
      ContextBean cBean = new ContextBean();
      cBean.setPersonId(sPersonId);
      cBean.setFName(sFname);
      cBean.setLName(sLname);
      cBean.setCompanyId(sCompanyId);
      cBean.setComName(sComname);
      cBean.setDivisionId(sDivisionId);
      cBean.setDivName(sDivname);
      cBean.setOpportunityId("0");
      cBean.setQuoteId("0");
      cBean.setOrderId("0");
      cBean.setCaseId(clbObj.getCaseId());
      cBean.setRmaId("0");
      session.setAttribute("context",cBean);
    }
    
    return clbObj;
  }
  
  public ActionRouter perform(HttpServlet servlet, HttpServletRequest req, HttpServletResponse res) throws java.io.IOException, ServletException{
    String sNextJsp = "/CaseList.jsp";
    session=req.getSession();
    UserBean ubObject = (UserBean)session.getAttribute("userbean"); //retrieving security user bean
    msUsername = ubObject.getUserId();
    msUserEmail = ubObject.getEMailAddress();
    Hashtable htSecurity = ubObject.getSecurity();
    mbLimit = ((SecurityBean)htSecurity.get("CASE")).getLimit();
    mRequest = req;
    m_response = res; //for search cookie management
    
    try{
      getConnection(servlet);
      String sCmd = (String) req.getParameter("cmd");
      
      if(sCmd.equals("case_list") || sCmd.equals("case_contextlist")){
        boolean bOverride = false;
        if(sCmd.equals("case_contextlist")){bOverride = true;}
        req.setAttribute("listing",caseSearch(req,bOverride));
        sNextJsp ="/CaseList.jsp";
      }else if(sCmd.equals("case_edit") || sCmd.equals("case_contextedit")){
        populateErrorFields(req);
        boolean bOverride = false;
        if(sCmd.equals("case_contextedit")){bOverride=true;}
        req.setAttribute("listing",caseEdit(req,bOverride));
        req.setAttribute("noteslisting",getCaseNotesList(req));
        req.setAttribute("action","update");
        sNextJsp ="/Case.jsp";
      }else if(sCmd.equals("case_save")){
        if(isValid(req)){
          caseUpdate(req);
          req.setAttribute("listing",caseEdit(req,false));
          req.setAttribute("noteslisting",getCaseNotesList(req));
        }
        sNextJsp ="/Case.jsp";
      }else if(sCmd.equals("case_insert")){
        populateErrorFields(req);
        req.setAttribute("listing",caseInsert(req));
        sNextJsp ="/Case.jsp";
      }else if(sCmd.equals("case_insertsave")){
        if(isValid(req)){
          caseInsertSave(req);
          req.setAttribute("listing",caseEdit(req,true));
          req.setAttribute("cmd","case_edit");
        }else{
          req.setAttribute("cmd","case_insert");
        }
        sNextJsp ="/Case.jsp";
      }else if (sCmd.equals("case_delete")){
        caseDelete(req);
        req.setAttribute("listing",caseSearch(req,true));
        sNextJsp ="/CaseList.jsp";
      }else if(sCmd.equals("case_search")){
        sNextJsp ="/CaseSearch.jsp";
      }else if(sCmd.equals("case_mylist")){
        boolean bOverride = false;
        req.setAttribute("listing",caseMySearch(req));
        sNextJsp ="/CaseList.jsp";
      }else if(sCmd.equals("case_move")){
        //Need current case and list of all people at company
        req.setAttribute("listing", caseEdit(req, false));
        req.setAttribute("people", personCompanyList(req));
        sNextJsp="/CaseMove.jsp";
      }else if(sCmd.equals("case_changecontact")){
        changeContact(req);
        req.setAttribute("listing",caseEdit(req,true));
        req.setAttribute("noteslisting",getCaseNotesList(req));
        req.setAttribute("action","update");
        sNextJsp ="/Case.jsp";
      }
    }
    catch(Exception _e){
      _e.printStackTrace();
      sNextJsp="/CaseList.jsp";
    }
    finally {
      try{close();}
      catch (Exception _e) {
        _e.printStackTrace();
        sNextJsp="/CaseList.jsp";
      }
    }
    return new ActionRouter(sNextJsp);
  }//end perform()
  
  private boolean isValid(HttpServletRequest req){
    
    boolean isValid=true;
    /*
    if (!validate(req,"detail_category","detailcat"))isValid=false;
    if (!validate(req,"detail_data","detaildata"))isValid=false;
    
    if (!isValid){
      DetailListBean dlbObj = new DetailListBean();
      dlbObj.setDetailId(req.getParameter("detailid"));
      dlbObj.setCategory(req.getParameter("detailcat"));
      
      ArrayList alDetailList = new ArrayList();
      alDetailList.add(dlbObj);
      req.setAttribute("listing",alDetailList);
    }
     */
    return isValid;
  }
  
  private void populateErrorFields(HttpServletRequest req){
    /*
    req.setAttribute("error","&nbsp;");   //error message
    req.setAttribute("detaildata_error","");
    req.setAttribute("detailcat_error","");
    req.setAttribute("note_error","");
     */
  }//end populateErrorFields()
}
[+][-]07.22.2008 at 09:00AM PDT, ID: 22060792

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.22.2008 at 09:25AM PDT, ID: 22061033

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.22.2008 at 11:35AM PDT, ID: 22062235

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.22.2008 at 12:04PM PDT, ID: 22062536

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Java Programming Language
Sign Up Now!
Solution Provided By: CEHJ
Participating Experts: 2
Solution Grade: A
 
 
[+][-]07.23.2008 at 05:31AM PDT, ID: 22068427

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628