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.

  /**
   * this method delete rule
   */
  public int deleteTask1(int taskId,int individualID)
  {
    CVDal cvdl = new CVDal(dataSource);


    try
    {
      InitialContext ic = CVUtility.getInitialContext();
      ActivityHelperLocalHome home = (ActivityHelperLocalHome)ic.lookup("local/ActivityHelper");
      ActivityHelperLocal remote = (ActivityHelperLocal) home.create();
      remote.setDataSource(dataSource);
      remote.deleteActivity(taskId,individualID);


      cvdl.setSql("projecttask.selecttaskaction");
      cvdl.setInt(1, taskId);
      Collection col = cvdl.executeQuery();
      Iterator itCount = col.iterator();
      if (itCount.hasNext())
      {
        HashMap hm = (HashMap)itCount.next();
        int actionId = ((Long)hm.get("actionid")).intValue();
        cvdl.setSql("projecttask.deletetaskalertaction");
        cvdl.setInt(1, actionId);
        cvdl.executeUpdate();
      }
      cvdl.clearParameters();
      cvdl.setSql("projecttask.deletetaskalert");
      cvdl.setInt(1, taskId);
      cvdl.executeUpdate();


      cvdl.setSql("projecttask.setidnull");
      cvdl.setInt(1, taskId);
      cvdl.executeUpdate();


    }catch (Exception e){
      System.out.println("[Exception][TaskEJB.deleteTask1] Exception Thrown: " + e);
      e.printStackTrace();
    }finally{
      cvdl.destroy();
      cvdl = null;
    }
    return 0;
  }

