Examples of com.centraview.common.CVDal

com.centraview.common.CVDal
This class wraps the full database interaction. Uses prepared Statements for executing Queries and Datasource to get connection from a connection pool eg. CVDal dl = new CCVDal(); dl.setsql("contact.getentity"); dl.setString(1,Iqbal); dl.setInt(2,30); HashMap hm = dl.executeQuery(); dl.destroy(); NOTE: STATIC SQL Queries are NO longer stored in this file, they should be set in the static initializer in com.centraview.common.QueryCollection DynamicQueries are still built in this file.

    ArrayList list = new ArrayList();


    boolean applyFilter = false;
    String filter = parameters.getFilter();


    CVDal cvdl = new CVDal(this.dataSource);
    if (filter != null && filter.length() > 0) {
      String str = "CREATE TABLE tasklistfilter " + filter;
      cvdl.setSqlQuery(str);
      cvdl.executeUpdate();
      cvdl.setSqlQueryToNull();
      applyFilter = true;
    }


    String query = this.buildTaskListQuery(applyFilter, individualId, cvdl, parameters);
    cvdl.setSqlQuery(query);
    list = cvdl.executeQueryList(1);
    cvdl.setSqlQueryToNull();
    cvdl.setSqlQuery("DROP TABLE alltasklist");
    cvdl.executeUpdate();


    if (applyFilter) {
      cvdl.setSqlQueryToNull();
      cvdl.setSqlQuery("DROP TABLE tasklistfilter");
      cvdl.executeUpdate();
    }


    cvdl.destroy();
    cvdl = null;
    return new ValueListVO(list, parameters);
  }

