documentmanagerplus.java

一个完整的

                                            String strTacheName,
                                            String strFillUserID,
                                            String strApproveUserID,
                                            String strOrderBy, String strIsDesc)
  {
    String strSQL = "SELECT a.[ID],a.[DocumentName],a.[DocFlowTempletID],a.[BuildTime],a.[BuildUserID],a.[FillUserID],a.[IsPigeonholed],"
      + "a.[CurrentFlowTacheID],a.[IsReminded],a.[RemindTime],c.[Name],e.[Name],f.[TacheName],f.[FromTacheID],f.[TacheStatus],"
      + "f.[ApproveUserIDs],f.[TacheType],f.[FlowID],f.[IsRecordRead],f.[RemindInterval],f.[MaxWaitTime],f.[IsFromDefault],f.[IsFinishedByRead]"
      + " FROM [Document] AS a LEFT OUTER JOIN  [UserInfo] AS b ON (a.[BuildUserID] = b.[ID]) LEFT OUTER JOIN [PersonnelInfo] AS c "
      + "ON (b.[PersonnelID] = c.[ID]) LEFT OUTER JOIN [UserInfo] AS d ON (a.[FillUserID] = d.[ID]) LEFT OUTER JOIN [PersonnelInfo] AS e"
      + " ON (d.[PersonnelID] = e.[ID]) LEFT OUTER JOIN [FlowTacheInfo] AS f ON (a.[CurrentFlowTacheID] = f.[ID]) WHERE a.[ID]>0 and NOT(a.[IsPigeonholed] = 1) and a.[CurrentFlowTacheID]>-2";

    if (strID.trim().length() > 0)
    {
      strSQL += " and a.[ID] ='" + strID + "'";
    }

    if (strDocumentName.trim().length() > 0)
    {
      strSQL += " and a.[DocumentName] like '%" + strDocumentName + "%'";
    }

    if (strBuildTimeFrom.trim().length() > 0)
    {
      strSQL += " and a.[BuildTime]>='" + strBuildTimeFrom + " 00:00:00.000'";
    }
    if (strBuildTimeTo.trim().length() > 0)
    {
      strSQL += " and a.[BuildTime]<='" + strBuildTimeTo + " 23:59:59.999'";
    }

    if (strBuildUserName.trim().length() > 0)
    {
      strSQL += " and c.[Name] like '%" + strBuildUserName + "%'";
    }
    if (strFillUserName.trim().length() > 0)
    {
      strSQL += " and e.[Name] like '%" + strFillUserName + "%'";
    }

    if (strTacheName.trim().length() > 0)
    {
      strSQL += " and f.[TacheName] like '%" + strTacheName + "%'";
    }

    if ( (strFillUserID.trim().length() > 0) &&
        (strApproveUserID.trim().length() > 0))
    {
      strSQL += " and ((a.[FillUserID]=" + strFillUserID +
        ") or (not(f.[TacheStatus]='S') and ',' + f.[ApproveUserIDs] + ',' LIKE '%," +
        strApproveUserID + ",%'))";
    }

    if (strOrderBy.trim().length() > 0)
    {
      if (strIsDesc.equalsIgnoreCase("True"))
      {
        strSQL += " Order by " + strOrderBy + " desc";
      }
      else
      {
        strSQL += " Order by " + strOrderBy + "";
      }
    }
    Vector vt = SQLManager.GetResultSet(strSQL);
    return vt;
  }

  /**
   * 重载getRecordBySearchAll方法，按ID返回结果
   * @param strID 公文模板ID
   * @return 符合条件记录向量集
   */
  public static Vector getRecordByIDAll(String strID)
  {
    Vector vt = getRecordBySearchAll(strID, "", "", "", "", "", "", "", "", "",
                                     "");
    return vt;
  }

  /**
   * 公文环节跳转
   * @param DocumentID 公文ID
   * @param NextTache 跳转环节ID
   * @return 整型值
   */
  public static int MoveFlow(String DocumentID, String NextTache)
  {
    String strSQL = "Update " + TableName +
      " set IsReminded=0,RemindTime=null,CurrentFlowTacheID='" + NextTache +
      "' where ID=" + DocumentID;
    return SQLManager.ExcuteSQL(strSQL);
  }

  /**
   * 返回应该被提醒的公文信息
   * @param strUserID 当前用户ID
   * @param CurrentTime 当前时间
   * @return 符合条件记录向量集
   */
  public static Vector UNReminded(String strUserID, String CurrentTime)
  {
    String strSQL = "SELECT a.[ID],a.[DocumentName],a.[FillUserID],a.[IsPigeonholed],a.[CurrentFlowTacheID],a.[IsReminded],"
      +
      "a.[RemindTime],b.[ApproveUserIDs],b.[RemindInterval],b.[TacheStatus],d.[Name] "
      + "FROM [Document] AS a LEFT OUTER JOIN [FlowTacheInfo] AS b ON (a.[CurrentFlowTacheID] = b.[ID])"
      + " LEFT OUTER JOIN [UserInfo] AS c ON (a.[FillUserID] = c.[ID]) LEFT OUTER JOIN [PersonnelInfo] AS d"
      + " ON (c.[PersonnelID] = d.[ID])"
      + " WHERE a.[ID]>0 and IsPigeonholed <> 1 and a.[CurrentFlowTacheID]>-2 and b.[RemindInterval]>0 and (((a.[FillUserID] = " +
      strUserID + ") and (b.[TacheStatus]='S')) or (not(b.[TacheStatus]='S') and (',' + b.[ApproveUserIDs] + ',') LIKE ('%," +
      strUserID + ",%')))"
      + " and ((a.[IsReminded]<>1) or (DATEDIFF(mi,a.[RemindTime],'" +
      CurrentTime + "')>=b.[RemindInterval]))";
    Vector vt = SQLManager.GetResultSet(strSQL);
    return vt;
  }

  /**
   * 设置公文提醒时间以及提醒标记
   * @param strID 公文ID
   * @param CurrentTime 当前时间
   * @return 整型值
   */
  public static int ResetReminded(String strID, String CurrentTime)
  {
    String strSQL = "UPDATE " + TableName + " SET IsReminded=1,RemindTime='" +
      CurrentTime + "' WHERE ID=" + strID;
    return SQLManager.ExcuteSQL(strSQL);
  }

  /**
   * 返回需要自动跳转的公文ID集合
   * @param CurrentTime 当前时间
   * @return 符合条件结果集
   */
  public static Vector NeedAutoFlow(String CurrentTime)
  {
    String strSQL = "select c.ID from Document as c left outer join FlowTacheInfo as d on (c.CurrentFlowTacheID=d.ID) "
      + "where  d.MaxWaitTime<>0 and (d.MaxWaitTime<=(datediff(mi,(Select top 1 a.ApproveTime from ApproveRecordInfo as"
      + " a left outer join Document as b on (a.DocumentID=b.ID) order by a.ApproveTime Desc),'" +
      CurrentTime + "')) or"
      + " ((datediff(mi,(Select top 1 a.ApproveTime from ApproveRecordInfo as a left outer join Document as b on "
      + "(a.DocumentID=b.ID) order by a.ApproveTime Desc),'" + CurrentTime +
      "') is null and d.MaxWaitTime<=(datediff(mi,c.BuildTime,'" +
      CurrentTime +
      "'))))) and c.IsPigeonholed <> 1 and c.[CurrentFlowTacheID]>-2";
    Vector vt = SQLManager.GetResultSet(strSQL);
    return vt;
  }

  /**
   * 公文环节自动跳转
   * @param strTacheID 环节ID
   * @param strDocumentID 公文ID
   * @return 整型值
   */
  public static int ResetCurrentTache(String strTacheID, String strDocumentID)
  {
    String strSQL = "UPDATE " + TableName + " SET CurrentFlowTacheID=" +
      strTacheID + " WHERE ID=" + strDocumentID;
    return SQLManager.ExcuteSQL(strSQL);
  }

  /**
   * 返回当前公文的下一个环节ID
   * @param DocumentID 公文ID
   * @return 符合条件向量集
   */
  public static Vector AutoFlowTache(String DocumentID)
  {
    String strSQL = "select top 1 ID from FlowTacheInfo where FromTacheID = "
      + "(select CurrentFlowTacheID from " + TableName + " where ID = " +
      DocumentID
      + ") and IsFromDefault = 1";
    Vector vt = SQLManager.GetResultSet(strSQL);
    return vt;
  }

  public static Vector VisitDoc(String strUserID, String strOrderBy,
                                String strIsDesc)
  {
    return VisitDoc(strUserID, "", "", "", "", "", "", strOrderBy, strIsDesc);
  }

  public static Vector VisitDoc(String strUserID, String strDocumentName,
                                String strBuildTimeFrom, String strBuildTimeTo,
                                String strBuildUserName, String strFillUserName,
                                String strTacheName, String strOrderBy,
                                String strIsDesc)
  {
    String strSQL = "select a.[ID],a.[DocumentName],a.[BuildTime],a.[BuildUserID],a.[FillUserID],a.[IsPigeonholed],a.[CurrentFlowTacheID]" +
      ",a.[DocFlowTempletID],c.[Name],e.[Name],f.[TacheName],g.[ReadOnlyUserIDs] from " +
      TableName + " As a LEFT OUTER JOIN" +
      " [UserInfo] AS b ON (a.[BuildUserID] = b.[ID]) LEFT OUTER JOIN [PersonnelInfo] AS c" +
      " ON (b.[PersonnelID] = c.[ID]) LEFT OUTER JOIN [UserInfo] AS d ON (a.[FillUserID] = d.[ID])" +
      " LEFT OUTER JOIN [PersonnelInfo] AS e ON (d.[PersonnelID] = e.[ID]) LEFT OUTER JOIN [FlowTacheInfo] AS" +
      " f ON (a.[CurrentFlowTacheID] = f.[ID]) left outer join [DocTempletMoveFlow] as g on (a.[DocFlowTempletID]=g.[ID])" +
      " where a.[IsPigeonholed]<>1 and a.[CurrentFlowTacheID]>-2 and g.[ReadOnlyUserIDs] like ('%" +
      strUserID + "%')";

    if (strDocumentName.trim().length() > 0)
    {
      strSQL += " and a.[DocumentName] like '%" + strDocumentName + "%'";
    }

    if (strBuildTimeFrom.trim().length() > 0)
    {
      strSQL += " and a.[BuildTime]>='" + strBuildTimeFrom + " 00:00:00.000'";
    }
    if (strBuildTimeTo.trim().length() > 0)
    {
      strSQL += " and a.[BuildTime]<='" + strBuildTimeTo + " 23:59:59.999'";
    }

    if (strBuildUserName.trim().length() > 0)
    {
      strSQL += " and c.[Name] like '%" + strBuildUserName + "%'";
    }
    if (strFillUserName.trim().length() > 0)
    {
      strSQL += " and e.[Name] like '%" + strFillUserName + "%'";
    }

    if (strTacheName.trim().length() > 0)
    {
      strSQL += " and f.[TacheName] like '%" + strTacheName + "%'";
    }

    if (strOrderBy.trim().length() > 0)
    {
      if (strIsDesc.equalsIgnoreCase("True"))
      {
        strSQL += " Order by " + strOrderBy + " desc";
      }
      else
      {
        strSQL += " Order by " + strOrderBy + "";
      }
    }

    Vector vt = SQLManager.GetResultSet(strSQL);
    return vt;
  }

  /**
   * 返回按制定条件查询的已作废记录集
   * @param strID ID标识
   * @param strDocumentName 公文名称
   * @param iDocFlowTempletID 公文流转模板ID
   * @param strDocFlowTempletName 公文流转模板名称
   * @param iBuildUserID 生成用户ID
   * @param strBuildUserName 生成用户姓名
   * @param iFillUserID 填表用户ID
   * @param strFillUserName 填表用户ID
   * @param bIsPigeonholed 是否已经归档
   * @param iCurrentFlowTacheID 当前流程环节ID
   * @param strCurrentFlowTacheName 当前流程环节名称
   * @param iTacheStatus 当前流程环节状态ID
   * @param strTacheStatus 当前流程环节状态
   * @param strIsReminded 是否已经提醒
   * @param strRemindTime 提醒时间
   * @param strBuildTimeFrom 建立时间起始
   * @param strBuildTimeTo 建立时间截至
   * @param strOrderBy 排序字段
   * @param bIsDesc 是否降序排序
   * @return 符合条件记录向量集
   */
  public static Vector getNoUsefulRecordBySearch(String strID,
                                                 String strDocumentName,
                                                 int iDocFlowTempletID,
                                                 String strDocFlowTempletName,
                                                 int iBuildUserID,
                                                 String strBuildUserName,
                                                 int iFillUserID,
                                                 String strFillUserName,
                                                 boolean bIsPigeonholed,
                                                 int iCurrentFlowTacheID,
                                                 String strCurrentFlowTacheName,
                                                 int iTacheStatus,
                                                 String strTacheStatus,
                                                 String strIsReminded,
                                                 String strRemindTime,
                                                 String strBuildTimeFrom,
                                                 String strBuildTimeTo,
                                                 String strOrderBy,
                                                 boolean bIsDesc)
  {
    String strSQL = "Select a.[ID], a.[DocumentName], a.[DocFlowTempletID], a.[BuildTime], a.[BuildUserID], a.[FillUserID]," +
      " a.[IsPigeonholed], a.[CurrentFlowTacheID], a.[IsReminded], a.[RemindTime]," +
      " c.[Name], d.[Name], e.[Name], f.[TacheName], f.[TacheStatus] from [" +
      TableName + "] as a" +
      " left outer join [UserInfo] as b on (a.BuildUserId=b.ID) left outer join [PersonnelInfo] as c on (b.PersonnelID=c.ID)" +
      " left outer join [UserInfo] as g on (a.FillUserID=g.ID) left outer join [PersonnelInfo] as d on (g.PersonnelID=d.ID)" +
      " left outer join [DocTempletMoveFlow] as e on (a.DocFlowTempletID=e.ID)" +
      " left outer join [FlowTacheInfo] as f on (a.CurrentFlowTacheID=f.ID) where a.[CurrentFlowTacheID] < -1";
    if (strDocumentName.trim().length() > 0)
    {
      strSQL += " and a.[DocumentName] like '%" + strDocumentName + "%'";
    }
    if (strDocFlowTempletName.trim().length() > 0)
    {
      strSQL += " and e.[Name] like '%" + strDocFlowTempletName + "%'";
    }
    if (strIsReminded.trim().equalsIgnoreCase("true"))
    {
      strSQL += " and a.[IsReminded] = 1";
    }
    else if (strIsReminded.trim().equalsIgnoreCase("false"))
    {
      strSQL += " and (a.[IsReminded] IS null OR a.[IsReminded] = 0)";
    }
    if (strBuildUserName.trim().length() > 0)
    {
      strSQL += " and c.[Name] like '%" + strBuildUserName + "%'";
    }
    if (strFillUserName.trim().length() > 0)
    {
      strSQL += " and d.[Name] like '%" + strFillUserName + "%'";
    }
    if (strCurrentFlowTacheName.trim().length() > 0)
    {
      strSQL += " and f.[TacheName] like '%" + strCurrentFlowTacheName + "%'";
    }
    if (strTacheStatus.trim().length() > 0)
    {
      strSQL += " and f.[TacheStatus] like '%" + strTacheStatus + "%'";
    }
    if (strBuildTimeFrom.trim().length() > 0)
    {
      strSQL += " and a.[BuildTime]>='" + strBuildTimeFrom + " 00:00:00.000'";
    }
    if (strBuildTimeTo.trim().length() > 0)
    {
      strSQL += " and a.[BuildTime]<='" + strBuildTimeTo + " 23:59:59.999'";
    }
    if (strOrderBy.trim().length() > 0)
    {
      if (bIsDesc)
      {
        strSQL += " Order by " + strOrderBy + " desc";
      }
      else
      {
        strSQL += " Order by " + strOrderBy + "";
      }
    }
    Vector vt = SQLManager.GetResultSet(strSQL);
    return vt;
  }
}