View Full Code Here

      StringBuffer fieldCriterion, int moduleId, Collection allTabls)
  {
    String recordOwnerField = "";
    String recordOwnerTable = "";
    StringBuffer selectQuery = new StringBuffer();
    CVDal cvdal = new CVDal(this.dataSource);


    try
    {
      // opporunities are activities, so the owner field can be found there.
      if (realTableName.equalsIgnoreCase("opportunity") || realTableName.equalsIgnoreCase("literaturerequest") || realTableName.equalsIgnoreCase("task"))
      {
        selectQuery.append(", activity");
      } //end of if statement (thisRealTable.equalsIgnoreCase(...
      // we don't have the owner column for the Vendor, Employee, Glaccount
      else if (moduleId == 50 ||  moduleId == 54 ||  moduleId == 47){
      }//end of if statement (moduleId == 50 || ...
      else
      {
        // get the owner field for the module
        String moduleOwnerString = "SELECT primarytable, ownerfield "
          + "FROM module WHERE moduleid = ?";
        cvdal.setSqlQueryToNull();
        cvdal.setSqlQuery(moduleOwnerString);
        cvdal.setInt(1, moduleId);
        Collection ownerResults = cvdal.executeQuery();
        cvdal.setSqlQueryToNull();
        if (ownerResults != null)
        {
            Iterator ownerIterator = ownerResults.iterator();
            if (ownerIterator.hasNext())
            {
              HashMap ownerHashMap = (HashMap) ownerIterator.next();
              recordOwnerField = (String) ownerHashMap.get("ownerfield");
              recordOwnerTable = (String) ownerHashMap.get("primarytable");
            } //end of if statement (ownerIterator.hasNext())
        } //end of if statement (ownerResults != null)
      } //end of else statement (thisRealTable.equalsIgnoreCase(...


      if ((!allTabls.contains(recordOwnerTable))&&(!recordOwnerTable.equals("")))
        {
        allTabls.add(recordOwnerTable);
        selectQuery.append(", " + recordOwnerTable);
      }
      // Record Owner
      selectQuery.append(" WHERE ");
      if (realTableName.equalsIgnoreCase("opportunity"))
      {
        selectQuery.append("opportunity.activityid = activity.activityid AND ");
        selectQuery.append("opportunity.opportunityid = " + primaryKey);
        selectQuery.append(" AND activity.owner = " + individualID);
        selectQuery.append(" AND ");
      } //end of if statement (thisRealTable.equalsIgnoreCase(...
      else if (realTableName.equalsIgnoreCase("literaturerequest"))
      {
        selectQuery.append("literaturerequest.activityid = activity.activityid AND ");
        selectQuery.append("activity.owner = " + individualID);
        selectQuery.append(" AND ");
      } //end of if statement (thisRealTable.equalsIgnoreCase(...
      else if (realTableName.equalsIgnoreCase("task"))
      {
        selectQuery.append("task.activityid = activity.activityid AND ");
        selectQuery.append(" activity.owner = " + individualID);
        selectQuery.append(" AND ");
      } //end of if statement (thisRealTable.equalsIgnoreCase(...
      // we don't have the owner column for the Vendor, Employee, Glaccount
      else if (moduleId == 50 ||  moduleId == 54 ||  moduleId == 47)
      {
      } //end of if statement (moduleId == 50 || ...
      else
      {
        selectQuery.append(recordOwnerTable + ".");
        selectQuery.append(recordOwnerField + " = " + individualID);
        selectQuery.append(" AND ");
      } //end of else statement (thisRealTable.equalsIgnoreCase(...


      selectQuery.append(fieldCriterion);
      //End Record Owner


      // Public Record
      selectQuery.append(" UNION ");
      selectQuery.append(initialQuery);
      selectQuery.append(", publicrecords WHERE publicrecords.moduleid = ");
      selectQuery.append(moduleId);
      selectQuery.append(" AND publicrecords.recordid = ");
      selectQuery.append(realTableName + "." + primaryKey);
      selectQuery.append(" AND ");
      selectQuery.append(fieldCriterion);
      // End Public Record


      // Record Permissions
      selectQuery.append(" UNION ");
      selectQuery.append(initialQuery);
      selectQuery.append(", recordauthorisation WHERE recordauthorisation.recordid = ");
      selectQuery.append(realTableName + "." + primaryKey);
      selectQuery.append(" AND recordauthorisation.recordtypeid = ");
      selectQuery.append(moduleId);
      selectQuery.append(" AND recordauthorisation.privilegelevel > 0 AND ");
      selectQuery.append("recordauthorisation.privilegelevel < 40 AND ");
      selectQuery.append("recordauthorisation.individualid = " + individualID);
      selectQuery.append(" AND ");
      selectQuery.append(fieldCriterion);
      // End Record Permissions
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.getRecordPermissionString: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block (Exception)
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of try block


    return selectQuery.toString();
  } //end of getRecordPermissionString method

View Full Code Here

   * @return A Integer The ID of the search on basis of module.
   */
  public Integer getSearchID(int moduleID)
  {
    Integer searchID = new Integer(0);
    CVDal cvdal = new CVDal(this.dataSource);
    try
    {
      String sqlQuery = "SELECT SearchID FROM simplesearch WHERE 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();
          int searchId = ((Number) resultsHashMap.get("SearchID")).intValue();
          searchID = new Integer(searchId);
        } //end of if statement (resultsIterator.hasNext())
      } //end of if statement (resultsCollection != null)
    } //end of try block
    catch (Exception e)
    {
      logger.error("[getSearchID] Exception thrown.", e);
    } //end of catch block (Exception)
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block


    return searchID;
  } //end of getSearchID method

