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.

   * a module.
   */
  public HashMap getPrimaryTableForModule(int moduleID)
  {
    HashMap primaryTableHashMap = new HashMap();
    CVDal cvdal = new CVDal(this.dataSource);


    try
    {
      String sqlQuery = "SELECT st.SearchTableID, st.DisplayName, "
        + "st.TableName, st.TablePrimaryKey FROM searchtable st,"
        + "searchmodule sm WHERE st.SearchTableID = sm.SearchTableID "
        + "AND sm.IsPrimaryTable = 'Y' AND sm.moduleid = ?";
      cvdal.setSqlQuery(sqlQuery);
      cvdal.setInt(1, moduleID);
      Collection resultsCollection = cvdal.executeQuery();
      if (resultsCollection != null)
      {
        Iterator resultsIterator = resultsCollection.iterator();
        if (resultsIterator.hasNext())
        {
          HashMap resultsHashMap = (HashMap) resultsIterator.next();
          Number tableID = (Number) resultsHashMap.get("SearchTableID");
          String tableDisplayName = (String) resultsHashMap.get("DisplayName");
          String tableName = (String) resultsHashMap.get("TableName");
          String tablePrimaryKey = (String) resultsHashMap.get("TablePrimaryKey");
          primaryTableHashMap.put("TableID", tableID);
          primaryTableHashMap.put("TableDisplayName", tableDisplayName);
          primaryTableHashMap.put("TableName", tableName);
          primaryTableHashMap.put("TablePrimaryKey", tablePrimaryKey);
        } //end of if statement (resultsIterator.hasNext())
      } //end of if statement (resultsCollection != null)
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.getPrimaryTableForModule: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block (Exception)
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block


    return primaryTableHashMap;
  } //end of getPrimaryTableForModule method

View Full Code Here

   * for this moduleID
   */
  public HashMap getSearchTablesForModule(int individualID, int moduleID)
  {
    HashMap tablesHashMap = new HashMap();
    CVDal cvdal = new CVDal(this.dataSource);
    try
    {
      String sqlQuery = "SELECT st.SearchTableID, st.DisplayName "
        + "FROM searchtable st, searchmodule sm WHERE "
        + "st.SearchTableID = sm.SearchTableID AND sm.moduleid = ?";
      cvdal.setSqlQuery(sqlQuery);
      cvdal.setInt(1, moduleID);
      Collection resultsCollection = cvdal.executeQuery();
      if (resultsCollection != null)
      {
        Iterator resultsIterator = resultsCollection.iterator();
        while (resultsIterator.hasNext())
        {
          HashMap resultsHashMap = (HashMap) resultsIterator.next();
          Number tableID = (Number) resultsHashMap.get("SearchTableID");
          String tableName = (String) resultsHashMap.get("DisplayName");
          tablesHashMap.put(tableID, tableName);
        } //end of while loop (resultsIterator.hasNext())
      } //end of if statement (resultsCollection != null)
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.getSearchTablesForModule: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block (Exception)
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block
    return tablesHashMap;
  } //end of getSearchTablesForModule method

View Full Code Here

   * for this tableID.
   */
  public HashMap getSearchFieldsForTable(int individualID, int tableID, int moduleId)
  {
    HashMap fieldHashMap = new HashMap();
    CVDal cvdal = new CVDal(this.dataSource);
    Collection resultsCollection = null;
    try
    {
      if (tableID != Integer.parseInt(SearchVO.CUSTOM_FIELD_TABLEID))
      {
        String sqlString = "SELECT SearchFieldID, DisplayName "
          + "FROM searchfield WHERE searchtableid = ?";
        //Note: We will need to revisit field authorization
        //at a later point in time. Right now, the data
        //in modulefieldmapping is too messed up to effectivly
        //handle fieldauthorization with the correct field names
        //in the database.
        cvdal.setSqlQuery(sqlString);
        cvdal.setInt(1, tableID);
        resultsCollection = cvdal.executeQuery();
      } else { // end of if (tableID != Integer.parseInt(CUSTOM_FIELD_TABLEID))
        // we need to get the custom fields, first find the id for cvtable
        // based on the moduleId.
        Number tableId = null;
        String sqlString = "SELECT cv.tableid AS id FROM cvtable AS cv, module "
             + "WHERE module.moduleid = ? AND UPPER(cv.name) = UPPER(module.name)";
        cvdal.setSqlQuery(sqlString);
        cvdal.setInt(1, moduleId);
        Collection tableIds = cvdal.executeQuery();
        if (tableIds != null)
        {
          Iterator tableIdsIterator = tableIds.iterator();
          if (tableIdsIterator.hasNext())
          {
            HashMap tableIdMap = (HashMap)tableIdsIterator.next();
            tableId = (Number)tableIdMap.get("id");
          } // end if (tableIdsIterator.hasNext())
        } // end if (tableIds != null)
        // now get the list of Fields.
        sqlString = "SELECT customfieldid AS SearchFieldID, name AS DisplayName FROM customfield "
          + "WHERE recordtype = ?";
        cvdal.setSqlQueryToNull();
        cvdal.setSqlQuery(sqlString);
        cvdal.setInt(1, tableId.intValue());
        resultsCollection = cvdal.executeQuery();
      } // end of else (if (tableID != Integer.parseInt(CUSTOM_FIELD_TABLEID)))
      // iterate the results and populate the return HashMap
      if (resultsCollection != null)
      {
        Iterator resultsIterator = resultsCollection.iterator();
        while (resultsIterator.hasNext())
        {
          HashMap resultsHashMap = (HashMap) resultsIterator.next();
          Number fieldID = (Number) resultsHashMap.get("SearchFieldID");
          String fieldName = (String) resultsHashMap.get("DisplayName");
          fieldHashMap.put(fieldID, fieldName);
        } //end of while loop (resultsIterator.hasNext())
      } //end of if statement (resultsCollection != null)
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.getSearchFieldsForTable: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block (Exception)
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block


    return fieldHashMap;
  } //end of getSearchFieldsForTable method

View Full Code Here

   * status from projectstatus table
   * @return Vector containing list of available statuses
   */
  public Vector getProjectStatusList()
  {
    CVDal dl = new CVDal(this.dataSource);
    Collection col = null;
    try {
      dl.setSql("project.getprojectstatuslist");
      col = dl.executeQuery();
      dl.clearParameters();
    } finally {
      dl.destroy();
    }
    return fillProjectStatusList(col);
  }

View Full Code Here

  * 
  * @return Vector
  */
  public Vector getTaskStatusList() throws java.rmi.RemoteException
  {
     CVDal dl = new CVDal(this.dataSource);
     //ALLSQL.put("project.getprojectstatuslist", "select title from projectstatus");
     dl.setSql("project.getprojectstatuslist");
     Collection col = dl.executeQuery();
     dl.clearParameters();
       dl.destroy();
     return fillProjectStatusList(col);
  }

View Full Code Here

   * field, false otherwise.
   */
  private boolean isFieldViewable(int individualID, int fieldID)
  {
    boolean isViewable = false;
    CVDal cvdal = new CVDal(this.dataSource);


    try
    {
      String privilegeQuery = "SELECT fa.privilegelevel FROM "
        + "fieldauthorisation fa, usersecurityprofile usp, "
        + "cvtable ct, field f, searchfield sf, searchtable st WHERE "
        + "f.tableid = ct.tableid AND f.fieldid = fa.fieldid AND "
        + "fa.profileid = usp.profileid AND usp.individualid = ? AND "
        + "UPPER(f.name) = UPPER(sf.FieldName) AND "
        + "UPPER(cv.name) = UPPER(st.TableName) AND "
        + "(fa.privilegelevel > 0 AND fa.privilegelevel < 40) AND"
        + "sf.SearchTableID = st.SearchTableID AND sf.SearchFieldID = ?";
      cvdal.setSqlQuery(privilegeQuery);
      cvdal.setInt(1, individualID);
      cvdal.setInt(2, fieldID);
      Collection privilegeResults = cvdal.executeQuery();
      cvdal.setSqlQueryToNull();
      if (privilegeResults != null)
      {
        Iterator privilegeIterator = privilegeResults.iterator();
        if (privilegeIterator.hasNext())
        {
          HashMap tableFieldHashMap = (HashMap) privilegeIterator.next();
          Number privilegeLevel = (Number)
            tableFieldHashMap.get("privilegelevel");


          if (privilegeLevel.intValue() > 0
            && privilegeLevel.intValue() < 40)
          {
            isViewable = true;
          } //end of if statement (privilegeLevel.intValue() > 0...
        } //end of if statement (tableFieldIterator.hasNext())
      } //end of if statement (tableFieldResults != null)
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.isFieldViewable: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block (Exception)
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block


    return isViewable;
  } //end of isFieldViewable method

View Full Code Here

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


    try
    {
      CVDal dl = new CVDal(dataSource);
      dl.setSql("projecttask.gettaskactivity");
      dl.setInt(1, taskId);
      tdbvo.setActivityID(taskId);
      Collection col = dl.executeQuery();
      Iterator ite = col.iterator();
      if (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        tdbvo.setTitle((String)hm.get("title"));
        tdbvo.setActivityDetails((String)hm.get("details"));
        tdbvo.setCreatedBy(((Integer)hm.get("creator")).intValue());
        tdbvo.setCreatedOn((Timestamp)hm.get("created"));


        if (hm.get("modified") != null)
        {
          tdbvo.setModifiedOn((Timestamp)hm.get("modified"));
        }


        if (hm.get("owner") != null)
        {
          tdbvo.setOwner(((Integer)hm.get("owner")).intValue());
        }


        if (hm.get("modifiedby") != null)
        {
          tdbvo.setModifiedBy(((Integer)hm.get("modifiedby")).intValue());
        }


        if (hm.get("status") != null)
        {
          tdbvo.setStatus(((Integer)hm.get("status")).intValue());
        }


        if (hm.get("name") != null)
        {
          tdbvo.setSelectedStatus((String)hm.get("name"));
        }


        if (hm.get("start") != null)
        {
          Object o = hm.get("start");
          tdbvo.setStart((java.sql.Timestamp)o);
        }


        if (hm.get("duedate") != null)
        {
          Object ox = hm.get("duedate");
          tdbvo.setEnd((java.sql.Timestamp)ox);
        }


        tdbvo.fillAuditDetails(this.dataSource);
      }
      dl.clearParameters();


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


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


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


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


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


        if (hm.get("projecttaskcount") != null)
        {
          tdbvo.setProjectTaskCount(((Integer)hm.get("projecttaskcount")).intValue());
        }
      }   // end if (ite.hasNext())
      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)
        {
          tdbvo.setAssignedTo(((Long)hm.get("individualid")).intValue(), (String)hm.get("CONCAT(firstname, ' ' , lastname)"));
        }
      }
      dl.clearParameters();


      Collection col1 = null;
      Iterator ite1 = null;


      int activityid = -1;
      dl.setSql("projecttask.gettaskalert");
      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();
        if (hm.get("actionid") != null)
        {
          activityid = ((Long)hm.get("actionid")).intValue();
        }
        dl.setSql("projecttask.gettaskalertaction");
        dl.setInt(1, activityid);
        col1 = dl.executeQuery();
        ite1 = col1.iterator();
        if (ite1.hasNext())
        {
          Object o = ite1.next();


          HashMap hhm = (HashMap)o;


          String type = (String)hhm.get("type");


          if (type.equals("ALERT"))
          {
            alert = true;
            alerta.put((Long)hm.get("recipient"), (String)hm.get("concat(individual.firstname,' ', individual.lastname)"));
          }else if (type.equals("EMAIL")){
            email = true;
            emaila.put((Long)hm.get("recipient"), hm.get("concat(individual.firstname,' ', individual.lastname)"));
          }   // end if (type.equals("ALERT"))
        }   // end if (ite1.hasNext())
      }   // end while (ite.hasNext())


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


      dl.clearParameters();


      dl.setSql("projecttask.getsubtask");
      dl.setInt(1, taskId);
      tdbvo.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)
          {
            tdbvo.setParentID(((Long)hm.get("projectid")).intValue());
          }
        }


        if (hm.get("milestone") != null)
        {
          tdbvo.setIsMileStone((String)hm.get("milestone"));
        }
      }   // end if (ite.hasNext())


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


      dl.setSql("projecttask.getparenttaskname");
      dl.setInt(1, tdbvo.getParentID());
      col = dl.executeQuery();
      ite = col.iterator();
      if (ite.hasNext())
      {
        HashMap hm = (HashMap)ite.next();
        if (hm.get("title") != null)
        {
          tdbvo.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)
        {
          tdbvo.setStat(((Integer)hm.get("statusid")).intValue(), (String)hm.get("name"));
        }
      }
      dl.clearParameters();


      int parentId = tdbvo.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;
            }
          }   // end if (ite.hasNext())
        }   // end while (flag)


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

