Beispiel #1
0
  public ActionForward query(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {

    String room = request.getParameter("room");
    String strDate = request.getParameter("date");
    Date startDay = UtilDateTime.getThisWeekDay(strDate, 1);
    Date endDay = UtilDateTime.getDiffDay(startDay, 6);

    try {
      Session hs = Hibernate2Session.currentSession();
      Transaction tx = null;
      tx = hs.beginTransaction();
      List valueList = null;

      if (room == null || room.trim().equals("")) {
        Query query =
            hs.createQuery(
                "from BookingRoomVO as br where br.bookingDate>=? and br.bookingDate<=? order by br.bookingDate,br.room,br.startTime");
        query.setDate(0, startDay);
        query.setDate(1, endDay);
        valueList = query.list();
      } else {
        Query query =
            hs.createQuery(
                "from BookingRoomVO as br where br.room=? and br.bookingDate>=? and br.bookingDate<=? order by br.bookingDate,br.room,br.startTime");
        query.setString(0, room);
        query.setDate(1, startDay);
        query.setDate(2, endDay);
        valueList = query.list();
      }
      request.setAttribute("valueList", valueList);
      request.setAttribute("startDay", startDay);
      hs.flush();
      tx.commit();

    } catch (Exception e) {
      e.printStackTrace();
      //	log.error(e.getMessage());
    } finally {
      try {
        Hibernate2Session.closeSession();
      } catch (HibernateException e1) {
        //		log.error(e1.getMessage());
        e1.printStackTrace();
      } catch (SQLException e1) {
        //		log.error(e1.getMessage());
        e1.printStackTrace();
      }
    }
    return mapping.findForward("query-success");
  }
Beispiel #2
0
 public Admin getAdminByName(String name) throws DBException {
   Session session;
   Admin admin = null;
   try {
     session = HibernateSessionFactory.currentSession();
     List list =
         session
             .createQuery("from com.bookstore.po.Admin admin where admin.name=:name")
             .setString("name", name)
             .list();
     if (list != null && list.size() > 0) {
       admin = (Admin) list.get(0);
     }
   } catch (HibernateException e) {
     e.printStackTrace();
     throw new DBException("获取对象失败!");
   } finally {
     try {
       HibernateSessionFactory.closeSession();
     } catch (HibernateException e) {
       e.printStackTrace();
     }
   }
   return admin;
 }
Beispiel #3
0
  public List getAdminListByPage(int pagesize, int curpage) throws DBException {
    List list = null;
    int Firstroll = (pagesize - 1) * curpage;
    Session session;
    try {
      session = HibernateSessionFactory.currentSession();
      list =
          session
              .createQuery("from com.bookstore.po.Admin admin  ")
              .setFirstResult((curpage - 1) * pagesize)
              .setMaxResults(pagesize)
              .list();
      System.out.println(">>>>>>>>>>>>>>>>>" + list.size());

    } catch (HibernateException e) {
      e.printStackTrace();
      throw new DBException("获取对象失败!");
    } finally {
      try {
        HibernateSessionFactory.closeSession();
      } catch (HibernateException e) {
        e.printStackTrace();
        throw new DBException("关闭session失败!");
      }
    }
    return list;
  }
Beispiel #4
0
  /**
   * Query by query sentence
   *
   * @param sql String Sql sentence
   * @param firstResult int First result
   * @param maxResult int Last result
   * @return List Return query results
   */
  public List listByCriteria(String sql, int firstResult, int maxResult) {

    Session session;
    List results = null;
    HbConn hbconn = new HbConn();
    try {
      session = hbconn.getSession();
      Query qry = session.createQuery(sql);
      if (maxResult > 0) {
        qry.setFirstResult(firstResult);
        qry.setMaxResults(maxResult);
      }
      qry.setCacheable(false);
      results = qry.list();
    } catch (Exception e) {
      // modified by xt	System.out.println("Exception)");
    } finally {
      try {
        hbconn.closeSession();
      } catch (Exception e2) {
        // modified by xt	System.out.println("Exception closing session)");
      }
      return results;
    }
  }
  /**
   * Executes the HQL query.
   *
   * @param query HQL query to execute.
   * @param sessionDataBean TODO
   * @param isSecureExecute TODO
   * @param columnIdsMap
   * @return
   * @throws ClassNotFoundException
   * @throws SQLException
   */
  public List executeQuery(
      String query,
      SessionDataBean sessionDataBean,
      boolean isSecureExecute,
      Map queryResultObjectDataMap)
      throws ClassNotFoundException, DAOException {
    List returner = null;

    try {
      Query hibernateQuery = session.createQuery(query);
      returner = hibernateQuery.list();

    } catch (HibernateException e) {
      throw (new DAOException(e));
    }

    return returner;
  }
Beispiel #6
0
  public List getAdminList() throws DBException {
    List list = null;
    Session session;
    try {
      session = HibernateSessionFactory.currentSession();
      list = session.createQuery("from com.bookstore.po.Admin admin  ").list();
      System.out.println(">>>>>>>>>>>>>>>>>" + list.size());

    } catch (HibernateException e) {
      e.printStackTrace();
      throw new DBException("获取对象失败!");
    } finally {
      try {
        HibernateSessionFactory.closeSession();
      } catch (HibernateException e) {
        e.printStackTrace();
        throw new DBException("关闭session失败!");
      }
    }
    return list;
  }
  /**
   * Erzeugen einer vollständigen Query an der Session mit allen angegeben Suchkriterien. Mit
   * attributeNamesToSelect können einzelne Attribute geladen werden.
   *
   * @param session
   * @param attributeNamesToSelect null oder Länge = 0 führt zur Selektion des IMSFacilityImpl
   * @return entweder ein IMSFacilityImpl, oder ein Object[] aus den gewählten Attributen
   */
  public Query getQuery(Session session, String[] attributeNamesToSelect)
      throws HibernateException {
    // Der Buffer für die gesammelten where-clause-Häppchen.
    StringBuffer whereClause = new StringBuffer();

    // Der Zähler für die automatische Nummerierung der positional parameter.
    int paramNumber = 0;

    // Im ersten Durchgang werden alle übergebenen Suchkriterien abgeklappert, um daraus
    // einen korrekten HQL-Query-String zu bauen.
    // Im Anschluss an das Erzeugen der Query über die Session mit dem Query-String, müssen
    // alle übergebenen Suchkriterien ein zweites Mal abgeklappert werden, um per
    // setParameter() die Kriterien in die Query einzusetzen.

    if (!id.isEmpty()) {
      whereClause.append('(');
      for (int i = 0; i < id.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.id = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!name.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < name.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append(
            "upper(btsitetemplate.name) like upper(:QueryParam" + ++paramNumber + ")");
      }
      whereClause.append(')');
    }

    if (!company.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < company.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.company = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!reference.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < reference.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append(
            "upper(btsitetemplate.reference) like upper(:QueryParam" + ++paramNumber + ")");
      }
      whereClause.append(')');
    }

    if (!contact.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < contact.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.contact = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!phone.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < phone.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.phone = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!fax.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < fax.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.fax = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!mobile.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < mobile.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.mobile = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!email.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < email.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.email = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!street.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < street.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.street = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!housenumber.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < housenumber.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.housenumber = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!zip.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < zip.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.zip = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!city.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < city.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append(
            "upper(btsitetemplate.city) like upper(:QueryParam" + ++paramNumber + ")");
      }
      whereClause.append(')');
    }

    if (!state.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < state.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.state = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!country.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < country.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.country like :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!remarks.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < remarks.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.remarks = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!regionid.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < regionid.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.regionid = :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    if (!type.isEmpty()) {
      if (paramNumber > 0) {
        whereClause.append(" and ");
      }
      whereClause.append('(');
      for (int i = 0; i < type.size(); i++) {
        if (i > 0) {
          whereClause.append(" or ");
        }
        whereClause.append("btsitetemplate.type= :QueryParam" + ++paramNumber);
      }
      whereClause.append(')');
    }

    // Endgültige Query erzeugen.
    String where = "";
    if (whereClause.length() > 0) {
      where = " where " + whereClause.toString();
    }

    StringBuffer selectClause = new StringBuffer();
    if (attributeNamesToSelect != null) {
      for (int i = 0; i < attributeNamesToSelect.length; i++) {
        selectClause.append("btsitetemplate.");
        selectClause.append(attributeNamesToSelect[i]);
        if (i + 1 < attributeNamesToSelect.length) {
          selectClause.append(", ");
        }
      }
    }

    Query query =
        session.createQuery(
            "select "
                + selectClause.toString()
                + " from BTSiteTemplateImpl btsitetemplate "
                + where);

    // Positional parameter setzen.
    paramNumber = 0;

    for (int i = 0; i < id.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, id.get(i));
    }

    for (int i = 0; i < name.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, name.get(i));
    }

    for (int i = 0; i < company.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, company.get(i));
    }

    for (int i = 0; i < reference.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, reference.get(i));
    }

    for (int i = 0; i < contact.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, contact.get(i));
    }

    for (int i = 0; i < phone.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, phone.get(i));
    }

    for (int i = 0; i < fax.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, fax.get(i));
    }

    for (int i = 0; i < mobile.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, mobile.get(i));
    }

    for (int i = 0; i < email.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, email.get(i));
    }

    for (int i = 0; i < street.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, street.get(i));
    }

    for (int i = 0; i < housenumber.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, housenumber.get(i));
    }

    for (int i = 0; i < zip.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, zip.get(i));
    }

    for (int i = 0; i < city.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, city.get(i));
    }

    for (int i = 0; i < state.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, state.get(i));
    }

    for (int i = 0; i < country.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, country.get(i));
    }

    for (int i = 0; i < remarks.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, remarks.get(i));
    }

    for (int i = 0; i < regionid.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, regionid.get(i));
    }

    for (int i = 0; i < type.size(); i++) {
      query.setParameter("QueryParam" + ++paramNumber, type.get(i));
    }

    return query;
  }
  public ActionForward execute(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {

    // Extract attributes we will need
    Logger log = Logger.getLogger(ReceiptDialogAction.class.getName());
    Locale locale = getLocale(request);
    MessageResources messages = getResources();
    String action = request.getParameter("FormAction");
    HttpSession session = request.getSession();
    List pageNumberList = new ArrayList();
    PageBean pageBean = new PageBean();

    String customerId = (String) request.getSession().getAttribute("BillAddressForReceipt");
    String filter = request.getParameter("custFilter");

    String pageNumber = request.getParameter("pageNumber");
    if (pageNumber == null) pageNumber = "";
    if (!pageNumber.equals("")) {
      pageBean = (PageBean) session.getAttribute("UserPageBean");
      pageBean.setCurrentPage(new Integer(pageNumber).intValue());
      pageNumberList = getPageNumberList(pageBean);
    } else {
      try {
        net.sf.hibernate.Session hs = Hibernate2Session.currentSession();
        String receiptNo = request.getParameter("ReceiptNo");
        String receiptCustomer = request.getParameter("ReceiptCustomer");

        if (receiptNo == null) receiptNo = "";
        if (receiptCustomer == null) receiptCustomer = "";

        String QryStr = "select p from ProjectReceiptMaster as p inner join p.customerId as c ";
        QryStr = QryStr + " where p.receiptNo like '%" + receiptNo + "%'";
        QryStr =
            QryStr
                + " and ( c.partyId like '%"
                + receiptCustomer
                + "%' or c.description like '%"
                + receiptCustomer
                + "%')";
        QryStr = QryStr + " and p.receiptStatus <> '" + Constants.RECEIPT_STATUS_COMPLETED + "'";

        String QryStr2 = QryStr + " and p.customerId = '" + customerId + "'";
        List result = null;
        if (filter != null && filter.equals("1")) {
          result = hs.createQuery(QryStr).list();
        } else result = hs.createQuery(QryStr2).list();

        pageBean.setItemList(result);
        int recCount = result.size();
        pageBean.setPage(MAXPAGE, recCount);
        pageNumberList = getPageNumberList(pageBean);
      } catch (Exception e) {
        e.printStackTrace();
      }
      Hibernate2Session.closeSession();
    }
    session.setAttribute("UserPageBean", pageBean);
    request.setAttribute("PageNumberList", pageNumberList);
    return (mapping.findForward("success"));
  }
Beispiel #9
0
  public ActionForward list4Add(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {

    //	Logger log = Logger.getLogger(EditPreSaleProjectAction.class.getName());
    String action = request.getParameter("formAction");
    //	log.info("action=" + action);

    String strDate = request.getParameter("date");
    Date date = UtilDateTime.toDate2(strDate + " 00:00:00.000");
    String room = request.getParameter("room");

    if (action == null) {
      action = "view";
    }

    try {
      Session hs = Hibernate2Session.currentSession();
      Transaction tx = null;

      if (action.equals("create")) {

        String personId = request.getParameter("iPerson");
        String startTime = request.getParameter("iStartTime");
        String endTime = request.getParameter("iEndTime");

        BookingRoomVO tmpValue = new BookingRoomVO();
        UserLogin person = new UserLogin();

        tmpValue.setBookingDate(date);
        tmpValue.setRoom(room);
        tmpValue.setStartTime(startTime);
        tmpValue.setEndTime(endTime);

        tx = hs.beginTransaction();

        person = (UserLogin) hs.load(person.getClass(), personId);
        tmpValue.setPerson(person);
        hs.save(tmpValue);

        hs.flush();
        tx.commit();
      }

      if (action.equals("remove")) {

        Long id = Long.valueOf(request.getParameter("bookingId"));

        tx = hs.beginTransaction();

        BookingRoomVO tmpValue = (BookingRoomVO) hs.load(BookingRoomVO.class, id);
        hs.delete(tmpValue);

        hs.flush();
        tx.commit();
      }

      if (action.equals("update")) {

        String bookingId[] = request.getParameterValues("bookingId");
        String startTime[] = request.getParameterValues("startTime");
        String endTime[] = request.getParameterValues("endTime");

        BookingRoomVO tmpValue = new BookingRoomVO();

        tx = hs.beginTransaction();

        int rowSize = java.lang.reflect.Array.getLength(bookingId);
        for (int i = 0; i < rowSize; i++) {
          Long id = null;
          if (bookingId[i] != null && bookingId[i].length() > 0) {
            id = Long.valueOf(bookingId[i]);
            tmpValue = (BookingRoomVO) hs.load(BookingRoomVO.class, id);
            tmpValue.setStartTime(startTime[i]);
            tmpValue.setEndTime(endTime[i]);
            hs.update(tmpValue);
          }
        }
        tx.commit();
        hs.flush();
      }

      if (action.equals("view")
          || action.equals("create")
          || action.equals("remove")
          || action.equals("update")) {

        request.setAttribute("dateAdd", strDate);
        request.setAttribute("roomAdd", room);

        tx = hs.beginTransaction();
        List valueList = null;

        Query query =
            hs.createQuery(
                "from BookingRoomVO as br where br.room=? and br.bookingDate=? order by br.startTime");
        query.setString(0, room);
        query.setDate(1, date);
        valueList = query.list();

        request.setAttribute("valueList", valueList);
        hs.flush();
        tx.commit();
      }
    } catch (Exception e) {
      e.printStackTrace();
      //	log.error(e.getMessage());
      return (mapping.findForward("view"));
    } finally {
      try {
        Hibernate2Session.closeSession();
      } catch (HibernateException e1) {
        //		log.error(e1.getMessage());
        e1.printStackTrace();
      } catch (SQLException e1) {
        //		log.error(e1.getMessage());
        e1.printStackTrace();
      }
    }
    return mapping.findForward("list4Add-success");
  }
Beispiel #10
0
  private ActionForward ExportToExcel(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response) {
    try {
      ActionErrors errors = this.getActionErrors(request.getSession());
      String DataId = request.getParameter("DataId");
      if ((DataId == null) || (DataId.length() < 1))
        actionDebug.addGlobalError(errors, "error.context.required");
      if (!errors.empty()) {
        saveErrors(request, errors);
        return null;
      }

      // Get Excel Template Path
      String TemplatePath = GetTemplateFolder();
      if (TemplatePath == null) return null;

      // Fetch related Data
      net.sf.hibernate.Session hs = Hibernate2Session.currentSession();
      ExpenseMaster findmaster = (ExpenseMaster) hs.load(ExpenseMaster.class, new Long(DataId));
      if (findmaster == null) return null;
      UserLogin ul = findmaster.getExpenseUser();
      List ExpList =
          hs.createQuery("select et from ExpenseType as et order by et.expSeq ASC").list();
      Iterator itExpType = ExpList.iterator();
      Date dayStart = findmaster.getExpenseDate();
      ArrayList DateList = new ArrayList();
      for (int i = 0; i < 14; i++) {
        DateList.add(UtilDateTime.getDiffDay(dayStart, i));
      }
      Iterator itDate = DateList.iterator();
      Query q =
          hs.createQuery(
              "select ed from ExpenseDetail as ed inner join ed.ExpMaster as em inner join ed.ExpType as et where em.Id =:DataId order by ed.ExpenseDate, et.expSeq ASC");
      q.setParameter("DataId", DataId);
      List detailList = q.list();
      q =
          hs.createQuery(
              "select ec from ExpenseComments as ec inner join ec.ExpMaster as em where em.Id =:DataId order by ec.ExpenseDate");
      q.setParameter("DataId", DataId);
      List CmtsList = q.list();
      Iterator itDetail = detailList.iterator();
      Iterator itCmts = CmtsList.iterator();
      ExpenseDetail ed = null;
      if (itDetail.hasNext()) {
        ed = (ExpenseDetail) itDetail.next();
      } else {
        return null;
      }
      ExpenseComments ec = null;
      if (itCmts.hasNext()) {
        ec = (ExpenseComments) itCmts.next();
      }
      // Start to output the excel file
      response.reset();
      response.setHeader(
          "Content-Disposition",
          "attachment;filename=\"Expense" + findmaster.getFormCode() + ".xls\"");
      response.setContentType("application/octet-stream");

      // Use POI to read the selected Excel Spreadsheet
      HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(TemplatePath + "\\" + ExcelTemplate));
      // Select the first worksheet
      HSSFSheet sheet = wb.getSheet(FormSheetName);

      // Header
      HSSFRow row = sheet.getRow(5);
      HSSFCell cell = row.getCell((short) 1); // ER No.
      cell.setCellValue(findmaster.getFormCode());
      cell = row.getCell((short) 4); // Department
      cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
      cell.setCellValue(ul.getParty().getDescription());
      cell = row.getCell((short) 8); // User Name
      cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
      cell.setCellValue(ul.getName());
      cell = row.getCell((short) 12); // Customer
      cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
      cell.setCellValue(
          findmaster.getProject().getCustomer().getPartyId()
              + ":"
              + findmaster.getProject().getCustomer().getDescription());

      if (findmaster.getClaimType().equals("CY")) { // Paid by
        cell = sheet.getRow(1).getCell((short) 12);
        cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
        cell.setCellValue(
            findmaster.getProject().getBillTo().getPartyId()
                + ":"
                + findmaster.getProject().getBillTo().getDescription()
                + "("
                + findmaster.getProject().getBillTo().getChineseName()
                + ")");
      }

      cell = sheet.getRow(2).getCell((short) 12);
      cell.setCellValue(findmaster.getExpenseCurrency().getCurrId());
      cell = sheet.getRow(3).getCell((short) 12);
      cell.setCellValue(findmaster.getCurrencyRate().floatValue());
      cell = sheet.getRow(4).getCell((short) 12); // Project Information
      cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
      cell.setCellValue(
          findmaster.getProject().getProjId()
              + ":"
              + findmaster.getProject().getProjName()
              + " ( "
              + findmaster.getProject().getDepartment().getDescription()
              + " )");

      cell = sheet.getRow(25).getCell((short) 3);
      cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
      cell.setCellValue("Employee (" + ul.getName() + ")");
      cell = sheet.getRow(27).getCell((short) 3);
      cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
      cell.setCellValue(
          "Confirmed By (" + findmaster.getProject().getProjectManager().getName() + ")");

      // List Header
      int ExcelRow = 0;
      int ExcelCol = 0;

      // List
      boolean NullData = true;
      ExcelRow = ListStartRow;
      while (itDate.hasNext()) {
        Date fd = (Date) itDate.next();
        row = sheet.getRow(ExcelRow);
        cell = row.getCell((short) 0);
        cell.setCellValue(fd);
        ExcelCol = ListStartCol;
        itExpType = ExpList.iterator();
        while (itExpType.hasNext()) {
          ExpenseType et = (ExpenseType) itExpType.next();
          NullData = false;
          ExcelCol++;
          if (ed != null) {
            if (ed.getExpenseDate().equals(fd) && ed.getExpType().equals(et)) {
              cell = row.getCell((short) ExcelCol);
              cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
              cell.setCellValue(ed.getUserAmount().doubleValue());
              if (itDetail.hasNext()) {
                ed = (ExpenseDetail) itDetail.next();
              }
            }
          }
        }
        if (ec != null) {
          if (ec.getExpenseDate().equals(fd)) {
            cell = row.getCell((short) 1);
            cell.setEncoding(HSSFCell.ENCODING_UTF_16); // 设置cell编码解决中文高位字节截断
            cell.setCellValue(ec.getComments());
            if (itCmts.hasNext()) {
              ec = (ExpenseComments) itCmts.next();
            }
          }
        }
        ExcelRow++;
      }

      // 写入Excel工作表
      wb.write(response.getOutputStream());
      // 关闭Excel工作薄对象
      response.getOutputStream().close();
      response.flushBuffer();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }
Beispiel #11
0
  public ActionForward perform(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response) {
    // Extract attributes we will need
    Logger log = Logger.getLogger(PRMUserListAction.class.getName());
    Locale locale = getLocale(request);
    MessageResources messages = getResources();
    String action = request.getParameter("FormAction");
    DynaValidatorForm actionForm = (DynaValidatorForm) form;
    HttpSession session = request.getSession();
    List projectSelectArr = new ArrayList();
    List result = new ArrayList();
    List pageNumberList = new ArrayList();
    PageBean pageBean = new PageBean();

    String pageNumber = request.getParameter("pageNumber");
    if (pageNumber == null) pageNumber = "";
    if (!pageNumber.equals("")) {
      pageBean = (PageBean) session.getAttribute("UserPageBean");
      pageBean.setCurrentPage(new Integer(pageNumber).intValue());
      pageNumberList = getPageNumberList(pageBean);
    } else {
      // init display
      try {
        net.sf.hibernate.Session hs = Hibernate2Session.currentSession();
        Transaction tx = null;
        String PartyId = request.getParameter("partyId");
        String lStrOpt = request.getParameter("rad");
        String srchStaff = request.getParameter("srchStaff");
        String srchDep = request.getParameter("srchDep");

        if (PartyId == null) PartyId = "";
        if (lStrOpt == null) lStrOpt = "2";
        if (srchStaff == null) srchStaff = "";
        if (srchDep == null) srchDep = "";

        String ChildPartyString = "'" + PartyId + "'";
        String QryStr = "";

        /** 获得指定机构的下级机构列表 */
        Party party = (Party) hs.load(Party.class, PartyId);
        PartyHelper ph = new PartyHelper();

        List pList = ph.getAllSubPartysByPartyId(hs, PartyId);
        if (pList != null) {
          Iterator pIt = pList.iterator();
          while (pIt.hasNext()) {
            Party p = (Party) pIt.next();
            ChildPartyString = ChildPartyString + ",'" + p.getPartyId() + "'";
          }
        }

        QryStr =
            "select ul from UserLogin as ul inner join ul.party as p where p.partyId in ("
                + ChildPartyString
                + ")";
        if (!srchStaff.equals("")) {
          if (lStrOpt.equals("2")) {
            QryStr =
                QryStr
                    + " and (ul.userLoginId like '%"
                    + srchStaff
                    + "%' or ul.name like '%"
                    + srchStaff
                    + "%')";
          } else {
            QryStr =
                QryStr
                    + " and (ul.userLoginId = '"
                    + srchStaff
                    + "' or ul.name = '"
                    + srchStaff
                    + "')";
          }
        }
        if (!srchDep.equals("")) {
          if (lStrOpt.equals("2")) {
            QryStr =
                QryStr
                    + " and (p.partyId like '%"
                    + srchDep
                    + "%' or p.description like '%"
                    + srchDep
                    + "%')";
          } else {
            QryStr =
                QryStr
                    + " and (p.partyId = '"
                    + srchDep
                    + "' or p.description = '"
                    + srchDep
                    + "')";
          }
        }

        Query q = hs.createQuery(QryStr);
        result = q.list();
        pageBean.setItemList(result);
        int recCount = result.size();
        pageBean.setPage(MAXPAGE, recCount);
        pageNumberList = ProjectListAction.getPageNumberList(pageBean);

      } catch (Exception e) {
        e.printStackTrace();
        log.error(e.getMessage());
        return (mapping.findForward("success"));
      } finally {
        try {
          Hibernate2Session.closeSession();
        } catch (HibernateException e1) {
          log.error(e1.getMessage());
          e1.printStackTrace();
        } catch (SQLException e1) {
          log.error(e1.getMessage());
          e1.printStackTrace();
        }
      }
    }

    session.setAttribute("UserPageBean", pageBean);
    request.setAttribute("pageName", "FindPRMPage");
    request.setAttribute("PageNumberList", pageNumberList);

    return (mapping.findForward("success"));
  }
  /**
   * Retrieves the records for class name in sourceObjectName according to field values passed in
   * the passed session.
   *
   * @param whereColumnName An array of field names.
   * @param whereColumnCondition The comparision condition for the field values.
   * @param whereColumnValue An array of field values.
   * @param joinCondition The join condition.
   * @param The session object.
   */
  public List retrieve(
      String sourceObjectName,
      String[] selectColumnName,
      String[] whereColumnName,
      String[] whereColumnCondition,
      Object[] whereColumnValue,
      String joinCondition)
      throws DAOException {
    List list = null;
    try {
      StringBuffer sqlBuff = new StringBuffer();

      String className = Utility.parseClassName(sourceObjectName);

      // Logger.out.debug("***********className:"+className);
      if (selectColumnName != null && selectColumnName.length > 0) {
        sqlBuff.append("Select ");

        // Added by Aarti
        // --------------------------------
        //                if(sourceObjectName.equals(Site.class.getName()))
        //                {
        //                    sqlBuff.append(className + "." + Constants.SYSTEM_IDENTIFIER);
        //                    sqlBuff.append(", ");
        //                }
        // ---------------------------------
        for (int i = 0; i < selectColumnName.length; i++) {
          sqlBuff.append(className + "." + selectColumnName[i]);
          if (i != selectColumnName.length - 1) {
            sqlBuff.append(", ");
          }
        }
        sqlBuff.append(" ");
      }
      // Logger.out.debug(" String : "+sqlBuff.toString());

      Query query = null;
      sqlBuff.append("from " + sourceObjectName + " " + className);
      // Logger.out.debug(" String : "+sqlBuff.toString());

      if ((whereColumnName != null && whereColumnName.length > 0)
          && (whereColumnCondition != null && whereColumnCondition.length == whereColumnName.length)
          && (whereColumnValue != null)) {
        if (joinCondition == null) joinCondition = Constants.AND_JOIN_CONDITION;

        sqlBuff.append(" where ");

        // Adds the column name and search condition in where clause.
        for (int i = 0; i < whereColumnName.length; i++) {
          sqlBuff.append(className + "." + whereColumnName[i] + " ");
          if (whereColumnCondition[i].indexOf("in") != -1) {
            sqlBuff.append(whereColumnCondition[i] + "(  ");
            Object valArr[] = (Object[]) whereColumnValue[i];
            for (int j = 0; j < valArr.length; j++) {
              // Logger.out.debug(sqlBuff);
              sqlBuff.append("? ");
              if ((j + 1) < valArr.length) sqlBuff.append(", ");
            }
            sqlBuff.append(") ");
          } else if (whereColumnCondition[i].indexOf("is not null") != -1) {
            sqlBuff.append(whereColumnCondition[i]);
          } else if (whereColumnCondition[i].indexOf("is null") != -1) {
            sqlBuff.append(whereColumnCondition[i]);
          } else {
            sqlBuff.append(whereColumnCondition[i] + " ? ");
          }

          if (i < (whereColumnName.length - 1)) sqlBuff.append(" " + joinCondition + " ");
        }

        // Logger.out.debug(sqlBuff.toString());

        query = session.createQuery(sqlBuff.toString());

        int index = 0;
        // Adds the column values in where clause
        for (int i = 0; i < whereColumnValue.length; i++) {
          // Logger.out.debug("whereColumnValue[i]. " + whereColumnValue[i]);
          if (whereColumnCondition[i].equals("is null")
              || whereColumnCondition[i].equals("is not null")) {
          } else {

            Object obj = whereColumnValue[i];
            if (obj instanceof Object[]) {
              Object[] valArr = (Object[]) obj;
              for (int j = 0; j < valArr.length; j++) {
                query.setParameter(index, valArr[j]);
                index++;
              }
            } else {
              query.setParameter(index, obj);
              index++;
            }
          }
        }
      } else {
        query = session.createQuery(sqlBuff.toString());
      }

      list = query.list();

      //          Added by Aarti
      // --------------------------------

      //            if(sourceObjectName.equals(Site.class.getName()))
      //            {
      //                boolean isAuthorized;
      //                Object[] objects = null;
      //                Object[] newObjects = null;
      //                Long id;
      //                Site site;
      //
      //                if (selectColumnName != null && selectColumnName.length > 0)
      //                {
      //                    if(list != null)
      //                    {
      //                        for(int i=0; i<list.size();)
      //                        {
      //                            objects = (Object[]) list.get(i);
      //
      //                            if(objects != null)
      //                            {
      //                                newObjects = new Object[objects.length-1];
      //                                id = (Long) objects[0];
      //                                isAuthorized = SecurityManager.getInstance(this.getClass())
      //                                .isAuthorized("*****@*****.**",
      //                                        sourceObjectName+"_"+id,
      //                                        Permissions.USE);
      //                                Logger.out.debug(" User's Authorization to update
      // "+sourceObjectName+"_"+id+" "+isAuthorized);
      //                                list.remove(i);
      //                                if(isAuthorized)
      //                                {
      //                                    for(int x = 1;x<objects.length;x++ )
      //                                    {
      //                                        newObjects[x-1] = objects[x];
      //                                    }
      //                                    list.add(i,newObjects);
      //                                    i++;
      //                                }
      //                            }
      //                        }
      //                    }
      //                }
      //                else
      //                {
      //                    if(list != null)
      //                    {
      //                        for(int i=0; i<list.size();)
      //                        {
      //                            site = (Site) list.get(i);
      //                            if(site !=null)
      //                            {
      //                                isAuthorized = SecurityManager.getInstance(this.getClass())
      //                                .isAuthorized("*****@*****.**",
      //                                        sourceObjectName+"_"+site.getId(),
      //                                        Permissions.USE);
      //                                Logger.out.debug(" User's Authorization to update
      // "+sourceObjectName+"_"+site.getId()+" "+isAuthorized);
      //
      //                                if(isAuthorized)
      //                                {
      //                                    i++;
      //                                }
      //                                else
      //                                {
      //                                    list.remove(i);
      //                                }
      //                            }
      //                        }
      //                    }
      //                }
      //            }
      // ---------------------------------

      // Logger.out.debug(" String : " + sqlBuff.toString());
    } catch (HibernateException hibExp) {
      Logger.out.error(hibExp.getMessage(), hibExp);
      throw new DAOException("Error in retrieve " + hibExp.getMessage(), hibExp);
    } catch (Exception exp) {
      Logger.out.error(exp.getMessage(), exp);
      throw new DAOException("Logical Erroe in retrieve method " + exp.getMessage(), exp);
    }
    return list;
  }