View Full Code Here

   * @return The ID of the newly added search.
   */
  public int addNewSearch(int individualID, SearchVO searchVO)
  {
    int newSearchID = -1;
    CVDal cvdal = new CVDal(this.dataSource);


    try
    {
      String sqlString = "INSERT INTO search (ModuleID, OwnerID, "
        + "CreatedBy, CreationDate, ModifiedBy, ModifiedDate, SearchName) "
        + "VALUES (?, ?, ?, ?, ?, ?, ?)";
      cvdal.setSqlQuery(sqlString);
      cvdal.setInt(1, searchVO.getModuleID());
      cvdal.setInt(2, individualID);
      cvdal.setInt(3, individualID);
      cvdal.setRealDate(4, new java.sql.Date(new Date().getTime()));
      cvdal.setInt(5, individualID);
      cvdal.setRealTimestamp(6, new Timestamp(new Date().getTime()));
      cvdal.setString(7, searchVO.getName());


      cvdal.executeUpdate();
      newSearchID = cvdal.getAutoGeneratedKey();


      Collection searchCriteria = searchVO.getSearchCriteria();
      if (searchCriteria != null)
      {
        Iterator iterator = searchCriteria.iterator();
        while (iterator.hasNext())
        {
          SearchCriteriaVO criteria = (SearchCriteriaVO) iterator.next();
          this.addNewSearchCriteria(newSearchID, criteria);
        } //end of while loop (iterator.hasNext())
      } //end of if statement (searchCriteria != null)
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.addNewSearch: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block (Exception)
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block


    return newSearchID;
  } //end of addNewSearch method

View Full Code Here

   * -1 if the search criteria was not added.
   */
  private int addNewSearchCriteria(int searchID, SearchCriteriaVO searchCriteriaVO)
  {
    int newSearchCriteriaID = -1;
    CVDal cvdal = new CVDal(this.dataSource);


    try
    {
      String sqlString = "INSERT INTO searchcriteria (SearchID, "
        + "SearchTableID, SearchFieldID, ConditionID, ExpressionType, "
        + "Value, CriteriaGroup) VALUES (?, ?, ?, ?, ?, ?, ?)";
      cvdal.setSqlQuery(sqlString);
      cvdal.setInt(1, searchID);
      cvdal.setInt(2, Integer.parseInt(searchCriteriaVO.getTableID()));
      cvdal.setInt(3, Integer.parseInt(searchCriteriaVO.getFieldID()));
      cvdal.setInt(4, Integer.parseInt(searchCriteriaVO.getConditionID()));
      cvdal.setString(5, searchCriteriaVO.getExpressionType());
      cvdal.setString(6, searchCriteriaVO.getValue());
      cvdal.setInt(7, Integer.parseInt(searchCriteriaVO.getGroupID()));


      cvdal.executeUpdate();
      newSearchCriteriaID = cvdal.getAutoGeneratedKey();
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.addNewSearchCriteria: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block


    return newSearchCriteriaID;
  } //end of addNewSearchCriteria method

View Full Code Here

   * update query.
   */
  public int updateSavedSearch(int individualID, SearchVO searchVO)
  {
    int recordsAffected = 0;
    CVDal cvdal = new CVDal(this.dataSource);
    try
    {
      StringBuffer sqlString = new StringBuffer();
      sqlString.append("UPDATE search SET ModuleID = ?, OwnerID = ?, ");
      sqlString.append("ModifiedBy = ?, ModifiedDate = ?, SearchName = ? WHERE ");
      sqlString.append("SearchID = ?");
      cvdal.setSqlQuery(sqlString.toString());
      cvdal.setInt(1, searchVO.getModuleID());
      cvdal.setInt(2, searchVO.getOwnerID());
      cvdal.setInt(3, individualID);
      cvdal.setRealTimestamp(4, new Timestamp(new Date().getTime()));
      cvdal.setString(5, searchVO.getName());
      cvdal.setInt(6, searchVO.getSearchID());


      recordsAffected = cvdal.executeUpdate();


      Collection searchCriteria = searchVO.getSearchCriteria();
      if (searchCriteria != null)
      {
        ArrayList criteriaIds = new ArrayList();
        Iterator iterator = searchCriteria.iterator();
        while (iterator.hasNext())
        {
          SearchCriteriaVO searchCriteriaVO = (SearchCriteriaVO) iterator.next();
          if (searchCriteriaVO.getSearchCriteriaID().equals(""))
          {
            int newId = this.addNewSearchCriteria(searchVO.getSearchID(), searchCriteriaVO);
            criteriaIds.add(String.valueOf(newId));
          } //end of if statement (searchCriteriaVO.getSearchCriteriaID() <= 0)
          else
          {
            this.updateSearchCriteria(searchCriteriaVO);
            criteriaIds.add(searchCriteriaVO.getSearchCriteriaID());
          } //end of else statement (searchCriteriaVO.getSearchCriteriaID() <= 0)
        } //end of while loop (iterator.hasNext())
        // Delete any Critieria from the database that wasn't included in the
        // VO that was passed in.
        sqlString.replace(0, sqlString.length(), "DELETE FROM searchcriteria WHERE SearchID = ? AND SearchCriteriaID NOT IN (");
        for (int i = 0; i < criteriaIds.size(); i++)
        {
          if (i != 0)
          {
            sqlString.append(", ");
          }
          sqlString.append((String)criteriaIds.get(i));
        } // end for (int i = 0; i < criteriaIds.size(); i++)
        sqlString.append(")");
        cvdal.setSqlQueryToNull();
        cvdal.setSqlQuery(sqlString.toString());
        cvdal.setInt(1, searchVO.getSearchID());
        cvdal.executeUpdate();
      } //end of if statement (searchCriteria != null)
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.updateSavedSearch: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block
    return recordsAffected;
  } //end of updateSavedSearch method

View Full Code Here

   */
  private int updateSearchCriteria(SearchCriteriaVO searchCriteriaVO)
  {
    int recordsAffected = 0;


    CVDal cvdal = new CVDal(this.dataSource);
    try
    {
      String sqlString = "UPDATE searchcriteria SET SearchTableID = ?, "
        + "SearchFieldID = ?, ConditionID = ?, ExpressionType = ?, "
        + "Value = ?, CriteriaGroup = ? WHERE SearchCriteriaID = ?";
      cvdal.setSqlQuery(sqlString);
      cvdal.setInt(1, Integer.parseInt(searchCriteriaVO.getTableID()));
      cvdal.setInt(2, Integer.parseInt(searchCriteriaVO.getFieldID()));
      cvdal.setInt(3, Integer.parseInt(searchCriteriaVO.getConditionID()));
      cvdal.setString(4, searchCriteriaVO.getExpressionType());
      cvdal.setString(5, searchCriteriaVO.getValue());
      cvdal.setInt(6, Integer.parseInt(searchCriteriaVO.getGroupID()));
      cvdal.setInt(7, Integer.parseInt(searchCriteriaVO.getSearchCriteriaID()));


      recordsAffected = cvdal.executeUpdate();
    } //end of try block
    catch (Exception e)
    {
      System.out.println("[Exception] AdvancedSearchEJB.updateSearchCriteria: "
        + e.toString());
      //e.printStackTrace();
    } //end of catch block
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block
    return recordsAffected;
  } //end of updateSearchCriteria method

View Full Code Here

   * delete query.
   */
  public int deleteSearch(int searchID)
  {
    int recordsAffected = 0;
    CVDal cvdal = new CVDal(this.dataSource);


    try
    {
      //There is no need to delete the searchcriteria records
      //they are taken care of with the foriegn key cascade delete.
      String sqlString = "DELETE FROM search WHERE SearchID = ?";
      cvdal.setSqlQuery(sqlString);
      cvdal.setInt(1, searchID);
      recordsAffected = cvdal.executeUpdate();
    } //end of try block
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block


    return recordsAffected;
  } //end of deleteSearch method

View Full Code Here

   * delete query.
   */
  public int deleteSearchCriteria(int searchCriteriaID)
  {
    int recordsAffected = 0;
    CVDal cvdal = new CVDal(this.dataSource);


    try
    {
      String sqlString = "DELETE FROM searchcriteria WHERE SearchCriteriaID = ?";
      cvdal.setSqlQuery(sqlString);
      cvdal.setInt(1, searchCriteriaID);
      recordsAffected = cvdal.executeUpdate();
    } //end of try block
    finally
    {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    } //end of finally block
    return recordsAffected;
  } //end of deleteSearchCriteria method

View Full Code Here

   * @return A HashMap of ID/Name value pairs.
   */
  public HashMap getSavedSearchList(int individualID, int moduleID)
  {
    HashMap searchHashMap = new HashMap();
    CVDal cvdal = new CVDal(this.dataSource);


    try {
      String sqlString = "SELECT SearchID, SearchName FROM search WHERE OwnerID = ? AND ModuleID = ?";
      cvdal.setSqlQuery(sqlString);
      cvdal.setInt(1, individualID);
      cvdal.setInt(2, moduleID);
      Collection resultsCollection = cvdal.executeQuery();
      if (resultsCollection != null) {
        Iterator iterator = resultsCollection.iterator();
        while (iterator.hasNext()) {
          HashMap resultsHashMap = (HashMap) iterator.next();
          Number searchID = (Number) resultsHashMap.get("SearchID");
          String searchName = (String) resultsHashMap.get("SearchName");
          searchHashMap.put(searchID, searchName);
        }
      }
    } catch(Exception e) {
      System.out.println("[Exception] AdvancedSearchEJB.getSavedSearchList: "  + e.toString());
    } finally {
      cvdal.setSqlQueryToNull();
      cvdal.destroy();
      cvdal = null;
    }
    return searchHashMap;
  }   // end getSavedSearchList() 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.