View Full Code Here

      throw new AuthorizationFailedException("Projects - Delete Project");
    }


    try
    {
      CVDal cvdl = new CVDal(dataSource);


      cvdl.setSql("project.getproject");
      cvdl.setInt(1, projectId);
      Collection colPrj = cvdl.executeQuery();


      String strTitle = "";
      int intOwner = 0;


      if (colPrj.size() > 0)
      {
        Iterator iter = colPrj.iterator();


        while (iter.hasNext())
        {
          HashMap hm = (HashMap)iter.next();


          if (hm.get("ProjectTitle") != null)
          {
            strTitle = (String)hm.get("ProjectTitle");
          }


          if (hm.get("Owner") != null)
          {
            intOwner = ((Long)hm.get("Owner")).intValue();
          }
        }
      }


      HashMap hmDetails = new HashMap();
      hmDetails.put("title", strTitle);
      hmDetails.put("owner", new Integer(intOwner));
      hmDetails.put("module", new Integer(9));
      hmDetails.put("recordtype", new Integer(48));


      //Get the transactionID
      transactionID = getAtticTransactionID(userID, Constants.CV_GARBAGE, hmDetails);


      HashMap hmPrj = new HashMap();
      hmPrj.put("ProjectID", (new Integer(projectId)).toString());
      sendToAttic(userID, transactionID, "project", hmPrj);


      cvdl.setSql("project.deleteproject");
      cvdl.setInt(1, projectId);
      cvdl.executeUpdate();
      cvdl.clearParameters();


      cvdl.clearParameters();
      cvdl.setSqlQuery("SELECT * FROM projectlink WHERE projectid = ?");
      cvdl.setInt(1, projectId);
      Collection colPrjLink = cvdl.executeQuery();
      cvdl.clearParameters();


      if (colPrjLink.size() > 0)
      {
        Iterator iterator = colPrjLink.iterator();
        while (iterator.hasNext())
        {
          HashMap hm = (HashMap)iterator.next();
          int intRecord = 0;
          if (hm.get("RecordID") != null)
          {
            intRecord = ((Long)hm.get("RecordID")).intValue();
          }


          HashMap hmPrjLink = new HashMap();
          hmPrjLink.put("ProjectID", (new Integer(projectId)).toString());
          hmPrjLink.put("RecordID", (new Integer(intRecord)).toString());
          sendToAttic(userID, transactionID, "projectlink", hmPrjLink);
        }
      }


    HashMap historyInfo = new HashMap();
    historyInfo.put("recordID",new Integer(projectId));
    historyInfo.put("recordTypeID", new Integer(Constants.ProjectModuleID));
    historyInfo.put("operation", new Integer(Constants.DELETED));
    historyInfo.put("individualID", new Integer(userID));
    historyInfo.put("referenceActivityID", new Integer(0));      
    historyInfo.put("recordName", strTitle);
      CVUtility.addHistoryRecord(historyInfo,dataSource);


      cvdl.setSql("project.deleteprojecttimeslip");
      cvdl.setInt(1, projectId);
      cvdl.executeUpdate();
      cvdl.clearParameters();


      cvdl.setSql("project.deleteprojectlink");
      cvdl.setInt(1, projectId);
      cvdl.executeUpdate();
      cvdl.clearParameters();


      cvdl.destroy();
    }catch (Exception e){
      System.out.println("[Exception][ProjectEJB.deleteProject] Exception Thrown: " + e);
    }
  }