View Full Code Here

    tvo.setTaskid("" + taskId);
    tvo.setActivityID(taskId);


    try
    {
      CVDal dl = new CVDal(dataSource);


      InitialContext ic = CVUtility.getInitialContext();
      ActivityHelperLocalHome home = (ActivityHelperLocalHome)ic.lookup("local/ActivityHelper");
      ActivityHelperLocal remote = (ActivityHelperLocal) home.create();
      remote.setDataSource(dataSource);
      ActivityVO actVo = new ActivityVO();
      actVo = remote.getActivity(taskId,userId);


      tvo.setTitle(actVo.getTitle());
      tvo.setActivityDetails(actVo.getActivityDetails());
      tvo.setCreatedBy(actVo.getCreatedBy());
      tvo.setCreatedOn(actVo.getCreatedOn());
      tvo.setModifiedOn(actVo.getModifiedOn());
      tvo.setOwner(tvo.getOwner());
      tvo.setModifiedBy(actVo.getModifiedBy());
      tvo.setStatus(actVo.getStatus());
      tvo.setStart(actVo.getActivityStartDate());
      tvo.setEnd(actVo.getActivityDueDate());
      tvo.fillAuditDetails(this.dataSource);


      if (actVo.getIndividualID() != 0)
      {
        tvo.setIndividualID(actVo.getIndividualID());
        tvo.setIndividualName(actVo.getIndividualName());
      }


      dl.setSql("projecttask.gettask");
      dl.setInt(1, taskId);
      Collection col = dl.executeQuery();
      Iterator ite = col.iterator();
      if (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        if (hm.get("projectid") != null)
        {
          tvo.setProjectID(((Long)hm.get("projectid")).intValue());
        }


        if (hm.get("parent") != null)
        {
          tvo.setParentID(((Long)hm.get("parent")).intValue());
        }


        if (hm.get("milestone") != null)
        {
          tvo.setIsMileStone((String)hm.get("milestone"));
        }


        if (hm.get("percentcomplete") != null)
        {
          tvo.setPercentComplete(((Long)hm.get("percentcomplete")).intValue());
        }


        if (hm.get("projecttitle") != null)
        {
          tvo.setProjectName((String)hm.get("projecttitle"));
        }


        if (hm.get("projecttaskcount") != null)
        {
          tvo.setProjectTaskCount(((Integer)hm.get("projecttaskcount")).intValue());
        }
      }
      dl.clearParameters();


      dl.setSql("projecttask.gettaskassigned");
      dl.setInt(1, taskId);
      col = dl.executeQuery();
      ite = col.iterator();
      while (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        if (hm.get("individualid") != null)
        {
          tvo.setAssignedTo(((Long)hm.get("individualid")).intValue(), (String)hm.get("CONCAT(firstname, ' ' , lastname)"));
        }
      }
      dl.clearParameters();


      Collection col1 = null;
      Iterator ite1 = null;


      int activityid = -1;
      dl.setSqlQuery("SELECT activityaction.actionid, activityaction.recipient, "+
                "concat(individual.firstname,' ', individual.lastname) IndividualName ,action.type "+
                "FROM individual INNER JOIN activityaction ON (individual.individualid = activityaction.recipient) ,action "+
                "where activityaction.activityid=? and activityaction.actionid = action.ActionID");
      dl.setInt(1, taskId);
      col = dl.executeQuery();
      ite = col.iterator();


      HashMap emaila = new HashMap();
      HashMap alerta = new HashMap();


      boolean email = false;
      boolean alert = false;


      while (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        String type = (String)hm.get("type");


        if (type.equals("ALERT"))
        {
          alert = true;
          alerta.put((Long)hm.get("recipient"), (String)hm.get("IndividualName"));
        }else if (type.equals("EMAIL")){
          email = true;
          emaila.put((Long)hm.get("recipient"), hm.get("IndividualName"));
        }
      }


      tvo.setAlerta(alerta);
      tvo.setEmaila(emaila);


      dl.clearParameters();


      dl.setSql("projecttask.getsubtask");
      dl.setInt(1, taskId);
      tvo.setActivityID(taskId);
      col = dl.executeQuery();


      ite = col.iterator();
      if (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        if (hm.get("ActivityID") != null)
        {
          if (hm.get("parent") != null)
          {
            tvo.setParentID(((Long)hm.get("projectid")).intValue());
          }
        }


        if (hm.get("milestone") != null)
        {
          tvo.setIsMileStone((String)hm.get("milestone"));
        }
      }


      tvo.setSubTasks(col);
      dl.clearParameters();


      dl.setSql("projecttask.getparenttaskname");
      dl.setInt(1, tvo.getParentID());
      col = dl.executeQuery();
      ite = col.iterator();
      if (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        if (hm.get("title") != null)
        {
          tvo.setParentTask((String)hm.get("title"));
        }
      }
      dl.clearParameters();


      dl.setSql("projecttask.gettaskstatus");
      col = dl.executeQuery();
      ite = col.iterator();
      while (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        if (hm.get("statusid") != null)
        {
          tvo.setStat(((Integer)hm.get("statusid")).intValue(), (String)hm.get("name"));
        }
      }
      dl.clearParameters();


      int parentId = tvo.getParentID();


      if (parentId != 0)
      {
        LinkedHashMap lhm = new LinkedHashMap();


        boolean flag = true;
        while (flag)
        {
          dl.setSql("projecttask.gettaskparent");
          dl.setInt(1, parentId);
          col = dl.executeQuery();
          ite = col.iterator();
          if (ite.hasNext())
          {
            HashMap hm = (HashMap)ite.next();
            lhm.put("" + parentId, hm.get("title"));


            if (hm.get("parent") != null)
            {
              parentId = ((Long)hm.get("parent")).intValue();
              if (parentId == 0)
              {
                flag = false;
              }
            }else{
              flag = false;
            }
          }
        }


        tvo.setCrumbs(lhm);
      }
      dl.clearParameters();
      dl.destroy();
    }catch (Exception e){
      System.out.println("[Exception][TaskEJB.getTask] Exception Thrown: " + e);
      e.printStackTrace();
    }
    return tvo;