View Full Code Here

   * @return ProjectName The ProjectName
   */
  public String getProjectName(int ProjectID)
  {
    String ProjectName = "";
    CVDal dl = new CVDal(dataSource);
    try
    {
      String ProjectQuery = "select ProjectID, ProjectTitle  from project where ProjectID = ?";
      dl.setSqlQuery(ProjectQuery);
      dl.setInt(1, ProjectID);
      Collection col = dl.executeQuery();


      if (col != null)
      {
        Iterator it = col.iterator();
        while (it.hasNext())
        {
          HashMap hm = (HashMap) it.next();
          ProjectName = (String) hm.get("ProjectTitle");
        }// end of while (it.hasNext())
      }//end of if (col != null)
    }catch (Exception e){
      e.printStackTrace();
    }finally{
      dl.clearParameters();
      dl.destroy();
      dl = null;
    } //end of finally block
    return ProjectName;
  }   // end getProjectName() method

View Full Code Here

    return tdbvo;
  }


  public int deleteTask(int taskId,int individualID)
  {
    CVDal cvdl = new CVDal(dataSource);
    cvdl.setSql("projecttask.getsubtask");
    cvdl.setInt(1, taskId);
    Collection col = cvdl.executeQuery();
    cvdl.clearParameters();


    deleteTask1(taskId,individualID);
    int activityID = 0;


    if (col != null)

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.