View Full Code Here

    if (!CVUtility.canPerformRecordOperation(userId, "Projects", projectId, ModuleFieldRightMatrix.VIEW_RIGHT, this.dataSource))
    {
      throw new AuthorizationFailedException("Projects - getProject");
    }


    CVDal cvdl = new CVDal(dataSource);
    cvdl.setSql("project.getproject");
    cvdl.setInt(1, projectId);
    Collection col = cvdl.executeQuery();
    Iterator ite = col.iterator();
    ProjectDBVO pdbvo = new ProjectDBVO();
    if (ite.hasNext())
    {
      HashMap project = (HashMap)ite.next();
      pdbvo.setProjectID(((Long)project.get("ProjectID")).intValue());
      pdbvo.setTitle((String)project.get("ProjectTitle"));
      if (project.get("Description") != null)
        pdbvo.setDescription((String)project.get("Description"));
      if (project.get("StatusID") != null)
        pdbvo.setStatusID(((Long)project.get("StatusID")).intValue());
      if (project.get("Start") != null)
        pdbvo.setStart((java.sql.Date)project.get("Start"));
      if (project.get("End") != null)
        pdbvo.setEnd((java.sql.Date)project.get("End"));
      if (project.get("BudgetedHours") != null)
        pdbvo.setBudgetedHours(((Long)project.get("BudgetedHours")).intValue());
      if (project.get("HoursUsed") != null)
        pdbvo.setUsedHours(((Long)project.get("HoursUsed")).intValue());
      if (project.get("Manager") != null)
        pdbvo.setManagerID(((Long)project.get("Manager")).intValue());
      if (project.get("Owner") != null)
        pdbvo.setOwner(((Long)project.get("Owner")).intValue());
      if (project.get("Creator") != null)
        pdbvo.setCreator(((Long)project.get("Creator")).intValue());
      if (project.get("ModifiedBy") != null)
        pdbvo.setModifiedBy(((Long)project.get("ModifiedBy")).intValue());
      if (project.get("Modified") != null)
        pdbvo.setModified((Timestamp)project.get("Modified"));
      if (project.get("Created") != null)
        pdbvo.setCreated((Timestamp)project.get("Created"));
    }


    cvdl.clearParameters();
    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pdbvo.getCreator());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pdbvo.setCreatorName((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pdbvo.getModifiedBy());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pdbvo.setModifierName((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }
    cvdl.clearParameters();


    cvdl.setSql("project.getprojectentitylink");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pdbvo.setEntityID(((Long)hm.get("entityid")).intValue());
      pdbvo.setEntityName((String)hm.get("name"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getprojectindividuallink");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pdbvo.setContactID(((Long)hm.get("individualid")).intValue());
      pdbvo.setContactName((String)hm.get("CONCAT(firstname , ' ' , lastname)"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getprojectgrouplink");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pdbvo.setGroupID(((Long)hm.get("groupid")).intValue());
      pdbvo.setGroupName((String)hm.get("name"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.projecthoursused");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      if (hm.get("sum(hours)") != null)
      {
        pdbvo.setUsedHours(((Double)hm.get("sum(hours)")).floatValue());
      }
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pdbvo.getOwner());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pdbvo.setOwnerName((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }
    cvdl.clearParameters();


    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pdbvo.getManagerID());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pdbvo.setManager((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }
    cvdl.clearParameters();
    return pdbvo;
  }

View Full Code Here

    // limiting
    // rows based on record rights. If it is true than the rights are used.
    boolean permissionSwitch = individualId < 1 ? false : true;
    boolean applyFilter = false;
    String filter = parameters.getFilter();
    CVDal cvdl = new CVDal(this.dataSource);
    if (filter != null && filter.length() > 0) {
      String str = "CREATE TABLE projectlistfilter " + filter;
      cvdl.setSqlQuery(str);
      cvdl.executeUpdate();
      cvdl.setSqlQueryToNull();
      applyFilter = true;
    }
    int numberOfRecords = 0;
    if (applyFilter) {
      numberOfRecords = EJBUtil.buildListFilterTable(cvdl, "projectlistfilter", individualId, 36,
          "project", "ProjectID", "owner", null, permissionSwitch);
    } else if (permissionSwitch) {
      numberOfRecords = EJBUtil.buildListFilterTable(cvdl, null, individualId, 36, "project",
          "ProjectID", "owner", null, permissionSwitch);
    }
    parameters.setTotalRecords(numberOfRecords);
    String query = this.buildProjectListQuery(applyFilter, permissionSwitch, parameters);
    cvdl.setSqlQuery(query);
    cvdl.executeUpdate();
    cvdl.setSqlQueryToNull();


    cvdl
        .setSqlQuery("UPDATE projectlist, projectlink, entity SET projectlist.EntityID =  projectlink.recordid , projectlist.Entity = entity.name WHERE entity.entityid = projectlink.recordid and projectlink.projectid = projectlist.projectid and projectlink.recordtypeid = 14;");
    cvdl.executeUpdate();
    cvdl.setSqlQueryToNull();


    cvdl.setSqlQuery("SELECT * FROM projectlist");
    list = cvdl.executeQueryList(1);
    cvdl.setSqlQueryToNull();
    cvdl.setSqlQuery("DROP TABLE projectlist");
    cvdl.executeUpdate();
    // throw away the temp filter table, if necessary.
    if (applyFilter) {
      cvdl.setSqlQueryToNull();
      cvdl.setSqlQuery("DROP TABLE projectlistfilter");
      cvdl.executeUpdate();
    }
    if (applyFilter || permissionSwitch) {
      cvdl.setSqlQueryToNull();
      cvdl.setSqlQuery("DROP TABLE listfilter");
      cvdl.executeUpdate();
    }
    cvdl.destroy();
    cvdl = null;
    return new ValueListVO(list, parameters);
  }

View Full Code Here


      TaskDBVO tdbvo = new TaskDBVO();
      tdbvo = getDBVO(tvo.getActivityID());
      tvo = (TaskVO)CVUtility.replaceVO(tdbvo, tvo, "Tasks", userId, this.dataSource);


      CVDal dl = new CVDal(dataSource);


      InitialContext ic = CVUtility.getInitialContext();
      ActivityHelperLocalHome home = (ActivityHelperLocalHome)ic.lookup("local/ActivityHelper");
      ActivityHelperLocal remote = (ActivityHelperLocal) home.create();
      remote.setDataSource(dataSource);


      ActivityVO actVo = new ActivityVO();
      actVo.setActivityID(tvo.getActivityID());
      actVo.setEntityID(0);
      actVo.setIndividualID(tvo.getIndividualID());
      actVo.setActivityType(8);
      actVo.setTitle(tvo.getTitle());
      actVo.setActivityDetails(tvo.getActivityDetails());
      actVo.setActivityStartDate(tvo.getStart());
      actVo.setActivityDueDate(tvo.getEnd());
      actVo.setStatus(tvo.getStatus());


      remote.updateActivity(actVo,userId);


      dl.setSql("projecttask.updatetask");
      dl.setInt(1, tvo.getProjectID());
      dl.setInt(2, tvo.getParentID());
      dl.setString(3, tvo.getIsMileStone());
      dl.setInt(4, tvo.getPercentComplete());
      dl.setInt(5, tvo.getActivityID());
      dl.executeUpdate();
      dl.clearParameters();


      dl.setSql("projecttask.deletetaskassigned");
      dl.setInt(1, tvo.getActivityID());
      dl.executeUpdate();
      dl.clearParameters();


      HashMap assigned = (HashMap)tvo.getAssignedTo();
      Iterator ite = null;
      if (assigned != null)
      {
        ite = assigned.keySet().iterator();
        while (ite.hasNext())
        {
          int assignedTo = ((Integer)ite.next()).intValue();


          dl.setSql("projecttask.addtaskassigned");
          dl.setInt(1, tvo.getActivityID());
          dl.setInt(2, assignedTo);
          dl.executeUpdate();
          dl.clearParameters();
        }
      }


      dl.setSql("projecttask.selecttaskaction");
      dl.setInt(1, tvo.getActivityID());
      Collection countCol = dl.executeQuery();
      dl.clearParameters();
      Iterator itCount = countCol.iterator();
      while (itCount.hasNext())
      {
        HashMap hm = (HashMap)itCount.next();
        if (hm.get("actionid") != null)
        {
          int actionidd = ((Long)hm.get("actionid")).intValue();
          dl.setSql("projecttask.deletetaskalertaction");
          dl.setInt(1, actionidd);
          dl.executeUpdate();
          dl.clearParameters();
          dl.setSql("projecttask.deletetaskalert");
          dl.setInt(1, actionidd);
          dl.executeUpdate();
          dl.clearParameters();
        }
      }


      Vector customField = tvo.getCustomField();
      if (customField != null && customField.size() != 0)
      {
        for (int i = 0; i < customField.size(); i++)
        {
          CustomFieldVO cvo = (CustomFieldVO)customField.get(i);
          if (cvo.getFieldID() != 0)
          {
            dl.setSql("common.updateCustomFieldScalar");
            dl.setString(1, cvo.getValue());
            dl.setInt(2, cvo.getFieldID());
            dl.setInt(3, tvo.getActivityID());
            dl.executeUpdate();
            dl.clearParameters();
          }
        }
      }


      Collection actions = tvo.getActivityAction();
      if (actions != null)
      {
        ite = actions.iterator();
        while (ite.hasNext())
        {
          ActivityActionVO action = (ActivityActionVO)ite.next();
          String message = tvo.getMessage();
          dl.setSql("projecttask.addtaskalertaction");
          dl.setString(1, action.getActionType());
          dl.setString(2, message);
          dl.executeUpdate();


          String individualIDs = "";
          int actionId = dl.getAutoGeneratedKey();
          dl.clearParameters();
          ArrayList recipients = action.getRecipient();
          if (recipients != null)
          {
            Iterator it1 = recipients.iterator();
            while (it1.hasNext())
            {
              int individualID = ((Integer)it1.next()).intValue();
              dl.setSql("projecttask.addtaskalert");
              dl.setInt(1, tvo.getActivityID());
              dl.setInt(2, actionId);
              dl.setInt(3, individualID);
              dl.executeUpdate();
              dl.clearParameters();
              individualIDs += individualID + ",";
            }
          }


          if (individualIDs != null && individualIDs.endsWith(","))
          {
            int individualIDsLen = individualIDs.length();
            individualIDs = individualIDs.substring(0,(individualIDsLen-1));
          }


          if (tvo.getStatus() == 2)
          {
            if (action.getActionType().equals(ActivityActionVO.AA_EMAIL))
            {
                this.sendAlertEmail(userId,individualIDs,message);
            }
          }
        }   // end while (ite.hasNext())
      }   // end if (actions != null)
      dl.destroy();
    }catch (Exception e){
      System.out.println("[Exception][TaskEJB.updateTask] Exception Thrown: " + e);
      e.printStackTrace();
    }
  }   // end updateTask() method

View Full Code Here

    // limiting
    // rows based on record rights. If it is true than the rights are used.
    boolean permissionSwitch = individualId < 1 ? false : true;
    boolean applyFilter = false;
    String filter = parameters.getFilter();
    CVDal cvdl = new CVDal(this.dataSource);
    if (filter != null && filter.length() > 0) {
      String str = "CREATE TABLE timeslipslistfilter " + filter;
      cvdl.setSqlQuery(str);
      cvdl.executeUpdate();
      cvdl.setSqlQueryToNull();
      applyFilter = true;
    }
    int numberOfRecords = 0;
    if (applyFilter) {
      numberOfRecords = EJBUtil.buildListFilterTable(cvdl, "timeslipslistfilter", individualId, 38,
          "timeslip", "TimeSlipID", "CreatedBy", null, permissionSwitch);
    } else if (permissionSwitch) {
      numberOfRecords = EJBUtil.buildListFilterTable(cvdl, null, individualId, 38, "timeslip",
          "TimeSlipID", "CreatedBy", null, permissionSwitch);
    }
    parameters.setTotalRecords(numberOfRecords);
    String query = this.buildTimeslipsListQuery(applyFilter, permissionSwitch, parameters);
    cvdl.setSqlQuery(query);
    cvdl.executeUpdate();
    cvdl.setSqlQueryToNull();


    cvdl.setSqlQuery("SELECT * FROM timeslipslist");
    list = cvdl.executeQueryList(1);
    cvdl.setSqlQueryToNull();
    cvdl.setSqlQuery("DROP TABLE timeslipslist");
    cvdl.executeUpdate();
    // throw away the temp filter table, if necessary.
    if (applyFilter) {
      cvdl.setSqlQueryToNull();
      cvdl.setSqlQuery("DROP TABLE timeslipslistfilter");
      cvdl.executeUpdate();
    }
    if (applyFilter || permissionSwitch) {
      cvdl.setSqlQueryToNull();
      cvdl.setSqlQuery("DROP TABLE listfilter");
      cvdl.executeUpdate();
    }
    cvdl.destroy();
    cvdl = null;
    return new ValueListVO(list, parameters);
  }

View Full Code Here

    if (!CVUtility.canPerformRecordOperation(userId, "Projects", projectId, ModuleFieldRightMatrix.VIEW_RIGHT, this.dataSource))
    {
      throw new AuthorizationFailedException("Projects - getProject");
    }


    CVDal cvdl = new CVDal(dataSource);
    cvdl.setSql("project.getproject");
    cvdl.setInt(1, projectId);
    Collection col = cvdl.executeQuery();
    Iterator ite = col.iterator();
    ProjectVO pvo = new ProjectVO();
    if (ite.hasNext())
    {
      HashMap project = (HashMap)ite.next();
      pvo.setProjectID(((Long)project.get("ProjectID")).intValue());
      pvo.setTitle((String)project.get("ProjectTitle"));
      if (project.get("Description") != null)
        pvo.setDescription((String)project.get("Description"));
      if (project.get("StatusID") != null)
        pvo.setStatusID(((Long)project.get("StatusID")).intValue());
      if (project.get("Start") != null)
        pvo.setStart((java.sql.Date)project.get("Start"));
      if (project.get("End") != null)
        pvo.setEnd((java.sql.Date)project.get("End"));
      if (project.get("BudgetedHours") != null)
        pvo.setBudgetedHours(((Long)project.get("BudgetedHours")).intValue());
      if (project.get("HoursUsed") != null)
        pvo.setUsedHours(((Long)project.get("HoursUsed")).intValue());
      if (project.get("Owner") != null)
        pvo.setOwner(((Long)project.get("Owner")).intValue());
      if (project.get("Manager") != null)
        pvo.setManagerID(((Long)project.get("Manager")).intValue());
      if (project.get("Creator") != null)
        pvo.setCreator(((Long)project.get("Creator")).intValue());
      if (project.get("ModifiedBy") != null)
        pvo.setModifiedBy(((Long)project.get("ModifiedBy")).intValue());
      if (project.get("Modified") != null)
        pvo.setModified((Timestamp)project.get("Modified"));
      if (project.get("Created") != null)
        pvo.setCreated((Timestamp)project.get("Created"));
    }


    cvdl.clearParameters();
    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pvo.getCreator());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pvo.setCreatorName((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pvo.getModifiedBy());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pvo.setModifierName((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }
    cvdl.clearParameters();


    cvdl.setSql("project.getprojectentitylink");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pvo.setEntityID(((Long)hm.get("entityid")).intValue());
      pvo.setEntityName((String)hm.get("name"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getprojectindividuallink");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pvo.setContactID(((Long)hm.get("individualid")).intValue());
      pvo.setContactName((String)hm.get("CONCAT(firstname , ' ' , lastname)"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getprojectgrouplink");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pvo.setGroupID(((Long)hm.get("groupid")).intValue());
      pvo.setGroupName((String)hm.get("name"));
    }


    cvdl.clearParameters();


    cvdl.setSql("project.projecthoursused");
    cvdl.setInt(1, projectId);
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      if (hm.get("sum(hours)") != null)
      {
        pvo.setUsedHours(((Double)hm.get("sum(hours)")).floatValue());
      }
    }


    cvdl.clearParameters();


    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pvo.getOwner());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pvo.setOwnerName((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }
    cvdl.clearParameters();


    cvdl.setSql("project.getindname");
    cvdl.setInt(1, pvo.getManagerID());
    col = cvdl.executeQuery();
    ite = col.iterator();
    if (ite.hasNext())
    {
      HashMap hm = (HashMap)ite.next();
      pvo.setManager((String)hm.get("CONCAT(firstname,' ',lastname)"));
    }
    cvdl.clearParameters();
    return pvo;
  }

View Full Code Here

  public void ejbActivate() { }
  public void ejbPassivate() { }


  public ValueListVO getCustomViewList(int individualId, ValueListParameters parameters)
  {
    CVDal cvdal = new CVDal(this.dataSource);
    try {
      ArrayList list = new ArrayList();
      
      // permissionSwitch turns the permission parts of the query on and off.
      // if individualID is less than zero then the list is requested without limiting
      // rows based on record rights.  If it is true than the rights are used.
      boolean permissionSwitch = false;
      boolean applyFilter = false;
      String filter = parameters.getFilter();
      
      if (filter != null && filter.length() > 0) {
        String str = "CREATE TABLE customviewlistfilter " + filter;
        cvdal.setSqlQuery(str);
        cvdal.executeUpdate();
        cvdal.setSqlQueryToNull();
        applyFilter = true;
      }
      int numberOfRecords = 0;
      if (applyFilter) {
        numberOfRecords = EJBUtil.buildListFilterTable(cvdal, "customviewlistfilter", individualId, 67, "listviews", "viewid", "ownerid", null, permissionSwitch);
      }
      parameters.setTotalRecords(numberOfRecords);




      StringBuffer query = new StringBuffer("");
      query.append("SELECT lv.viewid AS viewid, lv.viewname AS viewname, m2.name AS module, lv.listtype AS record ");
      query.append("FROM listviews lv, defaultviews df, listtypes lt, module m1, module m2 ");
      if (applyFilter) { query.append(", customviewlistfilter AS lf "); }
      query.append("WHERE df.viewid != lv.viewid AND lv.listtype = df.listtype AND ");
      query.append("lt.typename = lv.listtype AND lt.moduleid = m1.moduleid AND ");
      query.append("m1.parentid = m2.moduleid ");
      if (applyFilter) { query.append("AND m2.moduleid = lf.moduleid "); }
      query.append("UNION ");
      query.append("SELECT lv.viewid AS viewid, lv.viewname AS viewname, m2.name AS module, lv.listtype AS record ");
      query.append("FROM listviews lv, defaultviews df, listtypes lt, module m2 ");
      if (applyFilter) { query.append(", customviewlistfilter AS lf "); }
      query.append("WHERE df.viewid != lv.viewid AND lv.listtype=df.listtype AND ");
      query.append("lt.typename = lv.listtype AND lt.moduleid = m2.moduleid ");
      if (applyFilter) { query.append("AND m2.moduleid = lf.moduleid "); }
      query.append("ORDER BY " + String.valueOf(parameters.getSortColumn() + " " + parameters.getSortDirection()));
      query.append(parameters.getLimitParam());


      cvdal.setSqlQuery(query.toString());
      list = cvdal.executeQueryList(1);
      cvdal.setSqlQueryToNull();
      
      if (applyFilter){
        cvdal.setSqlQueryToNull();
        cvdal.setSqlQuery("DROP TABLE customviewlistfilter");
        cvdal.executeUpdate();
      }
      if (applyFilter || permissionSwitch) {
        cvdal.setSqlQueryToNull();
        cvdal.setSqlQuery("DROP TABLE listfilter");
        cvdal.executeUpdate();
      }
      return new ValueListVO(list, parameters);
    } catch (Exception e) {
      System.out.println("[getReportList] Exception thrown."+ e);
      throw new EJBException(e);
    } finally {
      cvdal.destroy();
    }


  }   // end getCustomViewList() method

View Full Code Here

  }   // end getCustomViewList() method


  
  public ValueListVO getAtticList(int individualId, ValueListParameters parameters)
  {
    CVDal cvdal = new CVDal(this.dataSource);
    try {
      ArrayList list = new ArrayList();
      
      // permissionSwitch turns the permission parts of the query on and off.
      // if individualID is less than zero then the list is requested without limiting
      // rows based on record rights.  If it is true than the rights are used.
      boolean permissionSwitch = false;
      boolean applyFilter = false;
      String filter = parameters.getFilter();
      
      if (filter != null && filter.length() > 0) {
        String str = "CREATE TABLE atticlistfilter " + filter;
        cvdal.setSqlQuery(str);
        cvdal.executeUpdate();
        cvdal.setSqlQueryToNull();
        applyFilter = true;
      }
      int numberOfRecords = 0;
      parameters.setTotalRecords(numberOfRecords);


      StringBuffer query = new StringBuffer("");


      query.append("SELECT at.atticid AS atticid, at.recordtitle AS title, m.name AS module, ");
      query.append("cv.name AS  record, CONCAT(owner.FirstName, ' ', owner.LastName) AS Owner, ");
      query.append("CONCAT(deletor.FirstName, ' ', deletor.LastName) AS deletedby, at.owner AS ownerid, ");
      query.append("at.deletedby AS deletorid, owner.individualid AS individualid ");
      query.append("FROM attic at LEFT OUTER JOIN individual owner ON (owner.individualid = at.owner), ");
      query.append("module m, cvtable cv LEFT OUTER JOIN individual deletor ON (deletor.individualid = at.deletedby) ");
      if (applyFilter){ query.append(", atticlistfilter alf "); }
      query.append("WHERE at.moduleid = m.moduleid AND at.record = cv.tableid AND at.dumpType='CV_ATTIC' ");
      if (applyFilter) { query.append("AND at.atticid = alf.atticid "); }
      query.append("ORDER BY " + String.valueOf(parameters.getSortColumn() + " " + parameters.getSortDirection()));
      query.append(parameters.getLimitParam());


      cvdal.setSqlQuery(query.toString());
      list = cvdal.executeQueryList(1);
      cvdal.setSqlQueryToNull();
      
      if (applyFilter){
        cvdal.setSqlQueryToNull();
        cvdal.setSqlQuery("DROP TABLE atticlistfilter");
        cvdal.executeUpdate();
      }
      return new ValueListVO(list, parameters);
    } catch (Exception e) {
      System.out.println("[getReportList] Exception thrown."+ e);
      throw new EJBException(e);
    } finally {
      cvdal.destroy();
    }
  }   // end getAtticList() method

View Full Code Here

  }   // end getAtticList() method




  public ValueListVO getGarbageList(int individualId, ValueListParameters parameters)
  {
    CVDal cvdal = new CVDal(this.dataSource);
    try {
      ArrayList list = new ArrayList();
      
      // permissionSwitch turns the permission parts of the query on and off.
      // if individualID is less than zero then the list is requested without limiting
      // rows based on record rights.  If it is true than the rights are used.
      boolean permissionSwitch = false;
      boolean applyFilter = false;
      String filter = parameters.getFilter();
      
      if (filter != null && filter.length() > 0) {
        String str = "CREATE TABLE garbagelistfilter " + filter;
        cvdal.setSqlQuery(str);
        cvdal.executeUpdate();
        cvdal.setSqlQueryToNull();
        applyFilter = true;
      }
      int numberOfRecords = 0;


      StringBuffer query = new StringBuffer("");
      query.append("CREATE TEMPORARY TABLE garbagelist SELECT at.atticid AS atticid, at.recordtitle AS title, m.name AS module, ");
      query.append("cv.name AS record, CONCAT(indv.FirstName, ' ', indv.LastName) AS Owner, ");
      query.append("CONCAT(indv1.FirstName, ' ', indv1.LastName) AS deletedby, at.owner AS ownerid, ");
      query.append("at.deletedby AS deletorid, indv.individualid AS individualid ");
      query.append("FROM attic at LEFT OUTER JOIN individual indv ON (indv.individualid = at.owner), ");
      query.append("module m, cvtable cv LEFT OUTER JOIN individual indv1 ON (indv1.individualid = at.deletedby) ");
      if (applyFilter){ query.append(", garbagelistfilter glf "); }
      query.append("WHERE at.moduleid = m.moduleid AND at.record = cv.tableid AND at.dumpType='CV_GARBAGE' ");
      if (applyFilter) { query.append("AND at.atticid = glf.atticid "); }
      query.append("ORDER BY " + String.valueOf(parameters.getSortColumn() + " " + parameters.getSortDirection()));
      query.append(parameters.getLimitParam());


      cvdal.setSqlQuery(query.toString());
      cvdal.executeUpdate();
      cvdal.setSqlQueryToNull();
      
      cvdal.setSqlQuery("SELECT * from garbagelist");
      list = cvdal.executeQueryList(1);
      cvdal.setSqlQueryToNull();


      cvdal.setSqlQuery("SELECT count(*) AS NoOfRecords FROM garbagelist");
      Collection colList = cvdal.executeQuery();
      cvdal.clearParameters();
      cvdal.setSqlQueryToNull();


    if (colList != null)
    {
     Iterator it = colList.iterator();
     if (it.hasNext())
     {
       HashMap hm = (HashMap)it.next();
       numberOfRecords = ((Number)hm.get("NoOfRecords")).intValue();
     }//end of if (it.hasNext())
    }//end of if (colList != null)
   
      parameters.setTotalRecords(numberOfRecords);


      cvdal.setSqlQueryToNull();
      cvdal.setSqlQuery("DROP TABLE garbagelist");
      cvdal.executeUpdate();


      if (applyFilter){
        cvdal.setSqlQueryToNull();
        cvdal.setSqlQuery("DROP TABLE garbagelistfilter");
        cvdal.executeUpdate();
      }
      return new ValueListVO(list, parameters);
    } catch (Exception e) {
      System.out.println("[getReportList] Exception thrown."+ e);
      throw new EJBException(e);
    } finally {
      cvdal.destroy();
    }
  }   // end getGarbageList() method

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of com.centraview.common.CVDal

com.centraview.account.accountlist.AccountListEJB

com.centraview.account.expense.ExpenseEJB

com.centraview.account.helper.AccountHelperEJB

com.centraview.account.inventory.InventoryEJB

com.centraview.account.invoice.InvoiceEJB

com.centraview.account.item.ItemEJB

com.centraview.account.order.OrderEJB

com.centraview.account.payment.PaymentEJB

com.centraview.account.purchaseorder.PurchaseOrderEJB

com.centraview.activity.activityfacade.ActivityFacadeEJB

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.